Share
ReportNeed to create a calculated field that would look at the contents of the payment terms id field and if it says "Net 60" show "60.
*This post is locked for comments
You’re offline. This is a read only version of the page.
2,935
Need to create a calculated field that would look at the contents of the payment terms id field and if it says "Net 60" show "60.
*This post is locked for comments
Rachel,
To accomplish it the way you describe I would just create a string calculated field with the following:
CASE
WHEN {Purchase Order History:Payment Terms ID} = 'Net 15' THEN '15'
WHEN {Purchase Order History:Payment Terms ID} = 'Net 30' THEN '30'
WHEN {Purchase Order History:Payment Terms ID} = 'Net 60' THEN '60'
ELSE {Purchase Order History:Payment Terms ID}
END
Change "Purchase Order History" in the calculation to whatever your table name is.
Hi Rachel,
So if your payment term id follow a pattern 'Net 60', 'Net 15' etc, you can use the below calculated field expression I have created. All you need to do is replace the table name 'Payment Terms Master' text with the table name where you have the payment terms id field .
SUBSTRING({Payment Terms Master:Payment Terms ID} , CHARINDEX(' ',{Payment Terms Master:Payment Terms ID})+1,LEN({Payment Terms Master:Payment Terms ID} )-CHARINDEX(' ',{Payment Terms Master:Payment Terms ID} )+1)
Below is the screenshot. Hope this helps.
I think my question has been misunderstood. The client currently has several payment terms ids. For example Net 60, Net 30, Net 15. When they display the payment terms id for each invoice this is what shows in the column. They really just want it to show 60, 30 or 15. Not the actual days past due.
So I need something like and IF/THEN statement. For example. IF payment terms id = 'Net 60" THEN 60.
Not sure how to do this in smartlist builder.
We did not just want to create new payment terms id's as this will not go back an update all the invoices entered previously.
Thanks
Rachel
Yes. I am agree with Naga suggestion without creating a calculated field you can get the Payment Terms from the table (Payment Terms Master). If you have any difficulties to join the table in SQL, then create a calculated field in SmartList Builder and achieve the same.
Please let me know if you need any helps regarding the same.
Hope this helps!!!
Hi Rachel,
If your intention is to display 'Due Days' from Payment Terms ID then you can get that into your smartlist by linking the Payment Terms ID of table 'Payment Terms Master'' to the Payment Terms ID of the table you have in your current smartlist definition. Once you link the tables on Payment Terms ID, you can add the 'Due Date/Days' field to your smartlist. I think this would avoid you to create a calculated field.
Please feel free to revert back with any queries you have.
Please have a look on the below link.
dynamicsgpland.blogspot.in/.../calculating-number-of-days-past-due-in.html
Hope this helps!!!
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (