769
Hi,
I’m looking for help how to calculate totals of documents from the flat file using vb script of the Integration Manager. Need to set the document type based on the total: If Total <0 then SourceField = “Return” else SourceField = “Invoice”.Anybody’s done something like that?Thank you in advance,
Oleg.
*This post is locked for comments
Well I missed that part in the first post about adding up the detail lines. You can use the detail lines to create the header records if they have the necessary information. Group the lines in IM by the document number and have it sum the extended prices for you.
Thank you Richard,
You are correct, I’m trying to import the batch of sales transactions from the flat file that contains only line items (no headers). I’m dynamically creating header records by sorting and grouping the records while creating a Header query. I then need to dynamically sum up the line item amounts to figure out whether the total documents is positive or negative and based on that set the document type. I was hoping somebody may have developed a script that sums up the line items and passes the total to the Document Type field.Regards,
Oleg.
You have a lot more to do than just set the document type.
Your post was not clear but I am assuming you are trying to import sales transactions using integration manager. You will need to not only change the document type but also change the value of the documents. See, GP stores all documents with positive numbers. So the total of a Return loaded in GP is a positive number.
The integration manager has the ability to run scripts against fields. Set the source of the Document Type field to Script and then open the details area where you can write the script you need. There are a number of sample scripts, including one that sets a value of a field based on the contents of the source document. Just what you need.
However, if your source documents (header and lines) have negative numbers on returns, you will need to create a formula for each value and take the Absolute value of the field or multiply it by -1 when appropriate to get positive values.
Check the sample scripts. I am sure the syntax on this is not 100% accurate but the lines should read something like
If SourceField(type)='RETURN then CURRENTFIELD = SourceField(lineunitprice) * -1;
or
IF SourceField(mytotal)<0 then CURRENTFIELD = SourceField(lineunitprice) * -1 else CURRENTFIELD = SourceField(lineunitprice);
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (