We have price scheme in some of our sales whereby the prices changed according to the Work Date in NAV. However, when we retrieve the prices using JetReport, JetReport default to current date, even if we amend the system date. Is it possible to get JetReport to retrieve the prices base on the Work Date in NAV?
Date
Votes
5 comments
-
Jet Reports Historic Posts Hi,
Without knowing the exact tables/fields you are using to retrieve the information, it is hard to know what you need to do. I suspect that perhaps the field you are retrieving is a flow field in Nav and you need to set a filter on the corresponding flow filter field (such as the Date Filter field). Be aware if you are using an NL function together with NF functions, you should set the flow filters in both the NL function itself AND in the NF function which is retrieving the flow field.
Does that help? If not, can you please post the actual tables/fields you are using or perhaps even the Jet formulas?
Regards,
Hughes -
Jet Reports Historic Posts Hi,
The price scheme is under a shared customized Table - 50006 MiscTable with the following fields:
Type: Pricescheme
Quantity: 5
Unit Price: 10
Amount: 50
Starting Date: 01.11.11
Ending Date:
Type: Pricescheme
Quantity: 5
Unit Price: 11
Amount: 55
Starting Date: 01.12.11
Ending Date:
Type: Pricescheme
Quantity: 5
Unit Price: 12
Amount: 60
Starting Date: 01.01.12
Ending Date:
The price scheme data from Table 50006 MiscTable is send into customized Table - 50000 AgreementTable of the following fields, according to the Work Date set under NAV's Tools > Work Date :
Quantity: 5
Unit Price: 10
Amount: 50
If we amend the Work Date to Dec in NAV, the price reflected in the Agreement card will be $55. And $60 if Work Date is set to Jan next year. But as you can tell, the price retrieved by JetReport will show $50 instead of $55 (assuming it's Nov now) and still show $50 even when I amend the system date to Dec or Jan next year. -
Jet Reports Historic Posts Hi, I'm not trying to read the work date from NAV since the work date is default to today's date unless we amend it in NAV.
What I'm trying to do is to set the work date from Jet. Is that even possible in the first place?
If not, is there anyway to trigger it for Jet to run report base on the result of the work date instead of today's date?
Or is it possible to pull all the fields from the price scheme and filter it to the work date? This sound more possible, but how am I going to do a date filtering such that if an price scheme entry start and end date fall within the date in Jet, it will be display, otherwise, it will be filter off? -
Jet Reports Historic Posts It sounds like you have code on your Agreement Card form that gets the price from a function.
You cannot do this in JR, however you can write JetReports formula to get the same result.
Maybe something like
D4 = WORKDATE (Filter given in Excel)
D5 = NP("DateFilter";;D4)
D6 = NL("Last";"50006 MiscTable";;"Starting Date";D5)
E6 = NF(D6;"Quantity")
F6 = NF(D6;"Unit Price")
G6 = NF(D6;"Amount") -
Jet Reports Historic Posts I did a simple test and found that it works! Thanks Sebastiaan Lubbers!
Now the challenge for me is to figure out how can I add this as the 3rd level loop of NL function:
1. NL row2 : retrieve data from all NAV companies
2. NL row : retrieve data from above company's Agreement Table
3. NL last : retrieve data from above Agreement Table's Misc Table
Please sign in to leave a comment.