Hi,
I`m working on the jet report and I want to get some data from GL Entry with added information "creation date".
I want to link it by Entry No. , but here I have faced with a problem.
In GL register we have 2 tables From Entry & To Entry No. .
There is any possibility to make a range filter by 2 columns from GL Register
ex 3454 & .. & 5432 = creation date 14.06.2018 (something similar like vlookup with "True")
I will be grateful for some help.
Hello Tomasz -
If you simply wanting to *display* the Creation Date for records you are retrieving from the G/L Entry table, you could use something like this:
(you may need to open the image in another window to see it more clearly)
If you are wanting to *filter* your G/L Entry records by Creation Date, you could use two NL(Filter) functions.
The first would list the To Entry No. for all records in the G/L Register table where the Creation Date field matched your criteria and another to list the From Entry No. for all such records.
Then, your NL(Rows) function {on the G/L Entry table} could filter the Entry No. by the NP(Union) of the two NL(Filter) functions.
I hope that helps
Hi,
Thank you for replaying for my massage.
When I was trying to use your solution:
This is only working when NL function will find Date to or Date from. As you can see when both fields are empty (this is caused by range data in G/L Register) I cannot get creation Date basis on Range. 
Do you have any other solution ? :)
Hello Tomasz -
If you want to show only those G/L Entry records where there is a corresponding Creation Date in the G/L Register table, you would need to filter the NL(Rows) function that is retrieving those G/L Entry records.
There is more than one way to do that.
Here is one method:
The results:
Hi Lews,
I want to show all G/L entries for the specific period for example for whole 2016.
And to this entries I want to add creation date from G/L Register. With your solution I`m not able to do that or I`m doing smth wrong :)
Anyway thanks for the advice but I will be greatful for your help :)
As you can on my screen still after retriving data i do not have creation date for all entries.
Thank you,
Tomek
Hello Tomek -
Your example would indicate that not all G/L Entry records have a corresponding Creation Date in the G/L Register table (I know that this is true of my data, as well).
Your example is showing data when it does exists.
Hi,
Yes on my example of G/L Entry there is no match 1 (entry no) = 1 (to entry no or from entry no. - > this could be range like 300-315)
That`s why I have difficulties with my report. Looking on your screen you have match 1=1, because you do not have empty cells.
Are you sure that your solution is good for my case?
Hi Tomasz -
In my example, by referencing the NP(Union) function I created...
=NL("Rows","G/L Entry",,"Entry No.",$C$6)
I only show those G/L Entry records which *do* have a matching record in the G/L Register table.
If I did not include that filter, I would see all my G/L Entry records (including those that do not have a matching record in the G/L Register). So, I believe your function is delivering the correct results.
Then this option is not good for me, because I need to get creation date for all g/l entries.
If it possible to do?
Tomasz -
While each G/L Entry record has a Posting Date, it does not appear that *all* G/L Entry records have a Creation Date.
Since that data does not exist, it is not possible to report on it from those tables.
Ok, I understand. Thanks for your time :), then there is no other way get creation date for all g/l entries in ONE retriev ?
My current solution is using 2 retrievs:
1 sheet: G/L entry with entry no.
2 sheet: G/L Register with creation date + to entry no. & from entry no.
I`m linking both by classing vlookup with value "TRUE", because then it`s looking after range.
No solution to this?
I see Tomasz dilemma... what if the entry no. we want to link to the G/L Register is between From and To Entry No.? How do we be able to capture this?