Re: ref table depending on previous selection

BlakeHammond · 06-07-2022 09:28 AM

Hey ALL

I am sure this has been answered before but I can't find it.

I am trying to amend my quote app as it's running into errors and crashing. In the process, I am trying to limit how many rows each table processes at once.

What I am hoping to achieve:

When selecting equipment to be added to the quote, first a supplier is selected. Then in the part number field, this should be a dropdown of equipment from this supplier. But I can't figure out how to make it look at the equipment table for that supplier I selected to start with.

Basically.

Select Supplier 1 for the supplier field

click part number field - Display all equipment by supplier 1 from supplier 1 table

Select Supplier 2 for the supplier field

Click Part number field - Display all equipment by supplier 2 from the supplier 2 table.

The reason I can't put all equipment into one table with supplier names at the beginning and then dereferencing is because after speaking to AppSheet support the reason my app crashes when syncing is that there are over 40,000 rows. So I am trying to spilt it into individual tables and see if that helps.

I appreciate any help I can get.

Thanks

Blake

Marc_Dillon

40k rows should not be a problem. Besides that, splitting it into two tables won't really help, you'd still be loading the same amount of data. It also shouldn't be causing errors, slow syncs definitely, but errors unlikely. Are you using too many virtual columns? Expensive VCs across 40k rows would definitely cause long sync times.

I wouldn't recommend continuing on your current path as it is not standard or "normalized" data, and will likely continue to cause issues down the road. It would be far better to figure out what the actual issue is. But if you really need to, your expression would have to be something like this:

SWITCH(
  [supplier] ,
  "1" , table1[column] ,
  "2" , table2[column] ,
  ...
)

https://help.appsheet.com/en/articles/2355972-switch

Or IFS would also work (SWITCH is just a special case of IFS) https://help.appsheet.com/en/articles/2355961-ifs

BlakeHammond

Hey @Marc_Dillon

Thank you for your reply.

The error I'm getting I don't think is directly with Appsheet, it is the webpage that appears to time out because of how long it takes to load. But the webpage also seems to crash while working in the app. This is the only one of the apps that does this. But it is also the biggest app.

Over the whole app, there are probably 100 Vc's which I was using to try and keep the spreadsheet clean, and probably 20-30 select statements. These calculate anything from profit and loss to how many days are allowed on a job.

Would I be better off converting VCs to actual columns in a spreadsheet instead of splitting the cost sheet into 7 different spreadsheets?

Thanks

Blake

Marc_Dillon

Holy crap! Yes 20-30 VCs with SELECTs is a huge amount, especially if even a single one of them is on the 40k table.

Yes, converting the VCs into real columns would 100% be better than splitting it into separate tables, especially as I already mentioned this probably wouldn't even affect anything because it'd still be the same amount of records.

BlakeHammond

Okie Dokie :). Sounds like I'm spending a couple of days rewriting the app. 🙂

Thanks

Blake