2. PHPRunner General questions
  3. [SOLVED]  Reports with multiple value in one field exploded
This topic is locked
[SOLVED]

 Reports with multiple value in one field exploded

 		10/3/2018 7:34:21 PM
PHPRunner General questions
W
 WilliamB authorDevClub member

Hello,
I have a report view that shows clients grouped by insurance companies that will be paying for the services. This all works fine. I needed to add the option to select multiple insurance companies for the same client. I made the lookup field allow multiple input. Now my problem is that when I run the report it has a group with both/all the insurance companies listed. eg. Blue Cross,WCB. What I need is to have the client listed in each insurance company group and not have multiple insurance companies in one group. I am guessing that this needs to be done with the SQL query. Is there a way to make the records with multiple comma separated insurance companies pull new row with only one?
Hope this makes sense.
Thanks
admin 10/5/2018

You can use 'View as' Custom and replace commas with

$value = str_replace(",", "
", $value);
W
 		WilliamB authorDevClub member 10/5/2018

I am not trying to change how the report looks.
I need the client in two different groups. That is why I think I need an MYSQL statement changing the data that is pulled. For example I want to make the one record with 3 insurance companies listed into 3 separate records with unique insurance companies.
fhumanes 10/5/2018

Hello,
Check this article to see if it works. Document
Regards,
W
 		WilliamB authorDevClub member 10/5/2018



Hello,
Check this article to see if it works. Document
Regards,


This works perfectly on phpMyAdmin. I will see tonight if it works in app.
Thanks!
lefty 10/5/2018



This works perfectly on phpMyAdmin. I will see tonight if it works in app.
Thanks!


I think the easiest way out here is to add another field in Insurance companies table and assign an unique ID to each insurance company . Then Group in your report on this ID then second group on client . That way the grouping will show only the insurance companies belonging to that client. If you have existing data without the ID you can run query on the database to update the Insurance companies with the correct ID. If you do that make sure you backup first for any anomalies in your query. As far a multiline/concat , Then use admin advice

for view as custom for [size="2"]$value [/size]

[color="#666600"][size="2"]Stored Procedure query Link[/size]
W
 		WilliamB authorDevClub member 10/6/2018



This works perfectly on phpMyAdmin. I will see tonight if it works in app.
Thanks!


Thanks for the help. PHPRunner did accept the SQL query. It works perfectly.
lefty 10/7/2018



I think the easiest way out here is to add another field in Insurance companies table and assign an unique ID to each insurance company . Then Group in your report on this ID then second group on client . That way the grouping will show only the insurance companies belonging to that client. If you have existing data without the ID you can run query on the database to update the Insurance companies with the correct ID. If you do that make sure you backup first for any anomalies in your query. As far a multiline/concat , Then use admin advice

for view as custom for [size="2"]$value [/size]

[color="#666600"][size="2"]Stored Procedure query Link[/size]


Can you tell me what query it accepted . I would like to use this in the future if possible . Thanks