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

(Steven Jeffery) #1

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) #2

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

(Steven Jeffery) #3

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) #4

This information is residing in the Site Equipment table.

(Aleksi Alkio) #5

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

(Steven Jeffery) #6

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) #7

@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) #8

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) #9

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) #10

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) #11

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) #12

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) #13

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) #14

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

(Aleksi Alkio) #15

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) #16

oh duh, yah.

Then how do I transfer that over to a report?

(Steven Jeffery) #17

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

the results the way I would like.

(Aleksi Alkio) #18

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

(Aleksi Alkio) #19

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

(Steven Jeffery) #20

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.