0

Concatenate

Jet Reports Historic Posts

February 03, 2008 02:26

None

Follow

Hello,

I need to concatenate field values from automatic rows to use as a filter on a filter field.

for example I am getting rows of departments

IT
HR
ADMIN
LEGAL

and i need combine them all as "IT|HR|ADMIN|LEGAL" This has to be automatically done so it can be used as a filter on the filter field while the report is running.

I've done this before many years back and I've forgotten how to do it… please advise if you have any ideas.

thanks in advance!

• Facebook
• Twitter
• LinkedIn

1 comment

• 0
  
  Jet Reports Historic Posts February 03, 2008 11:30

  Hi Yvon7979,
  
  I know 2 ways to do this with Jet Reports:
  - NP("Join"…)
  - NL("Filter"…)
  
  NP("Join"…)
  Suppose the Rows with Departments function is in C3 and looks like this: NL("Rows";"Department table";"Department code").
  
  Then in C6 you may enter =NP("Join";$C3:$C4;"|") to build a text string with the departmant codes separated by a pipe symbol ("|").
  
  Unfortunately this string and with a pipe symbol which is not accepted by the database (at least not in my NAV database). So you have to remove it. In C7 you may enter =LEFT($C6;LENGTH($C6)-1).
  
  Then you will have the filter string you need.
  
  NL("Filter"…
  An easier way could be the NL("Filter"…). Instead of using the NL as a replicator (e.g. rows) you build a filter string.
  
  The function in my example would be NL("Filter";"Department table";"Department code").
  
  Don't worry about the odd result this function gives you in design mode… it works :)
  
  Is this what you are looking for? I'm curious if anyone else knows other methods to do this!