How to negate integration manager from reading formula rows on import sheets

(0) Share

Report

Posted on by v_soysouvanh

hi we have a an AP import that has rounding formulas that we don't want to remove, unfortunately integration manager is reading these formulas as its own voucher. Though all invoices import correctly into GP, integration manager is reading everything so there are 1000+ of ERROR: Field 'Vendor ID' does not have a default value popping up. Is there anyway to stop this from happening?

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (0)

Community Member on at

Like (0)

Report

v_soysouvanh, Are you trying to map vendor id to a cell with formula?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Can you try the mapping with an alternative cell which has value.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Try splitting the formulas in separate spreadsheet and the results output in a different spread sheet. Map the results sheet with the IM.

Was this reply helpful? Yes No
v_soysouvanh on at

Like (0)

Report

Arun the value that I'm using in the calculated cell is for the purchases field in AP, so this field is actually a ifsums formula that adds the total gl amount lines per invoice and vendor. So this formula drags all the way to the 1000th row.

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

In the past, when we've had to perform transformation of data from a file, and preserve the format of the original file, we've employed before integration scripts to process the file, and save a new version (i.e. values in CSV format). This is not easy to do, per se, but a good consultant should be able assist you with the effort. You could do something similar by performing a manual "save as" operation or use SSIS to transform the data. There are lots of options.

Was this reply helpful? Yes No
v_soysouvanh on at

Like (1)

Report

I think I figured out the problem. Looks like u have to change the sorting. So in my case in the sources header field I'm sorting vendor ID first and invoice amount last. This will make IM read the vendor ID up to the rows with values in the import and will negate all blank rows even though there are formulas in other columns.

Was this reply helpful? Yes No
Redbeard 12,931 on at

Like (0)

Report

Let us know if this turns out to be the case!

Was this reply helpful? Yes No
v_soysouvanh on at

Like (0)

Report

I tested with 2 of our integrations by removing then adding back the sortings. Seems like this fixed it.

Was this reply helpful? Yes No
Suggested answer

Bill Campbell 12 on at

Like (0)

Report

Another option would also be to use a restriction on the Header - assuming you are using a single file and creating a header and detail files - if you restrict the header >> Vendor ID to be any thing but BLANK - then it will remove the blank field for you and not even consider them in the import process.

You do not need to change the sort order or tamper with the original data.

Was this reply helpful? Yes No
v_soysouvanh on at

Like (0)

Report

Thanks Bill I'll try this as well.

Was this reply helpful? Yes No