Is there an expression, to search records and...

[Steven_Jeffery]

Is there an expression, to search records and find ones that are alike?

Here is what I need the expression to do:

I have various pieces of equipment, with various materials per piece of equipment.

Example:

Unit #1 has (2) 20x20x2 filters. Unit #2 has (4) 20x25x2 filters. Unit #3 has (4) 20x20x2 filters. -same as unit #1 w/different qnty. Unit #4 has (2) 20x25x2 filters. -same as unit #2 w/different qnty.

I would like to run an expression in the template, or app if easier, to search records for the filter sizes that are the same, and then be able to calculate the TOTAL filter qnty, for each size.

So in essence, having the report return something like this: Filter Sizes: 20x20x2 = (6) 20x25x2 = (6)

What would be the best way to do this? I was trying to figure out how to have the template calculate this, but if I should build a VC that calculates these, that would be fine also.

Thanks, Steven

[Aleksi_Alkio]

Do you just need to see the amount or do you need to use that value somehow in the app?

[Steven_Jeffery]

I just need to see the amount of each different filter size total.

in the report would be best.

It is so I can give that report to my coordinator so she can order the materials for the service visit.

I would like her not to have to go line by line and add the totals up herself.

I would like each individual filter size, to have a total for the whole site.

[Steven_Jeffery]

This information is residing in the Site Equipment table.

[Aleksi_Alkio]

Last question… you want a report where you have all unique filters and belts for that one site order?

[Steven_Jeffery]

Yes, but I would like it to search all records and list the filter size once, with total filter quantity for that filter size.

So if there are (2) units with let’s say a filter size of 20x20x2 and each unit has (2) filters in them.

I would like the report to show:

20x20x2 = (4)

Instead of: 20x20x2= (2) 20x20x2= (2)

[Bellave_Jayaram]

@Steven_Jeffery probably best to set up another sheet that pulls desired rows from your orders sheet and uses sheet formulas to populate the columns.

[Steven_Jeffery]

So use google sheets to calculate on separate worksheet, and then if desired use that worksheet as a table that populates columns in another table within the app?

[Bellave_Jayaram]

Yes, you got it. You can use Filter and/or Query formulas to select rows that match your grouping criteria. You can add additional columns, if needed, to that sheet.

[Aleksi_Alkio]

I was thinking something in the middle😃 If you have a table already why don’t you add a virtual column to count correct values. If you create a slice for it and filter zero counts away, you can possibly print the list directly from the app.

[Steven_Jeffery]

Site Equipment = The table where the filter and belt sizes are input for each piece of equipment.

Can you walk me through how to setup the virtual column?

[Steven_Jeffery]

When you say “count correct values”, what do you mean and how do I do that? I would like to try each of these approaches and see what works best.

[Aleksi_Alkio]

I would say something like this… You need to show what site you are going to sum in the virtual column. You can do that with an extra column or you maybe have a column for that purpose.

The formula could be something like… SUM(SELECT(SiteEquipment[QUANTITY],AND([FILTER]=[_THISROW].[FILTER],[ACTIVE SITE]=TRUE)))

[Steven_Jeffery]

The answer I think is somewhere in the realm of ‘count’ expression or ‘list’ expression I think.

[Aleksi_Alkio]

I was thinking this… create a view (or you may have it already) and group them with the filter type. Then use COUNT as the Group Aggregate. Then just use the search feature (magnifying glass) and it will show you the correct count for that filter.

[Steven_Jeffery]

oh duh, yah.

Then how do I transfer that over to a report?

[Steven_Jeffery]

is there another way to capture this data? The view is not really giving me

the results the way I would like.

[Aleksi_Alkio]

Alright… do you have a table for filter types or where do you read them?

[Aleksi_Alkio]

Are you collecting the report from one order or like all orders from one day or something?

[Steven_Jeffery]

So I have a site equipment table, this holds Model#, Serial#, and the filter and belt sizes for each individual piece of equipment.

I have a table that holds all the various filter sizes.

Then in the Form when you are adding the piece of equipment for the site, for the filter and belt sizes, it references the table that holds the various filter and belt sizes.

[Steven_Jeffery]

I want to generate a report for our office lady, once I have input all the companies site information.

The report would tell her how many filters and belts to order for the upcoming service.

ex: 20x20x2 = (6) 20x25x2 = (6)

AX26 Belt = (3) AX37 Belt = (6)

ETC…