Options

Splitting the total amount after import

JBhavJBhav Member Posts: 32
Hello everybody,

I need some ideas. Actually a payment.txt file is being imported through dataport and the records are saved in a temporary table. The payment file contains a grand total for all the transactions done by a particular customer. What i need to do is to split that grand total so that the amount paid for each transaction will be equal to the invoice amount.

How can i split that amount?

Thanks for your help in advance.

Regards,
Bhavna
Regards,
Bhavna :)

Comments

  • Options
    vijay_gvijay_g Member Posts: 884
    Can you show the transactions coming in temp table?

    what is the invoice amount(amount paid for each transaction will be equal to the invoice amount.)?
  • Options
    JBhavJBhav Member Posts: 32
    Hello,

    Please find attatched the extract of the payment table

    The grand total is the number in bold.

    01PB131050 1480034830012011/07/292011/070000000000150005000000

    For Eg:
    Customer No Invoice Invoice Amount Amount paid
    131050 1 500.00
    131050 2 500.05
    131050 3 500.00

    In the actual table, only after the import of the payment file, the amount paid field will be updated. What is required is that the grand total (150005) has to be split so that the amount paid will be updated for each transactrion made by the customer. I understand that the grand total has to be converted to decimal.

    Regards,
    Bhavna
    Regards,
    Bhavna :)
  • Options
    vijay_gvijay_g Member Posts: 884
    i am not getting you where you want to split this amount?
    you have given 3 trans. for a customer and you don't have seprator for total amount in your text file.
    so do you want to total amount in a seperate field or anything else?
  • Options
    JBhavJBhav Member Posts: 32
    vijay_g wrote:
    i am not getting you where you want to split this amount?
    you have given 3 trans. for a customer and you don't have seprator for total amount in your text file.
    so do you want to total amount in a seperate field or anything else?


    I want to split it in the actual table. In the temporary table the 1st row contains the grand total of the 3 transactions i stated in the example above. I have been able to retrieve the grand total by using this code:

    PaymentAmtx := COPYSTR(PaymentFile."Text Line",50,12);
    PaymentAmtx := CONVERTSTR(PaymentAmtx,' ','0');
    EVALUATE(PaymentAmt,PaymentAmtx);
    PaymentAmt := PaymentAmt / 100;

    At this moment my actual table is like this:

    Customer_No Invoice Invoice_Amount Amount_paid
    131050 1 500.00
    131050 2 500.05
    131050 3 500.00

    After importing the payment.txt file in the temporary table, i have used a setfilter on the customer number. Once found, the table should be updated to this so that the amount paid is equal to the invoice amount and the sum of all the amount paid is equal to the grand total:

    Customer_No Invoice Invoice_Amount Amount_paid
    131050 1 500.00 500.00
    131050 2 500.05 500.05
    131050 3 500.00 500.00


    Regards,
    Bhavna
    Regards,
    Bhavna :)
  • Options
    JBhavJBhav Member Posts: 32
    Hello everybody,

    After all these headaches, finally the problem has been solved. :D

    I was using a wrong loop.
    Regards,
    Bhavna :)
Sign In or Register to comment.