Copy children from multiple parents and remove dup...

OptimiX_XcrY · 02-26-2025 01:53 PM

Hello dear community,

I'm looking for advice on the best ways to approach the following:

I have a Tasks table, containing children tables Orders and this contains OrdersDetails (grandchildren)

I want to build another children table, EverythingInOnePlace table, that will contain all of the OrderDetails grandchildren rows from the same Task.

I tried the action of a set of rows, but I can't figure out the proper select formula so I will copy grandchildren without duplicates (same product in OrderDetails can be found in different Orders).

I thought I'll ise the TRUE in select(), but I found that in action on a set of rows I must use the RowKey column, which is not the same with my Product column.

So far I got to this, but with repeating products copied

select(DetaliiPVSPD[idDetaliu];and([idPVSPD].[idlot].[x4]=[_THISROW].[idtask];or([Operatiune]="post Service";[Operatiune]="Livrare"););

FALSE) (I actually have grand grand children,but i tried to simplify my case description)

I am thinking that this approach might not be the best way for my target result.

Thank you for your ideas

TeeSee1

If I understand your requirements correctly. then here is what you can try to do.

Assumptions.

You want to copy Order Details rows without any PRODUCT duplicates and you do not care which rows are copied (I was thinking that you may have rows of the same Product with different order quantities but my solution does not take those into consideration)
EverythingInOnePlace has ID, ref.Task, Product, and other columns you want to copy from Order Details
Orders has a ref.Task
OrderDetails has a ref.Order, PRODUCT, etc

Solution Steps

Create an action on Order Details that creates a new row in EverythingInOnePlace
- Copy [ref.Order].[ref.Task] to [ref.Task]
- Copy anything else you want
- Behavior condition should be
```
NOT(
 IN(
  [PRODUCT],
  SELECT(
   EverythingInOnePlace[PRODUCT],
   [ref.Task]=[_THISROW].[ref.Order].[ref.Task]
  )
 )
)
```
Create a 'COUNT' column in TASK table (initial value = 1)
Create a series of actions that constitute a loop
- Action 1 - Loop Start: Execute the following actions
- Action 2 - Call the action created in Step 1
  - References Rows
```
LIST(
 INDEX(
  FILTER(
   "Order Details",
   [ref.Order].[ref.Task]=[_THISROW]
  ),
  [Count]
 )
)
```
- Action 3 - Increase [Count] by 1
- Action 4 - Call the Loop Start
  - Referenced Rows = LIST([_THISROW])
  - Behavior condition should be
```
ISNOTBLANK(
 INDEX(
  FILTER(
   "Order Details",
   [ref.Order].[ref.task]=[_THISROW]
  ),
  [Count]
 )
)
```

OptimiX_XcrY

I tested a "simpler" approach, in with the duplicates are copied one over the other, the final result containing only unique grandchildren rows.

I actually set the destination table key column to be a concatenation of product and parent of product. No nested select formulas,etc.

What is your advice about this approach, what bad things can come out of it?

Thank you

OptimiX_XcrY

First "bad thing" from today's testing...

When I use this approach from a local action, it works as intended...no duplicate rows,as rows with same key value are overwritten.

When I use this approach from a bot (automation), multiple rows with same key value are created, tested both from mobile and from app simulator. Couldn't figure out why.

Suvrutt_Gurjar

If understanding of your requirement is clear, please try following

1) Please create a slice called say "Unique_Task_Unique_Product" on the grandchild table "Order Details" with a slice filter expression something like

[Order details table key] =MINROW( "Order Details" , "_ROWNUMBER", AND( AND([Product Id]=[_THISROW].[Product Id], [Order Id].[Task ID]=[_THISROW].[Order Id].[Task ID]))

Where [Order ID] is the reference column in the "Order details" table referencing "Orders" table and [Task ID] is the reference column in the "Orders Table" referencing the Tasks table. [Product ID] is the reference column in the "Order details" table referencing the "Products" table.

This slice will list just one record per product per task.

2) Please create a VC called say [Unique_Task_Unique_Product] in the "Orders" table. The VC expression

REF_ROWS("Unique_Task_Unique_Product", "Order Id")

where [Order ID] is the key of the "Orders" table. The type of this VC - List, Element type- ref and Reference table" "Order Details"

This VC will list order details records for a specific order with one record per product.

3) Create a VC called say [Related_Unique_Order_Details] in the table "Tasks" with an expression something like

SPLIT(TEXT([Related Orders][Unique_Task_Unique_Product] ), " , ")

Column type - List, Element type : Ref, Reference Table Name" "Order Details"

This rev reference VC in the Tasks table will have a list of all the "order Details" records for that task, with one record per product. You need not even create a different table unless it is a must.

As @TeeSee1 mentioned, the above solution also assumes the following

@TeeSee1 wrote:

You want to copy Order Details rows without any PRODUCT duplicates and you do not care which rows are copied (I was thinking that you may have rows of the same Product with different order quantities but my solution does not take those into consideration)

OptimiX_XcrY

Thank you,

Every time I read a post from you, from any community topic, I have new ideas.

I like the slice approach, I will adapt it because I have many users and many tasks (large tables).

Right now I am thinking actually to first try this:

Make a slice based on useremail() and other parameters to reduce my 10k rows table to a 100 rows of order details, and then to apply my action's nested select rows only to the slice , avoiding this way the VC.s on very large tables

Copy children from multiple parents and remove duplicates