Hi all,
I have an issue I seem to be having slight trouble with. Nested Functions aren't my strongest suit yet (if anyone has a link to the Q&A or guides where I can learn some more, that would also be great).
I Have the following function in C5 : =NL("columns=5";"Vendorbankacc";"Vendorno.";"Code";"RP"), to get Colums of all vendors who have a bank account with the dubbed code RP. This works just fine.
What i really is to return the name instead of the number. The way I'm solving this at the moment, is simply to create a lookup in C6, the cell below, to return the name from the vendor table from a lookup in C5.
This is where I need help. I want to do something like this : =NL("columns=5";"Vendorbankacc";NL("First";"Vendor";"Name";"Vendorno.";"Vendorno.");"Code";"RP") or something. I know this doesn't work - but how do i in the Field return the name value instead of the code. Is it even possible?
Hope someone can help me.
/Kemad
Date
Votes
7 comments
-
Jet Reports Historic Posts Hi Kemad,
You need to come at it from the other way. Something like:
=NL("columns=5";"Vendor";"Name";"Vendorno.";NL("Filter","Vendorbankacc";"Vendorno.";"Code";"RP")) -
Jet Reports Historic Posts Ahh. Of course, you're right. I've changed it to: =NL("Columns";"Vendor";"Name";"Vendorno.";NL("Filter";"Vendorbankacc.";"Vendornr.";"Code";"RP"))
It no longer gives an error - but it also doesn't give any results, just blanks. I have created 6 bank accounts with the code RP, and my "easy fix" sheet works, so it should work if done correct. I must be missing something, or the filter isn't correct.
/Kemad -
Jet Reports Historic Posts Check your field names for errors, they're inconsistent in what you've told us (vendorno./vendornr.)
-
Jet Reports Historic Posts The names are corrects. That's simply because I am running NAV in Danish, so i am manually translating the fields before posting, so that it makes more sense for the community :)
My actual code is : =NL("Columns";"Kreditor";"Navn";"Kreditornr.";NL("Filter";"Kreditorbankkonto";"Kreditornr.";"Kode";"RP")) , which are all selected through lookup to in the J/FX assistant to be on the safe side. -
Jet Reports Historic Posts Quick update.
I changed the filter so that the function now looks as follows : =NL("Columns";"Kreditor";"Navn";"Kreditornr.";NL(;"Kreditorbankkonto";"Kreditornr.";"Kode";"RP"))
and in English: =NL("Columns";"Vendor";"Name";"Vendorno.";NL(;"Vendorbankacc.";"Vendorno.";"Code";"RP"))
Leaving out the Filter, when I evaluate the nested function, it returns with the correct Vendor no., but the colums still show up empty :S
I am just not strong enough in nested functions!! In my mind it should work. -
Jet Reports Historic Posts You definitely need the filter part, otherwise it's only going to give you one of your accounts back.
Given that it works for me, it's hard to debug without access to your actual system!
What I would do next would be to take the filter function, put it inside a rows function and check that it gives the correct vendor nos your are expecting.
NL("Rows"(NL("Filter";"Kreditorbankkonto";"Kreditornr.";"Kode";"RP"))
Then try all the vendors, maybe with a manual filter made of up all the vendor nos that the one above returns.
=NL("Columns";"Kreditor";"Navn";"Kreditornr.";"001|002|002|004")
If that doesn't produce some answers, I'm stuck! -
Jet Reports Historic Posts I had to take a few days with something else unfortunately. But returned to the code today, and got it running.
Huge thanks to you Teresa :) Your first code-string was correct. there was a human error mixed into everything unfortunately.
Thank you very much :)
Please sign in to leave a comment.