Dhoover, you can use the flatten transform to take a hierarchical dataset into a flat one.

You may need to first flatten, then re-heirarchise...but it will be dependent on the data.

Thanks

If you send a couple of relevent screenshots, we can provide some guidance.

I have attached a screenshot of my SQL data.  I need it to flatten to one record per check (I highlighted that column).  Not sure how to do this using the flatten transform, so any assistance appreciated.

Dane, I think we have our terms mixed up. 

By flatten, I think you mean you want a single record per unique check number.

This would then be used to map to the Bank Services reversal.

To do this, you want to add a hierarchy transform, where you have two transaction types (header & detail), where the header will be mapped to Bank Services reversal.

The hierarchy transform will use the 'CheckNumber' field as the key.

I took your suggestion and hierarchized the data, however after mapping it to a Bank Reversal transaction I get the error in the attached screenshot.  The bank does exist if course, which makes me wonder if it isn't logging into the company correctly.

Dane, the bank does not exist.

If you're attempting to reverse cheques in multiple companies, the error could be coming from any of those.

So, this means that the bank needs to exist in all companies?  This client has multiple companies.  With the other integrations I just map the company id in the source data to the COID in Sage.

Dane, no the bank does not need to exist in all companies.

Are you able to reverse any checks?

To isolate the error I would recommend that you remove every record except for the first to see if you can replicate the error. If you can, I would try entering the values into the target company to see if you get the same error.

Thanks

Dane, if the reversal worked for a single check for a single company, it means it will work across all companies (99.9999% on this).

What I think is happening is there's an error on a single record where the bank code is incorrect.

To isolate the record causing the error...

1. Create a field in a Map transform prior to the Sage300 connector. Call it 'Error'.
2. On the Sage300 Connector (of the reversal transaction), map the 'Error' field to the SYS.ERROR field at the bottom of the field list.
3. On the Audit tab of the Sage300 connector change the Action on Transform Error to Reject Record.
4. Set the Log Keys (on the Field Mapping tab) to the System Connector/Company Id, Vendor, and Cheque Number fields respectively 1, 2 & 3.

Then you have two options...

1. Designer - Press Refresh you will see the error message being printed against the actual record generating it.
2. Scheduler - If run the integration from the scheduler then, because of the log keys you should see the record generating the error.

Since Check Reversals are a one-off, I would recommend you backup the Sage databases.