Best way to count items in DB?

Hi guys,

I’m facing an issue and unsure of the best practice solution. My users can create doors, windows, conservatories, etc. and generate an estimate. I need to show the items and quantities in a format like this:

3 x Casement Window
1 x French Window
3 x Sash Window
1 x Standard Door

If I only had a few products, I could have fields in the Job Datatype, such as “Casement Window Count” and add +1 each time a new one is created. But with 100+ products, I need a simpler solution.

One idea is to have a ‘Products Count’ [text] field in the Job Datatype and add the product name to the list when it’s created, like “French Window +1.” But I found that this updates the list instead of adding to it.

Any advice is greatly appreciated.

Hi darren.james7518.
Hope to find you well.
I didn’t quite understand your purpose with your product, but it seems to be something related to real estate, interior design, something along those lines.

Come on, in my humble opinion, you would need to create a separate table with the product and a product code. With this you will have control over the registered product. For example: tb furniture:
Field: Standard port
mobile code:
Code: 01
This in a separate table from the main one, in the main one, you would need to have a field like the new table to fetch the already pre-ready records.
With this, you will certainly look for the number of doors and windows by user and by all for your own statistics, for example sales by products and so on.
That’s just my view. I don’t know if this is what you are looking for. If you give me more information I can recreate something here, for testing.
Anyway, we are available.

Sounds like you’re just creating line items in an invoice/order/cart?

If you’ve never thought about this sort of thing here’s a video where I talk about that topic broadly. Perhaps this will help you get your head around it:

1 Like

Thank you, I will go and check that video out now.

Thanks for your ideas.I do indeed have a new Thing for each of the products but I need to display the data which shows how many of each product the user has. I can do this in a number of ways but I’m unsure of the ‘best’ way. You have made me think that maybe I should also consider how to show “sales by products and so on”, that’s a good idea.

1 Like

After some challenges, I’ve successfully developed a counter to track the number of products in each job. I aimed to avoid performing a ‘Search for’ operation on the page to prevent long wait times when there are over 50 products. I also had to ensure that the database was updated after each product creation and product edit, where the user could alter the item count.

Each product has its own quantity, and the parent job (which holds the products) has a total “Product count” data type for each product. When a user creates a product, for example, a ‘casement window’, there is an input where they can add their own item count. When they save that product, I make changes to the job’s Product count, and the field I change is the Products field. So, if the product is a ‘casement window’, the change looks like this: the thing to change is the parent group job’s Product count, and the field to change in this case is ‘Casement window’, which I update by doing a ‘Search for’ operation to sum up each item’s quantity.

Moreover, if a product is deleted, I simply execute the following action:

delete

The result is that I can call the information (total count) for each product without doing a Search for… here’s is how I call the count:

easy count

1 Like