Last Invoice Date in List of Customer Business Partners

[kedalenechong]

Hi All

Is it possible to show Last Invoice Date in List of Customer Business Partner Master Data or Query Statement?

Kedalene Chong

[Felipe_Lima · Accepted solution]

Hi @kedalenechong ,

There are multiple ways to do this, a simple one would be assigning an FMS to an existing or custom field, triggering it from the field CardCode.

A VERY basic one:

SELECT
    MAX(RefDate)
FROM JDT1
WHERE 1 = 1
    AND ShortName = $[$5.0.0]
    AND TransType IN ('13', '14') -- Here you define which Objects are being looked into. In this case, the query is searching for AR Invoices and Credits, respectively.

Of course, the query above will bring the last document, which might be either an Invoice or a Credit. You may just adjust it to bring the document type you need, taking into consideration the differences between customers and suppliers. Also, it's not considering whether the document is cancelled or not, so, if you need to take it into account, it's a different story. This is just something to give you an idea on how this could be done and play around.

Apart from this, you could use a Stored Procedure to update data in a UDF or even use Add-Ons such as B1UP to do the job. Again, multiple ways.

I hope it helps,

Felipe

[kedalenechong · Accepted solution]

Thanks for the tip! I finally managed to see the Last Invoice Date appear in Customer BP Master Data but when select from OCRD UDF value is empty?

[Felipe_Lima · Accepted solution]

Hi @kedalenechong ,

The last invoice date must be recorded in the database to be retrieved in a query.

If your goal is to use this field for any purpose beyond the screen information achieved via FMS, I suggest using the SP PostTransactionNotice to get the UDF updated as soon as a new transaction is posted. Again, you have to gather all the requirements first, such as document types and statuses to be included, and then start creating the logic in it.

Regards,
Felipe