TeamDesk Knowledge Base & Support

basenine 11/24/2020 3:22:53 PM

Could you deprecate the STANDARD MODELS table and simply put a checkbox in the SCALES table named [STANDARD MODEL]?

Dean Robieson 11/26/2020 3:01:59 PM

Thanks for your suggestion Basenine.

I think your suggestion would work, however the benefits of having the second table (such as ensuring a user can’t enter an invalid model number when adding stock) is probably more important than the report I am hoping to generate.

Many years ago, I had a play with SQL, and this would have been easy to achieve, but I don’t know within Teamdesk.

I appreciate your input.

Thanks
Dean

basenine 11/26/2020 3:30:28 PM

Hi Dean,
I think I misinterpreted your tables usage.
Is the second table like an invoice items table where there can be multiple records created
using the same model number of scale, but the differences would be customer and
date of purchase?

Best regards,
Brett

basenine 11/26/2020 4:02:33 PM

I created a stock/inventory management system that required workflows which update quantities available based on: assigned to sale (but not left the building), sold (and customer now has), on order and received.
This would let us know exactly what was available to use, what was assigned to a sale and how many were in the building or on order.
It is the ultimate real-time stocktake.
Is this the sort of thing you’re after?

Dean Robieson 11/26/2020 4:26:10 PM

Hi Brett.

In my case, there is a finite number of different models of scale we sell. My STANDARD MODELS table’s unique field is ‘model number’, so each model can only be entered in this table once. The table contains ‘global’ information about the particular model such as a checkbox that indicates if that particular model is one we offer for sale on our website, or not.

In my SCALES table, there is a record added for every set of scales we bring into the country, and the record contains info specific to the particular scale such as our purchase date, current physical location, serial number etc.

When we add a new set of scales to the SCALES table, the model number is a field that is feed from the STANDARD MODELS table so that there can be no error made by the user. That way, I can do reliable searches on model number to see how many of a particular model I have in stock.

Now, this allows me to run a report listing oonly those models that are listed on my website (info from the Standard Models table) , and a count of how many sets is scales I have in stock (info from the scales table).

Only problem is the report will only list the models PROVIDED I have at least 1 set of that model of scales currently in stock. Unfortunately, I am most interested in finding out if I have models that are listed with a stock level of zero.

Thanks for your help.

basenine 11/26/2020 6:12:18 PM

Hi Dean,

I just created a simple structure based on what you've described and the report seems to work for me....which tells me I'm not doing everything the way you've done 😉

If you like, you can invite me to your application and I can check out the structure properly in a development branch.
my email is brett@basenine.com.au

Dean Robieson 11/27/2020 1:26:14 PM

Thanks for your continued help Brett.

I have invited you as an Admin.

The report (view) in question is found under the Stock tab and is titled “Stock to hold items - quantity on hand’.

Thanks again

Dean

basenine 11/27/2020 2:21:44 PM

Good morning Dean,
Thanks for inviting me to your application.
I've had a look at the view and can see you're using the #Records summary.

For this reason, if there are 0 records of a model of scale, it will not show.

There are a couple of solutions:
Solution 1 is moderately complex, but requires a shift in navigation logic.
Solution 2 is complex but will give you a much broader detail of sales

I'll email you direct with the two solutions.

Best regards,
Brett