How to retreive data from a field that contains a "|" symbol

[Former Member]

Hi,

Hopefully my title won't confuse anyone .

Well I am trying to create a group which would display an employee name(s).

In my case, the value comes from a field which can contain multiple values separated by a "|" symbol. See picture below.

I am pretty sure I will have to write a formula for this but simply don't know where to begin or how to write it.

Table use in this example is "ProductionProjectGroups" and will be linking this table to the "Employees" table which will display the actual employee name.

Does anyone knows how to display data if there is a "|" symbol in the field?

If you request more info please let me know.

Thank you in advance for the help.

Joe

[abhilash_kumar]

Hi Joe,

Do you need help on how to Join the two tables on the field that has the '|' symbol or are you looking for help on how to extract the employee's name from this field?

Could you also post a example of how this looks in the database versus how you want it to look on the report?

-Abhilash

[Former Member]

Hi Abhilash,

I am looking for help on how to extract the employee's name from this field.

I have also posted more pictures with details as requested.

Thank you

Joe

[Former Member]

Joe,

I think You just link the  Employee: LinkID -> ProductionProjectGroups: LinkID

Any wrong with this please update ASAP.

Note: In crystal we have link the key fileds after the data matchup then Group Expert -> Options ->options tab -> check customize group name field - > then click the radio buttion "use a formulas as group name " you wrote the formula like: insert data field as "Display Name".

Thanks,

DJ

[abhilash_kumar]

The way it is setup right, you will Not be able to join the LinkId field with the LinkIdInstaller field.

The best way to handle this is to create a SQL Query that splits the values in the LinkIdInstaller fields into multiple rows.

The SQL Query should have a new column that doesn't have the '|' character and which can be joined with the LinkId field in the Employee table.

What database are you reporting against?

-Abhilash

[Former Member]

This doesn't work, sorry.

Thanks

[Former Member]

I am using data from an SQL database that we use for our ERP system.

I know I have another report created by someone else, they create a formula to separate data between the "|" symbol. The formula is quite long and complicated and don't really undernstand it, I am not a programmer.

I can post the formula if you wish.

Thank you