Generate list of supplies needed based on form sel...

Dave_Niedbala · 12-20-2021 03:11 PM

I do woodworking and I am trying to use my app sheet as a customer database. I add customer orders myself and select the products they want through checkboxes. EXAMPLE, Let say “Lisa” wants “ONE” “Small Table” that is colored “brown”. Is there a way to generate a parts list based on the selection in the form? What I am hoping to be returned would say something like this.

Parts List for one small table
2x4 qty 3x at 8’
1x8 qty 1x at 4’
1x4 qty 2x at 8’
Stain Color Brown

I’m not sure how to set up the sheets to read between them each other to “Call” or “ask for” based on the selections.

THank you
Dave.

Marc_Dillon

How is the app supposed to know what the parts are for a certain product?

Dave_Niedbala

That is my question. Can I look up and return multiple cells based on a selection?
I can set up a sheet of all the parts listed vertically and then have a 2nd cell next to it that will list all the products that use that part. Not sure if there is a way to Filter a list?
Sheet Example.
| A | B
1 | 2x4 | Small Table, Large Table, Outdoor bench
2 | 1x3 | Large Table, Outdoor bench
3 | 1x8 | Small Table, Toy Chest
4 | 1x5 | Outdoor Planter

There are many options to set up the table but I’m not sure what is the best to suffice a lookup.

Here is an image of my app of where I would like this “Lookup” to show up. Outlined in RED

I keep track of my wood inventory so I would like to bring that into play as well, but not yet.

Marc_Dillon

Yes, if you have a Table like this, you can do:

SELECT(
  Table[A] ,
  IN( [_THISROW].[Product] , [B] )
)

Dave_Niedbala

Thank you. I’ll make a table like this. Now how can I get it to show like my picture above? I’m not sure how to even start that.

Marc_Dillon

The results of the SELECT, when converted to a Text value, will be a comma-separate list. You can use SUBSTITUTE() to replace all commas with new lines, in a LongText column.

SUBSTITUTE( ... , "," , "
")

1minManager

Hi @Dave_Niedbala all this is entirely doable, though its a fair amount of work to try and describe in detail in a forum. What I suggest is you consider getting one of the Appsheet Partners (https://www.appsheet.com/Partners) to give you 1-2 hours of work and consultancy. That way they can advise on how to lay the tables out correctly to make you life easier later on. This is what I did when I started and even a couple of weeks ago I paid someone for about 30mins work just to show me how to get the Appsheet API working. This was after 10+ hours of banging my head on a wall with it not working.

As a cheeky suggestion, I’m an Appsheet Dev with 6ish years of experience and I’ve done a very similar job for a plumbing company that does new build houses. They have a table of 179 types of houses and each house can have up to 160ish different materials. So if you want a 10-20 min chat FOC I’m happy to do that. DM if interested

Simon@1minManager.com

Generate list of supplies needed based on form selection