Power Platform Community Forum Thread Details

i have an entity called CustomerPurchasingHistory- with purchaseID,CustomerID(number), PurchaseItemInfo, PurchaseDate(Date).....etc.

Every field except PurchaseInfo is a single value.

PurchaseInfo stores a table of item purchased, and corresponding item quantity, and price. An example would be this:

PurchaseItemInfo

itemName, itemQuantity, itemTotalprice, discountApplied

bread 5 $20 yes

sugar 6 $20 yes

apple 5 $10 yes

towel 2 $15 no

........

i tried to concatenate all rows and columns in PurchaseItemInfoand make them stored as text

EG:

"bread,5,20,yes|sugar,6,20,yes|apple,5,10,yes|towel,2,15,yes|....."

this is saved to cds using patch func

but i cannot load it back to a collection format because split function only return table, and i cant retrieve the third item of the each row, not even talking about more fields in PurchaseItemInfo.

I just wonder what could i do to workaround this?

1. any functions/technique that can resolve this problem?

2. should i save PurchaseItemInfo as a seperate entity on cds and join it with CustomerPurchasingHistoryusing purchaseID and customerID? but would that be very slow if i am talking about 100 million records in PurchaseItemInfo because each customer can have many items purchased?

Categories:

Building Power Apps

Hi @Anonymous :

Do you want to restore the string "bread,5,20,yes|sugar,6,20,yes|apple,5,10,yes|towel,2,15,yes|" to its original appearance?

I've made a test for your reference:

ForAll(
 Split(
 "bread,5,20,yes|sugar,6,20,yes|apple,5,10,yes|towel,2,15,yes|",
 "|"
 ),
 {
 itemName: Last(
 FirstN(
 Split(
 Result,
 ","
 ),
 1
 )
 ).Result,
 itemQuantity: Last(
 FirstN(
 Split(
 Result,
 ","
 ),
 2
 )
 ).Result,
 itemTotalprice: Last(
 FirstN(
 Split(
 Result,
 ","
 ),
 3
 )
 ).Result,
 discountApplied: Last(
 FirstN(
 Split(
 Result,
 ","
 ),
 4
 )
 ).Result
 }
)

Let me Introduce this formula:

1\Using Split(xx,"|") separate each record by "|"

2\Using ForAll to traverse each record after separation

3\Using {itemName:XX,itemQuantity:xx,itemTotalprice:xx,discountApplied:xx} to construct the table.

4\Using Last(FirstN(Split(Result,","),N)).Result to get the value of each field

Best Regards,

Bof