Auto Transfer Fields

Rikarddo · 2018-01-31

I'm trying to implement a process that calculates the average of days where the customer actually pays an invoice and inserts into table Customer.
I'll try to be as specific as possible:

I've created a function that calculates the average number of days that a customer pays for invoices taking in account 2017.
The goal is that when I create an order, a new field is calculated by the order creation date with the average days previously entered on the customer's card. So far so good.

My question is: I need that this field to go to the Sales Invoice Header table and also to the Customer Ledger Entry table (maybe detailed also) where this new field may be editable by final user (is it possible)?
The solution would be to create the fields in the tables with the same "Field No." (AutoTransferFields?) And this would work, since in the Customer table the Field is in days, in Sales Header and Sales Invoice Header and Customer Ledger Entry in date format?

Or is there another solution?

Rikarddo · 2018-02-01

l

Rikarddo · 2018-02-01

Found the solution . Just wrote the code here

LOCAL PostCustomerEntry(SalesHeader2 : Record "Sales Header";TotalSalesLine2 : Record "Sales Line";TotalSalesLineLCY2 : Record "Sales Line";DocType : Option;DocNo : Code[20];ExtDocNo : Code[35];SourceCode : Code[10])
WITH SalesHeader2 DO BEGIN
  GenJnlLine2.INIT;
  GenJnlLine2."Data Prevista":="Data Prevista"; //Roliveira 2018-02-01 Passagem de Data Prevista para Customer LEdger Entry

Thanks for the help!

Slawek_Guzek · 2018-01-31

You have different Customer Ledger Entires posted when the Invoice is posted, and for payments, or you can use Detailed Customer Ledger Entries. If you wan to calculate how long would it take the invoice to be paid, you can work it out without modifying anything.

A Creation Date field on Sales Header table is not required imho - unless you want to know how long it took from creating the Sales Header record to get the invoice posted from it fully paid, which, at least for me, does not make much sense,

You would need to add the field to Sales Invoice Header (posted invoice) if yo want to store calculation result in there, rather than calculate it on the fly in the report. But event then you would not need to transfer any extra Creation Date field from Sales Header table.

Rikarddo · 2018-01-31

Slawek_Guzek wrote: »

You would need to add the field to Sales Invoice Header (posted invoice) if yo want to store calculation result in there, rather than calculate it on the fly in the report. But event then you would not need to transfer any extra Creation Date field from Sales Header table.

Let me see if i explained well what i wanted,

When post a invoice, it creates on customer ledger entry fields such as Document Date, Due Date etc.
My new Date would be calculated from the previous average of days already inserted in customer table plus the order date.
So i just need to calculate the new date when invoice is posted and store it in Sales Invoice Header and then it would transfer to Customer Detailed Entry?

Rikarddo · 2018-01-31

But the main goal is store the new date in Customer Ledger Entry Table...and for what i understand it is more complex

Slawek_Guzek · 2018-01-31

1. Average payment days goes to Customer records.
2. a process (report / codeunit / function in the code) is fired after posting payment. This process is supposed to go through Customer invoices and payments and calculate the average.

Alternatively it could just process the last payment, and calculate the days it took to close the invoice and then update the average payment days on Customer using the formula:

"payment average days" = (total days waiting for payment) / (total posted and paid Invoices)

where you can work out the "total days waiting for payment" as "current average" * ("No of posted and paid invoices" - 1) - to not to take into account the invoice which has been just paid and it is processed just now.

You can calculate payment 'age' for each invoice using the Posting Date - in most case it will be good enough - unless your users often change posting dates when posting invoices or payments

Rikarddo · 2018-02-01

My problem is not how to calculate the average days. But fill the date in Customer Ledger Entry

This is what i did:

I added a field in Sales Header table which is "Data Prevista", It is date type . So same field I added to table 81 and table 21.

Then I went to codeunit 80 and searched by .init ( till I found Genline.init)

after GenJnlLine.INIT just added

GenJnlLine.”Data Prevista”:= “Data Prevista”;

Then in CU 12 the Function Called PostCust ,inside this function i Wrote

CustLedgEntry.”Data Prevista” := “Data Prevista”;

But it didn't work. Any thoughts?

Slawek_Guzek · 2018-02-01

What I am trying to tell you is you have set youself to solve your problem in a very particular way which requires modifying standard tables and posting codeunits.

I have tried to understand your business requirement (calculate average invoice payment time for a given customer if I got it right) and proposed a solution achieving the results without the need of modifying Customer Entry table or posting routines.

Back to your original question. What you have done looks OK, should work, but perhaps you have put the lines in a wrong place. With version on NAV are U using? There are many changes in posting code betweeen version so just seeing these two lines I cannot tell anyting. Also tell us the function names you have changed

Rikarddo · 2018-02-01

NAV Version 2016

CU 80 ON RUN

...
IF Invoice THEN BEGIN
// Post sales and VAT to G/L entries from posting buffer
LineCount := 0;
IF InvPostingBuffer[1].FIND('+') THEN
REPEAT
LineCount := LineCount + 1;
Window.UPDATE(3,LineCount);

GenJnlLine.INIT;
GenJnlLine."Data Prevista":="Data Prevista"; //Roliveira 2018-02-01 Passagem de Data Prevista para Customer LEdger Entry

...

Then on CU 12 Function Local PostCust

// Post customer entry
  CVLedgEntryBuf.CopyToCustLedgEntry(CustLedgEntry);
  CustLedgEntry."Amount to Apply" := 0;
  CustLedgEntry."Applies-to Doc. No." := '';
  [b]CustLedgEntry."Data Prevista":="Data Prevista";[/b] // Roliveira 2018-02-01 Passagem Data Prevista para 
  //Customer Ledger Entry
  CustLedgEntry.INSERT(TRUE);

It fills Table Cus. Ledger Entry with empty value

aseigle · 2018-02-01

not sure I'm totally understanding what you're trying to achieve, but if you look at the Entry Statistics on the Customer card, there's an Average Collection Period which tells you the average time it takes to collect based on the "buckets." Maybe you could leverage that?

Rikarddo · 2018-02-01

l

Rikarddo · 2018-02-01

Found the solution . Just wrote the code here

LOCAL PostCustomerEntry(SalesHeader2 : Record "Sales Header";TotalSalesLine2 : Record "Sales Line";TotalSalesLineLCY2 : Record "Sales Line";DocType : Option;DocNo : Code[20];ExtDocNo : Code[35];SourceCode : Code[10])
WITH SalesHeader2 DO BEGIN
  GenJnlLine2.INIT;
  GenJnlLine2."Data Prevista":="Data Prevista"; //Roliveira 2018-02-01 Passagem de Data Prevista para Customer LEdger Entry

Thanks for the help!

Rikarddo · 2018-02-01

Thanks for all the help! Got the solution as mentioned above!

Auto Transfer Fields

Best Answers

Answers

Categories