Counting across Columns

Work_Goscinski · 06-26-2020 11:14 AM

I have read about counting across multiple columns.

I have created an audit app. My users have to check multiple random parts before they ship. I have hundreds of part numbers. They can check up to 12 parts per audit. The same parts may not end up in part number 1 column. SO I need the ability to see if a part number occurs in any of the 12 columns.

I created a virtual column with this formula

Next I created a slice called # of Parts checked.

Then I created a view

However, as you can see in the data sheet:

there should be 4 instances of 16 13643 000

Clearly, I am missing something. Just not sure what.

GreenFlux

I would suggest restructuring your data. If there were 12 every time and they each referenced a specific part then this structure might make more sense.

Your last screenshot starts with Part Number 1 at Column G. I would make columns A-F be the parent table and remove the Part Number columns.

Then add a child table with just a Ref to the Parent-Inspection, and a Ref to the Part.

Then you can have unlimited parts, and you won’t have a bunch of un-used columns when they inspect less than 12. Also, searching and counting will be much easier.

Work_Goscinski

I have something similar to what you describe. Not sure how your solution solves the issue though.

GreenFlux

Because it puts all the values in a single column, making it much easier to COUNT(FILTER()) since you will only have to do it on one column instead of 12.

It’s also a more efficient data structure since not every inspection uses all 12 columns. This way you don’t end up syncing a bunch of empty columns when they inspect less than 12. Every column is usable data.

What do you mean you have something similar? Did you try adding a child table? What part are you stuck on?

Work_Goscinski

What I mean by “similar” is that I have a table that has all parts and the plants in which they are made.
12345 | Plant 1
12356 | Plant 1
12555 | Plant 2
etc.
So when you select that you are in Plant 1, only Plant 1 parts appear.

I am still VERY NEW to all this. So I will have to read a little more to completely understand your solution. I am not entirely sure what Parent and Child records are.

GreenFlux

No worries! We were all new to AppSheet at one time.

Check out this sample app, and look at the Products[Current Stock] settings for the virtual column.
https://www.appsheet.com/samples/This-app-uses-barcode-scanner-to-record-stock-in-and-stock-out?appG...

Also look in the settings for the Ref columns in the child tables (Sale, Purchase).

You can make a copy of the sample and experiment to learn more about the Parent/Child table setup.

Work_Goscinski

Thanks for a place to start. Much appreciated.

Work_Goscinski

Ok. From what I have read and looking at the sample app, I have used data validation between parts and plant. So only Plant 3 parts appear when you select Plant 3. I am not sure how to DO what you suggest.

What I am not understanding is how to make 1-12 parts appear in one column. I don’t see how to setting anything to REF helps. I guess I am not understanding the difference between REF and data validation. Any articles to read?

GreenFlux

Let’s take a step back and look at the business problem you want to solve. I want to make sure I understand your end-goal.

My users have to check multiple random parts before they ship. I have hundreds of part numbers. They can check up to 12 parts per audit. The same parts may not end up in part number 1 column. SO I need the ability to see if a part number occurs in any of the 12 columns.

Are the inspections done in batches? I’m guessing you want to see if the part number occurs in a certain set, like per day, or inspector, or work station, right?

What kind of final report do you want? Something like a list of all parts that were inspected for the day?

Work_Goscinski

I am tracking what we call a Dock Audit. It is a random review of some parts before they are shipped to a customer. We have over 300 part numbers. Basically, this particular part of the app, I want to count occurrences of a part, so that auditors don’t just keep checking the same parts over and over.

So they pick a part (some parts ship in packs, i.e. there are a left hand and a right hand, or going to the same destination. This is the reason why I want the ability to add up to 12 parts in an audit. Generally, there aren’t that many, but there COULD BE), review questions listed in the app for accuracy, quality, etc.

If there is some sort of error, then a supervisor is notified, “hey, there is a problem.” The supervisor reviews the reason for the error, puts something in to place to ensure the problem doesn’t recur, and then signs off. “Problem fixed.” (I am trying to get that to appear in the app too, but that is NOT this question. I only mention it to explain the whole scope of the app.)

In addition, there is a scheduling function. 3 plants. Different parts are made at different plants. So users are notified via email: you have an audit coming up in Plant x next week, or; you are 3 days late on your audit, get your tushy moving.

I hope that helps give the scope. Is it “vital”, my question? No. As it exists, my app is performing as planned. I just thought a count of part numbers checked already helps the next auditor to select different parts so our audits are more thorough across all the parts we make.

David Goscinski, M.Ed

GreenFlux

Thank you for the extra info. I thought you were earlier in development and didn’t realize you had everything else working other than the count.

I still think a different table structure may be more efficient, but if you like how it works and just need the count then we should just go with that. I’ll help you figure it out.

Do you mind sharing the app with me?

Work_Goscinski

So I understand the “concept” of what you mean when you say “restructure the data.” I am just not sure what that LOOKS like.