Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
  
Outer join of tables
Hi all

I have a small data base that holds records for the stock items (industrial scales) sold by my business.

One table (Named ‘Standard Models’) has a column for each model we sell and includes (among other things) the Model Number and a checkbox (field name ‘Listed’) that identifies whether that particular model is one that we offer for sale on our website or not. The Model Number field is unique, so this table has only one row for each of the models we provide.

The second table (‘Scales’) has a record for every set of scales our business handles. Fields include the serial number of the scales, the date we received the scales from our supplier, date of sale, and customer name etc (for warrantee purposes) etc.

The two tables are ‘joined’ my the model number. Doing this makes sure that there can be no typing issues when entering a new set is scales in the Scales table because the user can not enter a model number, but instead selects the model number from the form field which is populated from the Standard Models table.

MY PROBLEM:

I want to produce a report that lists every model from the Standard Models table where the Listed field is checked, and that tells me how many of that model I currently have in stock (for re-ordering purposes).

This is pretty easy to do, EXCEPT, that the report lists all model munchers that are ‘Listed’ PROVIDED we have at least 1 of that particular model number in stock.

Of course, what I need is for EVERY standard model that is marked as being Listed to show on the report EVEN IF we currently have stock levels of zero for that particular model number.

Any help with how I would modify my report criteria would be appreciated.

Thanks.

Dean



ID
1397
Category
Integration/API
Author

Dean Robieson
Date Created
11/24/2020 2:51:54 PM
Date Updated
11/27/2020 2:21:44 PM
Comments
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
Feedback
Back to Search Results