Topic: Links tables together

Hi

I have been working on a small application, and run into a problem that I struggling to fix 'properly'

Structure
Each customer has
   Many appliances
      Each appliance has many tests

So How do i ensure the each test is linked to one appliance and one customer?

If I insert a counter field, all the existing records wont have that counter value?

Can I I fill in each form in the string with the inherited values... ie use a combo box on an appliance form, and fill it with the customer name that was clicked?

Thanks in advance...

I have attached my database... please excuse all the beginner errors... any suggestions would be appreciated.

Post's attachments

Attachment icon TestnTAG.zip 356.5 kb, 283 downloads since 2018-05-14 

2 (edited by derek 2018-05-14 22:31:02)

Re: Links tables together

Hello TC,
You are right - you could manually maintain the links with comboboxes but basically, MVD takes care of that side of things for you if you've set up your data structures and your forms in the correct way.
Taking your example, if you have a form with a specific customer on it and a tablegrid listing all the appliances for that customer, when you click to add a new appliance, it automatically 'relates' the new appliance record with that customer.  Similarly, you have a form with the details of a specific appliance and a tablegrid of test resutls, so when you add a new test result, it automatically 'relates' the new test result with that appliance.
A couple of things to note - all this presupposes your data structure is correct and that your tablegrids are either set up to show 'child records' or filled using a 'search' action (otherwise you will see ALL records irrespective of the customer or irrespective of the appliance).
Your 'businessinfo' form on your original post didn't have a tablegrid to display appliances on it so (for the reasons outlined above), it wasn't able to 'relate' the relevant customer to appliances correctly.  As a result of that, you had a lot of records that had a 'null' value in appliance.id_customer.  I went through them and added them all in but you might want to have a check (if this is real data that you are intending to use).  Likewise, I created a few new records to test my changes (which I've left in for you to see) that you'll want to delete.
Haven't had time to test it thoroughly as I'm on my way out, but if there's anything I've done that's not clear, please get back..
Regards,
Derek.

Post's attachments

Attachment icon testntag.zip 358.22 kb, 321 downloads since 2018-05-14 

Re: Links tables together

Hi Derek

Thank you very much.  Looks pretty much what I thought it should do

Two questions
a. How did you look at the data in the tables?
b. Did you do anything particular to key the records?

Thank you
Tony

4 (edited by derek 2018-05-15 09:56:49)

Re: Links tables together

Hi Tony,
I use a free utility called SQLiteStudio. 
Once downloaded and installed, if you click the sqlite.db that gets created when you first run your application, it opens in sqlitestudio showing all the structures, indexes etc. 
Among many features, there is, for each table, a data view and that is where you can see the 'raw' data and how the relationships between tables are being maintained (or not!).  I find it very useful for tracking down problems (the clue is almost always in the data!!), but it also gives you a much better understanding of how MVD actually hangs together and what it's doing 'behind the scenes'.
Using SQLiteStudio, it was very easy to spot that the relationship between 'test' and 'customer' wasn't being maintained.  I referred to the appliance table to find out the correct customer id for each appliance and then went back to the 'tests' table and manually copy and pasted in the missing keys (it's actually a lot more straightforward than it sounds!)..
If you don't want to use SQLiteStudio, you can also do a 'datacheck' yourself within MVD - please have a look at the attachment where I've added a 'datacheck' button to your main screen.   It's a quick and easy way to 'sight-check' your data and if you make your 'datacheck' tablegrids editable, you can fix the problem there and then. 
When you're happy with the quality of the data (typically just before you roll your application out), you can then delete the datacheck form and button (although I tend to just make the button invisible so that the facility is always there in case of future problems).
Hope all of that makes sense,
Regards,
Derek.

Post's attachments

Attachment icon testnTAG2.zip 359.22 kb, 317 downloads since 2018-05-15 

Re: Links tables together

Hi Derek

Thanks for the work. Loved the dataview button, and the sqllite suggestion.

I thought yesterday that when I went from the Business info form to appliance search using the appliance button, it auto filled the combo box customer details on the form... today it doesnt..

Could you have a look and see why it doesnt?  Using your vrsion 2

Also how do you do the tabbed form?

Thank you
tc

6 (edited by derek 2018-05-15 23:18:07)

Re: Links tables together

Hi Tony,
Just checked on your original attachment and on my subsequent attachments and I can't see how the combobox on the appliance search form would have ever been filled with the customer details.
Not sure that's the best way to do it anyway though because, as I understand it,  it presupposes that the user knows what customer the appliance belongs to and I wonder if that is actually the case.  If I have a bit of time, I can mock up some other ways of doing it if that would be of any use to you - or at least give you some more ideas.
The tabbed form is done by dropping the 'page control' object onto a form;  you can then add as many 'tabs' (tabbed sheets) as you want.  Depending on your application, it can be a useful way to present the data.
Regards,
Derek.

Re: Links tables together

Hi Derek

Yes that is correct, each appliance belongs to one customer. These are test carried out on behalf of particular customer - so each appliance is associated with a single customer.

I would be interested in some tips to associate the customer to the appliance...

Thank you
tc

Re: Links tables together

Hi Tony,
With projects like yours that I've done before (3 tier hierarchy - customers - appliances - results), I've often been asked for a 'total view' all on one screen.  It might be of interest to you so I've added this sort of view to your project.  It's not as 'seamless' as it should be because I didn't want to mess up your existing project too much, but I hope you get the idea.
I've also created a calculated field on each table to do 'free format' searches (users often don't know which field they might have put data into!). so rather than have multiple discrete search fields, they can just type anything in to one search and it scans across all fields (hope that makes sense).  For example, in the 'appliance' search, you can enter maker or model or serial no' or location etc etc.
All of the above can be done without using a script (although I've added a few lines, just to automate things and make it more user-friendly).
Hope it helps,
Derek.

Post's attachments

Attachment icon testntag pagecontrol.zip 364.4 kb, 353 downloads since 2018-05-17 

Re: Links tables together

Hi Derek

Thanks for your work.  Will have a rummage today.  Thinking about re building the whole project.... so scope there to improve!

tc