I have some records in the Payroll Ledger Entry table that have an incorrect value in the Taxable Amount field via data conversion. I am attempting to modify this value with the following code. This code is working except for it does not appear to be setting a filter on the employee. I am getting one total for earnings and one total for pre-tax deductions and adding those totals together to come up with TaxableAmt total. I am then modifying each employee with this same total. How do I per employee get a total for earnings ,a total for pre-tax deductions, add those totals together and modify the record with the reslult? PayrollLedgerEntry.SETCURRENTKEY(“Employee No.”,“Posting Date”,“Document Type”,“Document No.”,“Payroll Control Type”); PayrollLedgerEntry.SETRANGE(“Posting Date”,033104D); PayrollLedgerEntry.SETRANGE(“Employee No.”,Employee.“No.”); PayrollLedgerEntry.SETRANGE(“Payroll Control Type”,PayrollLedgerEntry.“Payroll Control Type”::Earnings); IF PayrollLedgerEntry.FIND(’-’) THEN BEGIN REPEAT EarnAmt := EarnAmt + PayrollLedgerEntry.Amount; UNTIL PayrollLedgerEntry.NEXT = 0; END; PayrollLedgerEntry.SETCURRENTKEY(“Employee No.”,“Posting Date”,“Document Type”,“Document No.”,“Payroll Control Type”); PayrollLedgerEntry.SETRANGE(“Posting Date”,033104D); PayrollLedgerEntry.SETRANGE(“Employee No.”,Employee.“No.”); PayrollLedgerEntry.SETRANGE(“Payroll Control Type”,PayrollLedgerEntry.“Payroll Control Type”::“Pre-Tax Deduction”); IF PayrollLedgerEntry.FIND(’-’) THEN REPEAT DedAmt := DedAmt + PayrollLedgerEntry.Amount; UNTIL PayrollLedgerEntry.NEXT = 0; PayrollLedgerEntry.RESET; PayrollLedgerEntry.SETRANGE(“Posting Date”,033104D); PayrollLedgerEntry.SETRANGE(“Employee No.”,Employee.“No.”); PayrollLedgerEntry.SETRANGE(“Payroll Control Code”,‘FIT’); IF PayrollLedgerEntry.FIND(’-’) THEN BEGIN REPEAT TaxableAmt := EarnAmt + DedAmt; PayrollLedgerEntry.“Taxable Amount” := TaxableAmt; PayrollLedgerEntry.MODIFY; UNTIL PayrollLedgerEntry.NEXT =0; END;
Are you running this code with the Employee Record Loop? Add “Amount” field in this key and then you can write like this: IF Employee.FIND(’-’) THEN Repeat PayrollLedgerEntry.SETCURRENTKEY(“Employee No.”,“Posting Date”,“Document Type”,“Document No.”,“Payroll Control Type”); PayrollLedgerEntry.SETRANGE(“Posting Date”,033104D); PayrollLedgerEntry.SETRANGE(“Employee No.”,Employee.“No.”); PayrollLedgerEntry.SETRANGE(“Payroll Control Type”,PayrollLedgerEntry.“Payroll Control Type”::Earnings); PayrollLedgerEntry.CALCSUMS(Amount); EarnAmt := PayrollLedgerEntry.Amount; PayrollLedgerEntry.SETRANGE(“Payroll Control Type”,PayrollLedgerEntry.“Payroll Control Type”::“Pre-Tax Deduction”); PayrollLedgerEntry.CALCSUMS(Amount); DedAmt := PayrollLedgerEntry.Amount; PayrollLedgerEntry.SETRANGE(“Payroll Control Code”,‘FIT’); PayrollLedgerEntry.MODIFYALL(“Taxable Amount”,(EarnAmt + DedAmt)); UNTIL Employee.NEXT = 0;
Please read this Add “Amount” field in this key and then you can write like this: as Add “Amount” field in the sumindexfield of this key and then you can write like this:
Also clear the filter on “Payroll Control Type” before modifyall complete code once again will be: IF Employee.FIND(’-’) THEN Repeat PayrollLedgerEntry.SETCURRENTKEY(“Employee No.”,“Posting Date”,“Document Type”,“Document No.”,“Payroll Control Type”); PayrollLedgerEntry.SETRANGE(“Posting Date”,033104D); PayrollLedgerEntry.SETRANGE(“Employee No.”,Employee.“No.”); PayrollLedgerEntry.SETRANGE(“Payroll Control Type”,PayrollLedgerEntry.“Payroll Control Type”::Earnings); PayrollLedgerEntry.CALCSUMS(Amount); EarnAmt := PayrollLedgerEntry.Amount; PayrollLedgerEntry.SETRANGE(“Payroll Control Type”,PayrollLedgerEntry.“Payroll Control Type”::“Pre-Tax Deduction”); PayrollLedgerEntry.CALCSUMS(Amount); DedAmt := PayrollLedgerEntry.Amount; PayrollLedgerEntry.SETRANGE(“Payroll Control Type”); //I forgot this line before PayrollLedgerEntry.SETRANGE(“Payroll Control Code”,‘FIT’); PayrollLedgerEntry.MODIFYALL(“Taxable Amount”,(EarnAmt + DedAmt)); UNTIL Employee.NEXT = 0;
Thanks for the response, Naveen. I have been unable to get back to this issue and try what you suggested. I will soon and let you know how it goes. Thanks again
Thanks for the help, Naveen. This code is doing exactly what I need for it to do.
I still have an issue here: If I run the code: PayrollLedgerEntry.SETRANGE(“Employee No.”,‘12345’) it modifies the taxable amount field correctly If I run the code: PayrollLedgerEntry.SETRANGE(“Employee No.”,Employee.“No.”); then EarnAmt gets ignored. The entry is modified by the value of DedAmt IF Employee.FIND(’-’) THEN Repeat PayrollLedgerEntry.SETCURRENTKEY(“Employee No.”,“Posting Date”,“Document Type”,“Document No.”,“Payroll Control Type”); PayrollLedgerEntry.SETRANGE(“Posting Date”,033104D); PayrollLedgerEntry.SETRANGE(“Employee No.”,Employee.“No.”); PayrollLedgerEntry.SETRANGE(“Payroll Control Type”,PayrollLedgerEntry.“Payroll Control Type”::Earnings); PayrollLedgerEntry.CALCSUMS(Amount); EarnAmt := PayrollLedgerEntry.Amount; PayrollLedgerEntry.SETRANGE(“Payroll Control Type”,PayrollLedgerEntry.“Payroll Control Type”::“Pre-Tax Deduction”); PayrollLedgerEntry.CALCSUMS(Amount); DedAmt := PayrollLedgerEntry.Amount; PayrollLedgerEntry.SETRANGE(“Payroll Control Type”); //I forgot this line before PayrollLedgerEntry.SETRANGE(“Payroll Control Code”,‘FIT’); PayrollLedgerEntry.MODIFYALL(“Taxable Amount”,(EarnAmt + DedAmt)); UNTIL Employee.NEXT = 0;
It should not make any difference. Are you looking the right Employee Card/Payroll Ledger Entries (for Employee No. 12345)?
Try to put this message after the EarnAmt calculation: IF Employee.“No.” = ‘12345’ then MESSAGE(’%1’,EarnAmt)
It seems that you also need to remove the filter PayrollLedgerEntry.SETRANGE("Payroll Control Code",'FIT');after each loop - Something like this perhaps?IF Employee.FIND('-') THEN REPEAT PayrollLedgerEntry.RESET; ... rest of code as above
Hi guys, thanks for the responses sv: when I used the PayrollLedgerEntry.RESET, I get the same result, the entry is modifided with the value of DedAmt Naveen: When I ran this code IF Employee.“No.” = ‘12345’ then MESSAGE(’%1’,EarnAmt) I don’t even get a message box, apparently I am not looking at the right Employee Card/Payroll Ledger Entries per employee. I wonder if this statement is setting the correcting filter: PayrollLedgerEntry.SETRANGE(“Employee No.”,Employee.“No.”); Thanks again for the direction, I am stumped on this one.
Sorry Naveen I had my REPEAT…UNTIL loop commented out. When I put this statement after the EarnAmt calculation: IF Employee.“No” = ‘123245’ THEN MESSAGE(’%1’,EarnAmt) I do indeed get a message box with the correct value in EarnAmt
So is that working now, or still you are not getting the correct values in the payroll Ledger Entries?
No, unfortunately it is not working. IF Employee.FIND(’-’) THEN REPEAT PayrollLedgerEntry.SETCURRENTKEY(“Employee No.”,“Posting Date”,“Document Type”,“Document No.”,“Payroll Control Type”, “Payroll Control Code”); PayrollLedgerEntry.SETRANGE(“Posting Date”,033104D); PayrollLedgerEntry.SETRANGE(“Employee No.”,Employee.“No.”); PayrollLedgerEntry.SETRANGE(“Payroll Control Type”,PayrollLedgerEntry.“Payroll Control Type”::Earnings); PayrollLedgerEntry.CALCSUMS(Amount); EarnAmt := PayrollLedgerEntry.Amount; PayrollLedgerEntry.SETRANGE(“Payroll Control Type”,PayrollLedgerEntry.“Payroll Control Type”::“Pre-Tax Deduction”); PayrollLedgerEntry.SETFILTER(“Payroll Control Code”,’<>%1&<>%2’,‘401K EE’,‘DEFERRED COMP’); PayrollLedgerEntry.CALCSUMS(Amount); DedAmt := PayrollLedgerEntry.Amount; PayrollLedgerEntry.SETRANGE(“Payroll Control Type”); PayrollLedgerEntry.SETRANGE(“Payroll Control Code”,‘SS EE’); PayrollLedgerEntry.MODIFYALL(“Taxable Amount”,(EarnAmt + DedAmt)); UNTIL Employee.NEXT = 0; I get the desired result in EarnAmt and DedAmt, but the record is modified with the value of DedAmt; instead of EarnAmt + DedAmt
I just test and it works fine for me. I also put the message for a total of EarnAmt and DedAmt before modifyall. It modified the payroll Ledger Entries with the Payroll controll code ‘SS EE’ with (EarnAmt+DedAmt). Check if you find any difference with me code: IF Employee.FIND(’-’) THEN REPEAT PayrollLedgerEntry.SETCURRENTKEY(“Employee No.”,“Posting Date”,“Document Type”,“Document No.”, “Payroll Control Type”,“Payroll Control Code”); //PayrollLedgerEntry.SETRANGE(“Posting Date”,033104D); PayrollLedgerEntry.SETRANGE(“Employee No.”,Employee.“No.”); PayrollLedgerEntry.SETRANGE(“Payroll Control Type”,PayrollLedgerEntry.“Payroll Control Type”::Earnings); PayrollLedgerEntry.CALCSUMS(Amount); EarnAmt := PayrollLedgerEntry.Amount; IF Employee.“No.” = ‘AH’ THEN MESSAGE(’%1’,EarnAmt); PayrollLedgerEntry.SETRANGE(“Payroll Control Type”,PayrollLedgerEntry.“Payroll Control Type”::“Pre-Tax Deduction”); PayrollLedgerEntry.SETFILTER(“Payroll Control Code”,’<>%1&<>%2’,‘401K EE’,‘DEFERRED COMP’); PayrollLedgerEntry.CALCSUMS(Amount); DedAmt := PayrollLedgerEntry.Amount; IF Employee.“No.” = ‘AH’ THEN MESSAGE(’%1,%2’,DedAmt,EarnAmt+DedAmt); PayrollLedgerEntry.SETRANGE(“Payroll Control Type”); PayrollLedgerEntry.SETRANGE(“Payroll Control Code”,‘SS EE’); PayrollLedgerEntry.MODIFYALL(“Taxable Amount”,(EarnAmt + DedAmt)); UNTIL Employee.NEXT = 0;
Hi Naveen, I have found my problem IF Employee.FIND(’-’) THEN REPEAT PayrollLedgerEntry.SETCURRENTKEY(“Employee No.”,“Posting Date”,“Document Type”,“Document No.”, “Payroll Control Type”,“Payroll Control Code”); //PayrollLedgerEntry.SETRANGE(“Posting Date”,033104D); PayrollLedgerEntry.SETRANGE(“Employee No.”,Employee.“No.”); PayrollLedgerEntry.SETRANGE(“Payroll Control Type”,PayrollLedgerEntry.“Payroll Control Type”::Earnings); PayrollLedgerEntry.CALCSUMS(Amount); EarnAmt := PayrollLedgerEntry.Amount; IF Employee.“No.” = ‘AH’ THEN MESSAGE(’%1’,EarnAmt); PayrollLedgerEntry.SETRANGE(“Payroll Control Type”,PayrollLedgerEntry.“Payroll Control Type”::“Pre-Tax Deduction”); PayrollLedgerEntry.SETFILTER(“Payroll Control Code”,’<>%1&<>%2’,‘401K EE’,‘DEFERRED COMP’); PayrollLedgerEntry.CALCSUMS(Amount); DedAmt := PayrollLedgerEntry.Amount; IF Employee.“No.” = ‘AH’ THEN MESSAGE(’%1,%2’,DedAmt,EarnAmt+DedAmt); PayrollLedgerEntry.SETRANGE(“Payroll Control Type”); EarnAmt calculates correctly up to this point **************************************************************** PayrollLedgerEntry.SETRANGE(“Payroll Control Code”,‘SS EE’); **************************************************************** This line of code sets EarnAmt to 0 in my database DedAmt remains calculated correctly, so what I have is 0 + DedAmt Does the employee filter need to be set again? **************************************************************** PayrollLedgerEntry.MODIFYALL(“Taxable Amount”,(EarnAmt + DedAmt)); UNTIL Employee.NEXT = 0;
At the risk of repeating myself: You maintain the filter on “Payroll Control Code” after each loop; this time with the value ‘SS EE’ - is this intended?
Hi sv, My intention is to modify only the record with a Payroll Control Code of ‘SS EE’
Yes, but the filter remains active when you calculate EarnAmt after the first loop [B)] You need either the aforementioned RESET or aPayrollLedgerEntry.SETRANGE("Payroll Control Code");after the MODIFYALL.
SV is right. You need to clear the “Payroll Control Code” filter for each pass. So After: PayrollLedgerEntry.SETRANGE(“Payroll Control Type”,PayrollLedgerEntry.“Payroll Control Type”::Earnings); write: PayrollLedgerEntry.SETRANGE(“Payroll Control Code”); so the code will be IF Employee.FIND(’-’) THEN REPEAT PayrollLedgerEntry.SETCURRENTKEY(“Employee No.”,“Posting Date”,“Document Type”,“Document No.”, “Payroll Control Type”,“Payroll Control Code”); PayrollLedgerEntry.SETRANGE(“Employee No.”,Employee.“No.”); PayrollLedgerEntry.SETRANGE(“Posting Date”,033104D); PayrollLedgerEntry.SETRANGE(“Payroll Control Type”,PayrollLedgerEntry.“Payroll Control Type”::Earnings); PayrollLedgerEntry.SETRANGE(“Payroll Control Code”); //This is new line added PayrollLedgerEntry.CALCSUMS(Amount); EarnAmt := PayrollLedgerEntry.Amount; PayrollLedgerEntry.SETRANGE(“Payroll Control Type”,PayrollLedgerEntry.“Payroll Control Type”::“Pre-Tax Deduction”); PayrollLedgerEntry.SETFILTER(“Payroll Control Code”,’<>%1&<>%2’,‘401K EE’,‘DEFERRED COMP’); PayrollLedgerEntry.CALCSUMS(Amount); DedAmt := PayrollLedgerEntry.Amount; PayrollLedgerEntry.SETRANGE(“Payroll Control Type”); PayrollLedgerEntry.SETRANGE(“Payroll Control Code”,‘SS EE’); PayrollLedgerEntry.MODIFYALL(“Taxable Amount”,(EarnAmt + DedAmt)); UNTIL Employee.NEXT = 0;