Data structure help for invoicing, If/then/else queries and other things

[Ja_ke]

I am creating an internal invoicing and inventory system for my app.

Line items get entered into a Line Items table with an Invoice ID column.

We have Retail, Accommodation, Services, and Support offerings. I am unsure if they should go in one table or separate tables.

One table seems easy for relation columns and lookup of name/price and other data.

They each have different needs regarding other columns in their table (goods/services has commissions to be paid to staff, Accommodation has lots of optional room options, etc) which leads me to separate tables.

Or separate tables with a general “price list” table maybe.

I want to avoid search and Dropdown choice (Why can i not turn off the search bar that brings up the keyboard automatically?) functions as typing is difficult in this use case.

For another app we use a series of 4 Choice (Chips) component to write to columns in the users table, then a query in the client table searches for a Line Item that fits all 4 criteria and spits out a Line Item ID for the invoice.

This works so well, but I cannot query 4 different tables, or have it select the table to query based on another computed column as far as i know. I would need 4 different queries and an if/then/else column. This is doable but feels like somebody better at this than me would not do it this way.

My biggest concern is once it goes into use, and gets filled with 5000 line items per year its going to slow down. Invoicing is only one part of the app.Its also possible 5000 line items per year is a complete non-issue and considered a miniscule amount of data that is not cause for any concern. Inexperienced Glider here.

Any help would be appreciated.

[MaximeBaker]

@Ja_ke

We have Retail, Accommodation, Services, and Support offerings. I am unsure if they should go in one table or separate tables.

It all depends on the needs.

@Ja_ke

I want to avoid search and Dropdown choice (Why can i not turn off the search bar that brings up the keyboard automatically?) functions as typing is difficult in this use case.

Are you talking about the default collection search bar?

@Ja_ke

This works so well, but I cannot query 4 different tables, or have it select the table to query based on another computed column as far as i know. I would need 4 different queries and an if/then/else column. This is doable but feels like somebody better at this than me would not do it this way.

I would probably create a table that contains 4 queries on the other tables based on your requirements.

@Ja_ke

My biggest concern is once it goes into use, and gets filled with 5000 line items per year its going to slow down. Invoicing is only one part of the app.Its also possible 5000 line items per year is a complete non-issue and considered a miniscule amount of data that is not cause for any concern. Inexperienced Glider here.

Glide handles a looooot of data with excellent performance. Don’t be afraid

That said, you might like to take a look at this template showing how to implement custom searching system. It may be also helpful for you:

How to search multiple tables (template)

If you need further help, don’t hesitate!

[Robert_Petitto]

@Ja_ke

This works so well, but I cannot query 4 different tables, or have it select the table to query based on another computed column as far as i know. I would need 4 different queries and an if/then/else column. This is doable but feels like somebody better at this than me would not do it this way.

My suggestion would be to keep all this data in one table rather than 4 different tables. Then, include a column that specifies the category/purpose that you can use for filtering and querying.

[Ja_ke]

@MaximeBaker

Are you talking about the default collection search bar?

No. When I use a dropdown collection with more than a certain amount of options it automatically includes a “search bar” at the top of the list that opens the keyboard when used on a mobile phone. There is no setting to turn it off like a default collection search bar.

@MaximeBaker

I would probably create a table that contains 4 queries on the other tables based on your requirements.

Ok great! I can believe that my tiny business is not in the realm of problematic for Glide, but are queries generally something i should try to minimize? I have read lots of “optimizing” posts but Queries actually come with a speed warning built in (when faster as a relation) so maybe they are particularly problematic when used in great amounts.

I will look at the template, thanks!

[MaximeBaker]

@Ja_ke

There is no setting to turn it off like a default collection search bar.

In collection, there is an option to disable search bar. If it doesn’t hide it, it might be a bug.

@Ja_ke

are queries generally something i should try to minimize?

Don’t be afraid of using these queries. Just ensure that they are optimized! You can try the performance analysis in the data editor on the left just above the tables. There is a monitor icon.

@Ja_ke

but Queries actually come with a speed warning built in (when faster as a relation) so maybe they are particularly problematic when used in great amounts.

This message shows up when you use simple condition like relationship: productId = products_table.RowId.

[Ja_ke]

@MaximeBaker

In collection, there is an option to disable search bar. If it doesn’t hide it, it might be a bug.

Even after re:reading i used the wrong term, sorry. I can turn off COLLECTION search bars, but the dropdown CHOICE component search bar I cannot turn off. It auto enables after a certain amount of options are available in the dropdown. Due to this, I have relied on chip selections and filtering of possible choices before using a dropdown when a dropdown would have worked great alone.

[Ja_ke]

@Robert_Petitto

@Ja_ke

This works so well, but I cannot query 4 different tables, or have it select the table to query based on another computed column as far as i know. I would need 4 different queries and an if/then/else column. This is doable but feels like somebody better at this than me would not do it this way.

My suggestion would be to keep all this data in one table rather than 4 different tables. Then, include a column that specifies the category/purpose that you can use for filtering and querying.

Great!

Things like inventory tracking, COGS, commissions etc which only apply to certain types of items are better handled in different tables then? Or all in the same table with sections of columns that only apply to certain rows?

I understand it is a complex question that likely depends on more information than im giving you.

I have begun my entire Glide progress based on your videos, i think I have gone through nearly all of them except for the “Gamifying” ones, thanks for sharing all of that!

[MaximeBaker]

Ohh I remember what you are talking about. Yes, the only eay to hide it is to use custom css.

[Robert_Petitto]

@Ja_ke

I have begun my entire Glide progress based on your videos, i think I have gone through nearly all of them except for the “Gamifying” ones, thanks for sharing all of that!

Happy to help! I really need to remake the gamifying ones— those ones were referencing the old app builder.

[ThinhDinh]

@Ja_ke

Things like inventory tracking, COGS, commissions etc which only apply to certain types of items are better handled in different tables then? Or all in the same table with sections of columns that only apply to certain rows?

It depends on how much varying calculation you need for those. If there are not too many different calculations for each type, I would suggest keeping it in the same table, and vice versa.