18
i am trying to add the SOP number to the Customer AR Statement. i know i need to add a relationship between the AR Statements Temp Trx table and the SOP History Trx table but when I do this there is no existing key that has just the document number, only document number and transaction type which is not the same as the trx type all in the statement trx temp table.
Has anyone done this? I must be missing something
Thanks
-Len
Hi Len,
This is going to be a tough one. The RM Open is probably your best bet as there is a link to the Customer number, document number.
The Documents that POST in SOP are and Invoice and Return. Those values are as such in the SOP module/tables.
IN RM they are as follows:
However, as you know the SOP the Order is a 2 in the SOP tables and there is no equivalent to that in RM. That is because an Order is just a document that lists out what the customer is intending to purchase from your company. It's not posted. It is at some point transferred into an Invoice in SOP. It's the Invoice that is posted in SOP that goes over to RM and creates an RM Invoice. So, you are trying to get that Order number from that Invoice to print.
NOTE: Only Invoices and Returns in SOP post. So that is why there is both types in SOP and RM. They just have different values in their respective tables.
And the only thing that really provides the link in SOP between the SOP Order and the SOP invoice is the MASTER NUMBER which is in the SOP header table. (SOP10100/SOP30200). However, if there is one field that gets populated on the Invoice which tells you which order it came from. That is the Original number. It should be populated with the order number itself and you should be able to pull that field right out onto the report. So I did some testing and finally was able to get this to work. Give this a try.
|
1. |
Click on the Tables in the top menu-bar, and then click Tables . |
|
2. |
In the Tables window, click RM_Statements_TRX_TEMP , and then click Open . |
|
3. |
In the Table Definition window, click Relationships . (The RM Open File is listed.) |
|
4. |
In the Table Relationship window, click New . |
|
5. |
In the Table Relationship Definition window, click the ellipsis button [...] next to the Secondary Table field. |
|
6. |
In the Relationship Table Lookup window, click RM Open File , and then click OK . |
|
7. |
In the Secondary Table Key list, click RM_OPEN_Key1 . |
|
8. |
Match the appropriate fields: |
|
9. |
Click OK to close the Table Relationship Definition window. |
|
10. |
Exit out of the Table Relationship window. (The RM Open File is listed twice.) |
|
11. |
Click OK to close the Table Definition window. |
|
12. |
Leave the Tables window open. |
|
1. |
In the Tables window, click RM_OPEN , and then click Open . |
|
2. |
In the Table Definition window, click Relationships . |
|
3. |
In the Table Relationship window, click New . |
|
4. |
In the Table Relationship Definition window, click the ellipsis button [...] next to the Secondary Table box. |
|
5. |
In the Relationship Table Lookup window, click Sales Transaction History , and then click OK . |
|
6. |
In the Secondary Table Key list, click SOP_HDR_HIST_Key1 . |
|
7. |
Match the appropriate fields: For the primary table, select Document Number , and the secondary table will display the SOP number. Do not match the SOP type to any primary table. The link should be as follows: • RM Open File - Document Number = Sales Transaction History - SOP Number Note The SOP type on the left-hand side should be left blank. This is key to getting this to work. If you fill this in it will not work. |
|
8. |
Click OK to close the Table Relationship Definition window. |
|
9. |
Exit the Table Relationship window. |
|
10. |
Click OK to close the Table Definition window. |
Next go into the Layout and from the Sales Transaction History table pull out the Original Number field into the Body of the report and save the changes. Once you give the GP access to the modified report, the Order number linked to the Invoice should print. Hopefully it works for you.
Thank you for using Microsoft Dynamics Communities,
Angela Ebensteiner
Sr Technical Advisor
Microsoft Dynamics GP
i think i need the Sales Order Hist Trx table SOP30200 in play, as I am looking for the orignumb field (sales order from which the invoice was derived). The client creates SOP type orders and later transfers them to invoices.
-Len
Hi Len
The SOP Number will be used as the Document Number in the RM_OPEN table and the Batch Sources will be "Sales Entry" so you know that the transaction came from SOP.
The Transaction Types will need to be mapped from RM doc types to SOP Doc types.
If you just want the number it is already there as the number in RM is the same.
Regards
David
If you want the method, these examples show how:
From the GP Power Tools Samples page: http://winthropdc.com/GPPT/Samples
https://winthropdc.wordpress.com/2017/03/02/gppt-adding-vendor-item-number-to-sop-documents/
https://winthropdc.wordpress.com/2017/03/06/gppt-adding-data-from-any-sql-table-to-sop-documents/
Did You sort out some technique for looking out for this, expecting this is the case will you share the methodology?
If you cannot create the relationship you need. You can use GP Power Tools and its Custom RW Function feature to achieve what you need.
Regards
David
Hi Len-
Just thinking off the top of my head here -- you may want to try to see if you can add a relationship to the RM Transaction Open file instead. Customer number, document number and transaction type are the key fields in that table.
I hope this helps. Hopefully!
Joe
Share
Report
All responses (
Answers (
