Hi,
Document references for customers can be setup through various places such as the customer transaction form, the customer list page, the collections list page, etc.
Unfortunately, there is no form that allows a quick overview of all document references that have been entered for a single customer as the document references are saved in relation to the table / record they refer to.
My question is whether somebody has an idea how I might be able to see all document references from the customer table - without destroying the relationship to the individual tables where the references have been setup? What is the most efficient way how to realize this and what are the major risks?
Would be great if somebody could share their experience.
Many thanks and best regards,
Ludwig
*This post is locked for comments
I thought about this some more last night. You really wouldn't even need to have a temporary table that could contain the note itself and reference fields, you would just need to track the RecId of the DocuRef record that you needed to display in the grid. Then in your custom form, use DocuRef as the main data source with a join into your temporary table on DocuRef.RecId == TmpNotes.RefRecId just filter down on which records to show. Seems like a very easy and straightforward approach. There are probably already Tmp tables in the AOT with a RefRecId you could use.
Thank you Brandon and Sohaib for sharing your ideas and experiences. Helps a lot. Cheers, Ludwig
Specifically, for a given production order, I had to show all Document handling notes attached to that order, any ProdBOM record associated with that order, and ProdRoute record associated, and BOM or Route record, then the InventTable record for the finished good, then any ProdTable parent records recursively until it reached a SalesLine record, then attached to that SalesLine's header SalesTable record, and it's CustTable record, and so on.
I've done this with a temporary table, executing multiple queries in sequence each inserting DocuRef records from separate tables into the composite result set in the temporary table, and then displayed that in a form. Is that an approach that works for your case?
Hello Brandon,
Yes that is my interest. You summarize this correctly.
Ludwig
What are you requiring? That a user could open a form and see Document handling records not just for the CustTable record, but also for individual SalesTable records for that customer, and SalesLine records for those orders, etc.? Is that your interest?
Hi Ludwig,
As we know already the documents are saved with RefRecId. Speaking in functional language, documents are saved per table. Or you can say, documents are saved with reference of Table. These are NOT saved with reference of entity.
Customer itself is a party/ entity, it may has many activities such as Transactions, collections, settlements, adjustments (As you exemplified above)
For each activity of customer as data goes to different table, so associated documents are saved with reference of different tables.
Now let’s come to your issue and how can fix it.
A possible way seems a customization here (which does not need any change in document framework). If I were in your shoes I will go with some solution such as below
Create a form/setup which will define Entity and related tables:-
EntityName |
MainTableName |
ChildTableName |
Notes/Reason |
Customer |
CustTable |
CustTrans |
For customer transactions |
Customer |
CustTable |
LedgerJournalTrans |
For any adjustments where accountType is customer or where offfsetaccountType is customer |
Customer |
CustTable |
AnyOtherTable |
Any reason |
VendTable |
VendTable |
VendTrans |
Any reason for this new entity |
|
|
|
|
After that I can ask my developer to create a form, which will show me all documents per Entity/Module.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,151 Super User 2024 Season 2
Martin Dráb 229,993 Most Valuable Professional
nmaenpaa 101,156