How to combine same item and summarise quantity in PDF

[Jeffbo60]

Hello everyone

I try to combine same "EQUIPEMENT" and Summarise "NOMBRE" to have a total of equipment in my PDF Template (to create report). But i'm beginner and I turn arround since a long time...

My EQUIPEMENT is a Ref for another table

The NOMBRE (quantity) is a part of table LISTE CONTENT

Here my template PDF

thank you in advance

[WillowMobileSys]

I am not clear on what you having issues with.  Can you elaborate please?

[Jeffbo60]

I need to have one view in my PDF with same EQUIPMENT (same item) just in one line with Total Quantity for each different equipment.

[Steve]

The numeric component of each of these values:

should be computed by corresponding virtual columns in the LISTE CONTENTS table. You should then use the values of virtual columns both in the above and here:

[Jeffbo60]

Thank you Steeve

 i try this with virtual column.

But my issue is for to combine the same EQUIPMENT item in one line with total for each EQUIPMENT 

for example I have

zone 1

item 1 - Qty 4

item2 - Qty3

item3 - Qty 5

zone 2

item2 - Qty2

item1 - Qty6

item4 - Qty 5

and I need to have view like this :

Total Item 1 - Qty 10

Total Item 2 -Qty 5

Total Item 3 - Qty 5

Total Item 4 - Qty 5

[Steve]

You might try adding a new page with a template similar to the existing Zone page (or replace the existing Zone page):

[Gustavo_Eduardo]

You could try creating a virtual column that calculates the total quantity, meaning summing all the equipment based on the type.

For the quantity in the virtual column, you can use an expression similar to the following:

SUM(
  SELECT(
         Table[Qty],
         [equipment] = [_THISROW].[equipment]
       )
)

That’s one part.

Next, you could create a slice that stores each piece of equipment only once. This prevents having to see the same equipment multiple times.

Then, when selecting the list you want to display, you should point to the slice, and for the quantity, you should reference the virtual column. I hope that makes sense. Here’s an example of a formula for the slice that achieves this:

MINROW(
         "table",
         "_RowNumber",
         [equipment] = [_THISROW].[equipment]
       ) = [id]

This formula returns the IDs that meet the condition of having the same equipment but with the lowest row number (it could be the highest using MAXROW(), but it doesn’t matter—it’s just one row).

This way, each piece of equipment will be listed only once.

So, when configuring your PDF, you should use the slice in your SELECT or FILTER expression, ensuring that only one row per equipment appears instead of the entire list. Also, when referencing the quantity, make sure to call the virtual column.

I believe this should work—give it a try!

[Jeffbo60]

Thank you Gustavo,

I have make successfull Slice and I have only one line per Equipment with total qty.

Then I don't know how to use expression to using Slice on my PDF ...

My slice name is "TOTAL EQUIPEMENT"

[Gustavo_Eduardo]

Don't worry, I'll try to explain it to you in a bit!

[Gustavo_Eduardo]

Alright, @Jeffbo60, now that you've managed to create the slice, you need to add a ref-type field in the table where you’ll generate the PDF. If the table where you plan to create it is the same as the slice's table, this step is unnecessary.

1. Create a Y/N type column in the table. The default state is "false," but if you press the button, it will switch to "true." You could set it as inline, for example.

2. Create a BOT and set the event to whatever you need (add or update) depending on how you want to trigger it. I assume it will be that update we created. Then, as a condition for the bot, set the event to [status] = true. Each time the row is updated and the status is true, the bot will be triggered.

3. Create a "Run a Task" process and select "create a new file." In "Template," choose "create" and build a template. Templates usually come with a small sample table, but if it doesn’t, just create one. In the first column, below the header, insert this formula:

<<Start: FILTER("TOTAL EQUIPEMENT", true)>><<[label]>>

In the second column, add:

<<[virtualColumn_qty]>><<end>>

The first column will return all the records from the slice (the unique records), while the second column will show the quantities calculated with the virtual column.

Please always test that the formulas are correctly written; otherwise, the PDF won’t be generated.

As the next step, you need to create an action that resets the value of the Y/N column you created. Remember that pressing the button sets it to "true," but it must return to "false" to be available for a second request.

So, set up a process that resets the button column to "false" by using "Set the values of some columns in this row" and setting the status column to false.

With this, I believe it should work.

[Jeffbo60]

Thank you so much

It works perfectly !

I use <<Start: FILTER(“TOTAL EQUIPEMENT”, true)>><<[EQUIPEMENT].[Complete Name]>><<[Total Qty]>>

Another thing:

Here I have my row filter for the slice:

MINROW(
"LISTE CONTENT",
"_RowNumber",
[EQUIPEMENT] = [_THISROW].[EQUIPEMENT]
) = [Row ID]

Is it possible to see only equipment used in this operation (like "retated  LIST CONTENT") ? (I have ref column "ID OPERATION " for each equipment  )

Because for the moment I see also  other Equipment used for another operation. I don't know if I'm clear...

[Gustavo_Eduardo]

Yes, I believe it should be possible. You just need to add a filtering condition. Instead of writing "true", enter the condition you need.

I don't know your database structure, but I assume you have either an operation ID or a "in use" status.

If you have an operation ID, you should modify the SELECT() condition like this:

[operation_id] = [_THISROW].[operation_id]

If you have a status like "in use", then create a condition like:

[status] = "in use"

Does that make sense? You're not modifying the slice itself but rather adjusting the formula to fetch the correct values in your PDF.

Now, if you do want to modify the slice, you'll need to adjust its selection criteria:

MINROW(  
  "LISTE CONTENT",  
  "_RowNumber",  
  AND(  
    [EQUIPEMENT] = [_THISROW].[EQUIPEMENT],  
    [OPERATION] = [_THISROW].[OPERATION]  
  )  
) = [Row ID]  

Try both approaches and see which one works for your case.

[Jeffbo60]

Thank you for your helpfull

I have try both solution but the result is the same : the equipment used for another ID OPE stay visible. Maybe it is issue with my structure table . Please see below the 4 main tables. LISTE CONTENTZone of equipment is usedPosition of equipment is usedLISTE NAME (Event Name who we need to have equipment)

[Gustavo_Eduardo]

Hello @Jeffbo60,

More than the tables, what formula are you currently using to obtain the values?

If possible, please show the slice formula and also the PDF formula.

I think we should focus on filtering from the slice. We should request the data directly by filtering for Equipment and Operation.

Thinking out loud for this case:

Would it be possible to create a slice from another slice?

Here’s the approach I was considering: First, you create a slice to filter operations that match the current operation.

Slice condition:
[ID_OPE] = [_THISROW].[ID_OPE] or something similar.

You name the slice something like "LISTE_CONTENT_SameOPE".

Then, you create another slice to limit it to one row per equipment and assign it to the table. But instead of using:

MINROW(  
  "LISTE CONTENT",  
  "_RowNumber",  
  [EQUIPEMENT] = [_THISROW].[EQUIPEMENT]  
) = [Row ID]  

You use:

MINROW(  
  "LISTE CONTENT_SameOPE",  
  "_RowNumber",  
  [EQUIPEMENT] = [_THISROW].[EQUIPEMENT]  
) = [Row ID]  

This way, it references the slice. Not sure if I’m explaining myself clearly. Hope it works!

[Jeffbo60]

I've try with two slice and the result is the same

It seem like the formula "[ID_OPE] = [_THISROW].[ID_OPE]" doesnt work ...

Here my screen shot from slice and PDF

[Jeffbo60]

 Here the equipment with differents ID OPE

[Gustavo_Eduardo]

@Jeffbo60, I understand, it's giving you a list of equipment, but the list includes several of them, and you only need one. Believe me, my brain is burning too, haha. @Steve? @WillowMobileSys? What do you think? You are the real experts.

The other option that comes to mind, thinking out loud, is to create a 'status' column that sets the current row's value to true. For example, if you request this report from a button, it would be ideal for the value to be set to true once the button is pressed.

After that, you need to create a report filter that looks for the ID_OPE whose value matches the ID_OPE of the row where the newly created status column is true (i.e., this active column).

Then, you can try:

FILTER( "LISTE_CONTENT_sameEquipament", [ID_OPE] = ANY( SELECT( LISTE_CONTENT_sameEquipament[ID_OPE], [status] = true ) ) )

Once the report is executed, set the column back to false.