Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
Topic: Combining multiple records from one field Posted: 09 Feb 2018 at 3:36am
Hello everyone,
I have a Credentials field and some staff will have multiple credentials in that field (LSW, LPC). I have it included in a cross tab. with staff name and credentials in rows and services in columns.
The problem is when a staff member has multiple credentials, it's causing their services to be duplicated.
Is there a formula to combine the multiple records from one field? I would think the result could look like (LSW,LPC) like I listed above.
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Posted: 14 Feb 2018 at 4:01am
If you weren't using a cross tab, you could have a formula something like this:
StringVar creds;
if OnFirstRecord or {MyTable.staffid} = previous({MyTable.staffid}) then
creds := ""
else
creds := creds + ", ";
creds := creds + {MyTable.Credentials};
""
Put the formulain a details section to accumulate the credentials into a single string. To show the credentials, you would then have another formula like this:
StringVar creds;
creds
Put this in the group footer section of a staff member to show the credentials.
However, this won't work in a cross-tab. The only way I can think of to get this to work in a cross-tab would be to create a stored function in the database so that you pass the staff ID to the function and it will create the string using the same logic as the first formula above. Call this formula in a SQL Expression in your report so you can use it in the cross-tab.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum