how to display multi select in multiple columns?

Mohammad Absi commented on Nov 15, 2022

I have field in a table called 'whySmoke' it contain reasons as numbers comma separated Ex: 1,2,3 1,2,3 : are reasons and i want to bring the value of each number from reasons table then display every reason in separate column

Sebastian Morales commented on Nov 16, 2022

We have the Map process which is perfect for your case:

https://www.koolreport.com/docs/processes/map

Pipe your row through Map and convert "whySmoke" column into multiple columns, then return the new row.

Mohammad Absi commented on Nov 16, 2022

Hello Sebastian I am new to Koolreport can you please help me to do this

tips sent :Receipt number: 2GP69039RF803103B

protected function setup()

{
    $this->src('jbcpDB')
    ->query(
        DB::table("patients")
        ->join("reasons",'patients.whySmoke','=','reasons.reasonID')   //here I need to split them before display 
         
        ->select(
            'patients.patientID',
            'patients.patientName',
            'reasons.reasonName as whySmoke',
        )
    )
    ->pipe($this->dataStore("patients"));

}

patients Table contain: patientID, patientName, whySmoke reasons Table contain: reasonID, reasonName

value of whySmoke from multi select Ex: "1,2,3,4" or maybe "4" or "1,3" so I want to display 4 columns Reason1, Reason2, Reason3, Reason4 under whySmoke and the value that returned if i get two reasonse fill two reasons in the table an the other leave them blank like below table

patientID     patientName                               whySmoke
                                                 Reason1    Reason2    Reason3  Reason4
					
1                   aaaa                          r1             r3
2                   bbbb                          r3             r2         r4

Sebastian Morales commented on Nov 17, 2022

Pls try this code and let us know if it's what you want:

    $this->src('jbcpDB')
    ->query(
        ...
    )
    ->pipe(new \koolreport\processes\Map(array(
        "{value}" => function($row) {
            $whySmoke = $row["whySmoke"] ?? "";
            $whySmokeReasons = explode(",", $whySmoke);
            $row["Reason1"] = $whySmokeReasons[0] ?? null;
            $row["Reason2"] = $whySmokeReasons[1] ?? null;
            $row["Reason3"] = $whySmokeReasons[2] ?? null;
            $row["Reason4"] = $whySmokeReasons[3] ?? null;            
            unset($row["whySmoke"];
            return $row;
        }
    )))
    ->pipe($this->dataStore("patients"));

KoolReport's Forum

How to display multi select in multiple columns? #2875

Build Your Excellent Data Report