Integration Manager easy script?

[barbola]

I am getting there, slowly but surely. I have an integration for a payables transaction. Our vendor finally had to write it for me and it uses a script.

Now I want to change the script. This is what it was:

If (SourceFields("pm detail.DistType"))="PAY" Then SPurchase=SourceFields("pm detail.Amount")*-1
Else sPurchase=""
End If

CurrentField=sPurchase

but I was getting an error because the Else should have been Else sPurchase = 0.

But now, I also want to add a second "if" so I did this and it only works if DistType is "PAY".

If (SourceFields("pm detail.DistType"))="PAY" Then
  SPurchase=SourceFields("pm detail.Amount")*-1
  Else
  If (SourceFields("pm detail.F3"))="CASH" Then 
    SPurchase=SourceFields("pm detail.Amount")*-1
  Else sPurchase=""
  End If
End If

Okay, I just ran it again (answering my thread within my thread...I am losin it)!

I think the reason is because there is more than one line in the source query for the same vendor invoice that uses "CASH". I think I need another grouping level.

Okay I added pm - transaction with grouping level for the Distribution Type but how do I add the amounts together using an integration script?

thanks
barb

 

[barbola]

Does anyone know how to write a script for Integration Manager?

My sample data has 4 records that integrate into one payables transaction, but I need to add two of them together to get the Purchases amount. Either that or subtract the Tax from the Payables amount.

I know VB a bit but can't see any examples that do this.

I also can't find any training within 5000 miles, and the one I found will cost as much as the software. There is no WAY my employer would pay for this.

I emailed our VAR but they haven't gotten back to me yet.

thanks.

 

[RobertC22]

Hello barbola,

In your VBscript, in one line you refer to the field source field "Dist Type", yet in the second line you refer to "F3" instead.
Is the "PAY" and "CASH" in the same field from this source query?

I will assume you have two source queries.
One for the Payables header "pm header", and another for the Payables distributions "pm detail".

Unfortunately, the payables header fields will need the Purchase amount total. You cannot add up lines from the four (or more) "pm detail" records as you go and then place into the DestinationField.
*1

If there one record in the "pm detail" that can be considered your header record, and contains fields with amount that allows you to determine you pruchase amount, tax amount, frieght and miscellaneous amounts (if appropriate)?
If so, in the "pm header" source query, create a restriction so it is that record used - rather than grouping.
the "pm detail" can then load the individual distribution accounts.


Note *1
Perhaps there is a way, though I havent done it.
The following scripts may have syntax errors.
The following set a variable to accumulate the amount of purchase lines. As each distribution line is scanned will sum the value, and put it back into the Purchases field of the pm header. Not sure whether this is allowable, though give it a try.

In the Before Document script have

SetVariable "gPurchases", 0

In the distribution account script

' in additional to reversing the sign and setting the distribution amount, add the following

AccumulatePurchases = GetVariable ("gPurchases")
AccumulatePurchases = sPurchase + gPurchases
SetVariable "gPurchases", AccumulatePurchases
Destinationfield ("Payables.Purchases") = AccumulatePurchases

' The payables.purchases need to match the name of the destination field in the destination mapping where the total purchase amount is usually placed.

The above would be like entering an amount on the payables window, say 50, then going to the distribution window, typing in the distribution account with the amount of $50. Then based on the second line in the source query going back to the payables window, and increasing the pruchase amount from 50 to 120, going to the distribution window, adding you next distribution account with an amount of $70. Repeat this process for the four lines.

It might be that you need the following in the Before Document Save script rather than at the end of the distribution amount field script:

AccumulatePurchases = GetVariable ("gPurchases")
Destinationfield ("Payables.Purchases") = AccumulatePurchases

If you need more instructions, can you please include a list of your source query names and the fields each query contains.

-------
RobertC

 

[barbola]

The F3 and DistType are the same, it was a typo. I will look at your response later today/tomorrow and thanks very much.

 

[barbola]

[blue]' in additional to reversing the sign and setting the distribution amount, add the following
[/blue]
AccumulatePurchases = GetVariable ("gPurchases")
AccumulatePurchases = sPurchase + gPurchases
SetVariable "gPurchases", AccumulatePurchases
Destinationfield ("Payables.Purchases") = AccumulatePurchases
[code]

I'm not sure what you mean in your first line there. I am using a source field for the account and I don't know how to combine that with a script.

I think this is what I want to do however, to use a Before Document script (of which I just learned there was such a thing). I should have read your response more closely but I was in a hurry.

thanks