Answered

Splitting row level items from a gallery

(0) Share

Report

Posted on by Su27

Hello, I have a dataverse table to make dataverse for teams app with two columns that have comma separated values. An example is as follows:

ID (Record)	Industry	CityCode
1	Agri,Service	Agri~938YC.1, Service~8982U.1
2	Service,Agri	Service~9844U.1, Service~9844U.2,

Given such a data, I want to display first the industry in a gallery and lookUp the associated CityCOde from the CityCode table. The CityCode table can be changed/updated by admins to a newer version. When the ID status changes to say Completed, I want to split CItyCode after the "~", get the latest version of the city code (the dot 1 after the ~ represents the version) and post the CityCodeName (see below) so that it does not change when CityCode table changes by admins.

The cityCode table looks like this:

ID	Industry	CityCode	CityCodeName
123	Service	8982U.1	Morganston
321	Agri	3984U.1,3984U.2	Filipa

I have tried to Split, Concat and a combination of the two. The errors I get is that after Splitting the values are tables and wont be added to a gallery.

In the current gallery, I was able to add the following in the items property of the gallery Split(Industry,"<").Value which gave me the values added to the gallery. But then I added another label and tried to split CityCode with no success.

Thanks in advance!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

MudassarSZ365 591 on at

Like (0)

Report
Copy link

Link copied!
Hi @Su27 ,
Splitting Industry Values:
In the Items property of your gallery, you've already used Split(Industry,"<").Value to display the Industry values. This is correct.
Splitting City Codes:
In the Items property of another label or control within the gallery (let's call it Label1), you can split the CityCode values using the Split function. Assuming CityCode is in a text format like "Service~9844U.1", you can use Split(Label1.Text, "~") to split it into an array.
Get Latest City Code:
To get the latest version of the CityCode, you can use the Last function to select the last item in the split array. For example, Last(Split(Label1.Text, "~")) will give you the latest version like "9844U.2".
Lookup CityCodeName:
To lookup the associated CityCodeName from your CityCode table, you can use the Lookup function. Assuming you have a CityCodeName column in your CityCode table, you can use:
Lookup(CityCodeTable, CityCode = Last(Split(Label1.Text, "~"))).CityCodeName
If this solution is helpful, please accept it. 😊 Regards, Muhammad Mudassar Mazhar

Was this reply helpful? Yes No
Su27 28 on at

Like (0)

Report
Copy link

Link copied!

Thanks for your prompt reply.
When I tried this approach, the label where I am splitting CityCode gives me an error 'Expected Text Value'.

Was this reply helpful? Yes No
Verified answer

MudassarSZ365 591 on at

Like (0)

Report
Copy link

Link copied!
@Su27 ,
Here's how you can correct the error and achieve the functionality you're looking for:
Split Industry Values:
You've used Split(Industry, ",") to separate the industries. This is correct and should give you a table of industries.
Split City Codes:
To split CityCodes within the gallery, you need to ensure that you are referencing a text field. If CityCode is the name of the field that contains the comma-separated city codes, the formula should reference the current item in the gallery. For example:
Split(ThisItem.CityCode, ",")
Get Latest City Code Version:
To get the latest version after the "~", you need to further split each item in the CityCode column and then take the last item. This requires a nested split:
ForAll(Split(ThisItem.CityCode, ","), Last(Split(Result, "~")).Result
Lookup CityCodeName:
To find the CityCodeName, you would use the Lookup function along with the ForAll function, since now you have a table of city codes:
ForAll(Split(ThisItem.CityCode, ","), Lookup(CityCodeTable, CityCode = Last(Split(Result, "~")).Result).CityCodeName )
If you think my solution is helpful for you, then please accept it. 😊 Regards, Muhammad Mudassar Mazhar.

Was this reply helpful? Yes No
Hassan_SZ_365 542 on at

Like (0)

Report
Copy link

Link copied!
Hi @Su27 ,

To address the issue of splitting and displaying data in a Power Apps gallery based on your Dataverse setup, here’s a concise solution:
Splitting Industry Column: You've successfully used Split(Industry, ",") for the Industry column. To display these in a gallery, set the gallery's Items property to Split(ThisItem.Industry, ",").
Handling CityCode: To split the CityCode and get the latest version, you can use a combination of Split and LookUp. First, split the CityCode based on the comma to separate different codes, then split each code at the "~" to separate the city code and its version. Use the LookUp function to find the corresponding CityCodeName from the CityCode table.
Using a Nested Gallery: Since the split CityCodes are tables, consider using a nested gallery. The parent gallery displays the Industry, and the nested gallery inside each parent gallery item displays the split CityCodes.
Preventing Change on Status Update: To ensure that CityCodeName does not change when the status is updated, consider storing the final value in a separate column or a collection when the status changes to 'Completed'. This way, it remains unaffected by future changes in the CityCode table.
Formula Example:
Parent Gallery Items: Split(ThisItem.Industry, ",").
Nested Gallery Items: Split(ThisItem.CityCode, ",").
Label in Nested Gallery: LookUp(CityCodeTable, CityCode = Left(ThisItem.Result, Find("~", ThisItem.Result) - 1), CityCodeName). This assumes CityCodeTable is the name of your CityCode data source.
This approach should address the issues you're facing with splitting and displaying the data in your gallery.

Best Regards,
Hassan Raza

Was this reply helpful? Yes No