Topic: Setting Relationships

Hello,
I'm having a problem understanding how to set relationships.  It should be pretty straightforward but I'm getting myself confused. 
- I have 2 tables, one for Users and one for Housing
- Users can have many entries
- Housing can have multiple entries for each user (they may have lived in different places over time
- Do I set the relationship field in Users and have it point to Housing?
- or do I set the relationship field in Housing and have it point to Users?
- When I add a relationship field in Housing it asks me to "Select the parent table to Link"
- So when I do that the schema shows a one to many going from Users to Housing (if I'm reading that correctly?) This sounds correct to me?
- But in some of the code examples I've seen multiple relationship field entries in the main table (like my Users table) suggesting to me that Users is not always the parent table.
-
- In my application I'm going to have multiple tables with data that will belong to one of the entries in the User table.
-
- Like I said, I'm getting myself confused.  Are there any rules or guidelines as how to set relationships for multiple tables?
-
- Thanks for your help, Frank

2 (edited by CDB 2020-07-12 00:41:13)

Re: Setting Relationships

I think you might require a 3rd table as the intermediary .

Your Users is the parent link  table - you have a link in your Housing table back to Users .


As an example I am working my way through an 'orders' database.   I have Technicians who can have many orders and those orders have multiple items.


I've attached part of my schema - so for you Technicians = Users and Houses = Orders, I'm struggling to think what you would call the 3rd table.  You might not need a third table, but if you find you are entering the same house data for more than one User theoretically you ought to move the data to another table.


Now that I've confused you and me, the picture is attached below.   There is a website that has over 300 database schema to look at as a base idea. The moment I can find it, I'll post the link. The author designs database for the US and UK councils etc.

Post's attachments

Attachment icon db_links.PNG 96.27 kb, 107 downloads since 2020-07-12 

On a clear disk you can seek forever

Re: Setting Relationships

Any help will be appreciated.
-
I'm pretty much stuck.  If I can't figure it out I'll have to abandon MVD.
-
I have the 2 tables as mentioned in my previous post.  Users and Addresses.  I want to be able to choose a user in a tablegrid and then, using a button, go to that users addresses in another table grid.  Then I would like to select that address and edit it.
-
- However if I set the Address tablegrid to "show all records" then I get all address records, even those that belong to another user.  If I set the tablegrid to show only child records then I don't get any records.
-
- I think it has something to do with relationships.  And my guess is that I'm probably doing something wrong in trying to manage those 2 tables.
-
- It's probably something simple, but I've been trying everything I can think of and having no luck.
- I'm a newbie in case you haven't figured that out yet.
-
- Thanks for any suggestions.
- Frank

4 (edited by ehwagner 2020-07-16 04:02:19)

Re: Setting Relationships

papafrankc,
Welcome aboard. No need to abandon MVD. It can handle most any situation. What you want to do is quite simple. See attached project for an example of what you are trying to do. It is a very simple setup. MVD gives you various ways to show/update info. But as far as the database relationship, you should set the relationship on the child table (addresses) pointing to the parent (users). Click on dbschema button in the database tables tab to see how the relationship is defined.


Hope this helps. If you have any other questions, don't hesitate to ask. There are many people on here to assist.

Post's attachments

Attachment icon Users_Addresses.zip 336.63 kb, 289 downloads since 2020-07-16 

5 (edited by derek 2020-07-16 11:11:09)

Re: Setting Relationships

Hi Frank, Hi EHW,
This is just a long shot so I hope it doesn't add to the problem.
I wonder if part of your confusion is that, having related the two tables together in your database schema, you think that by placing two tablegrids (one showing user data and one showing address data) ON THE SAME FORM, that those tablegrids are now 'linked'.  This is not the case - tablegrids are NOT the same as tables and, as it stands, tablegrid2 has no way of knowing that you've clicked on a row in tablegrid1 and want to see the related records.
If this is the case, then you simply need to add a button and assign a 'search' action to it - the 'search' action is (among other things) what links tablegrids on the same form together.  Please look at screenshot1 in the attachment - in essence, it says 1) use (a highlighted row in) tablegrid1 as a selection criteria to filter records from 2) the addresses table and 3) output these chosen fields to 4) tablegrid2 - the tablegrids are now linked - and linked according to the relationship you set up in your database schema.
In 'basic' MVD using two discrete forms, none of the above is needed, as EHW has show you in his attachment;  Form1 has a tablegrid to show your list of users and then Form2 has a tablegrid to show a list of addresses related to the user that you had highlighted on Form1 - as you move from Form1 to Form2, the addresses are filtered automatically.
In the attachment are two examples which do exactly the same thing as EHW's example but with the two tablegrids on the same form.
In the first example (frank1), highlight a user and then click 'Find Addresses' to show the related addresses.  You can then double-click on an address and go directly to the address maintenance form.
In the second example (frank2), it builds slightly on this, simply to make it more user-friendly.  If you look at screenshot2, you can specify tablegrid1 as an 'incremental search';  this simply means that whenever a different row is highlighted on tablegrid1. the 'search' button is automatically clicked (you can then optionally make the 'search' button invisible to tidy up the form).
As with all posts in the Forum, if you can attach your project, it really helps others to better understand your question and offer a more focussed solution.
Hopefully this helps move you along,
Derek.

Post's attachments

Attachment icon frank.zip 1.04 mb, 314 downloads since 2020-07-16 

6 (edited by papafrankc 2020-07-17 17:54:23)

Re: Setting Relationships

Thank you EHW & Derek
-
EHW's solution works perfectly. I did have some trouble trying to duplicate it in my test app.  But that was on me, not paying strict attention to the details.  Following this example I should be able to go ahead with my project.
-
Derek, thanks so much for the 2 examples you sent.  I took a quick look and they look really cool.  Yes, a little while ago I did try to put 2 tablegrids onto one form.  That didn't work out as you mentioned.  So, I'm going to go over your 2 examples so I can understand exactly how they work.
-
A comment on relationships: I had the mindset that a PARENT table should point to a CHiLD table when setting up relationships.  This comes from working with other databases.  It's a simple thing, but knowing that I need to set the relationship from the Child to the Parent table makes it easy to understand.
-
FYI: when I tried to download frank.zip using Google Chrome, it wouldn't let me.  It said it was a suspicious file.  Maybe because it had my name as the file name?? I've downloaded example files from the forum before with no problem.  Chrome just being picky I guess.  Anyway I went to Firefox and the file downloaded with no problem.
-
Comment: the Forum is great.  The amount of support you folks provide is impressive.  Especially when you include a complete program showing how to complete the task.  This is really helpful to someone like myself who is a newbie.  Thanks again smile

7 (edited by papafrankc 2020-07-18 03:33:24)

Re: Setting Relationships

Derek,
-
I've been looking at your frank2 code.  It's really great and I'm learning  lot.  I've started to play with it to make it do some of the things that I hope to be able to eventually do.
-
Based on your code, let me explain what my ultimate goal is and see if you have any advise.
When I double click on a user I want to go to a MainMenu form.  On this new form I will have quite a few buttons for them to choose from.  Addresses will be one of them.
-
As a test I added the new MainMenu form and put the User Name & Address on that form. I then put a MainMenu BUTTON on Form1.  Now when I double click on a User Name it automatically goes to Form2 (Addresses).Then when I close Form2 my MainMenu form shows up.  It does show the Users Name as I want.  Actually going forward I want to ONLY show various records for this user. 
-
When I click on my new MainMenu button on Form1, it does go directly to my new form as I want.
-
Then if I go back to the beginning and pick a different user, I want to see only that persons records.
-
I'm thinking that I might need a variable to store the Users ID so I can make sure I'm only seeing that users records when I move from menu item to menu item?  Or maybe's there  another way?
-
I noticed that the FIND ADDRESSES button becomes invisible when running the app.  Is this button related to when I double click on a User name?  And is it necessary?
-
I'll work on this some more to see what I can do.
-
I've attached a copy of my program.  I haven't zipped anything before so I hope it's OK
-
I really appreciate your helping me with this
-
Thanks, Frank

Post's attachments

Attachment icon frank2-Modified.zip 467.16 kb, 248 downloads since 2020-07-18 

8 (edited by derek 2020-07-18 16:10:08)

Re: Setting Relationships

Hi Frank,
You could do it as you suggest but you would need to be careful that you were passing through the userid as you moved from form to form.  One way of keeping it all within the same form is to use a 'page-control' with as many 'tab-sheets' as you have menu items.  Think of 'page control' / 'tab sheets' as sub-forms - or forms within a form.   Doing it this way, MVD takes care of all the keys and links for you automatically.
.
In the attached example, I've added a few 'menu items' as tab-sheets just to give you a flavour for how you might approach it.  On Form1, I've also used calculated fields so that you can see how many of each menu item has been added (it just saves the user having to go in and out of each record).  Calculated fields are defined in your database schema but are not 'real' - they are 'virtual' fields and only exist whilst your program is running.
.
In answer to your specific question, the 'find addresses' button on the original attachment was needed to filter addresses when displaying them on the same form as the user name;  it's not needed if you take the approach suggested above.
.
I imagine by now you're getting a feel for the flexibility of MVD and none of this involves any programming (yet - LOL!).
.
Not sure if this is the direction you're planning to move in but hopefully it will give you some ideas.  Any problems, just shout and I'm sure someone will try and give you a steer.
Derek.

Post's attachments

Attachment icon frankmodifed3.zip 349.45 kb, 280 downloads since 2020-07-18 

9 (edited by papafrankc 2020-07-18 21:35:30)

Re: Setting Relationships

Derek,
Wow, this is exactly the direction I'm heading.  I continue to be impressed about how many things MVD does automatically with out having to write any code.
-
And I'm equally impressed with the support that you and the other members of the forum provide to newbies like myself.
-
I feel a little guilty about pestering you folks with my basic problems, but without your replies and guidance I'm afraid I wouldn't be progressing very well with my project.
-
I'm going to dig into your latest sample program and see what I can do.  Knowing me, I'll probably be back with more questions
-
Thanks, Frank  smile

10 (edited by papafrankc 2020-07-19 01:14:21)

Re: Setting Relationships

Derek,
I'm working on the pagecontrol in your provided solution.  It's pretty cool,  a little challenging so far but that's OK .   
-
One of the first things I'm trying to do is add some more tabs to the control.  For my current database design I have 15 Tabs where a user might need or want to go.  When I add some more tabs to the pagecontrol, they get added to the right as a new Tab.  But when the number of tabs exceeds the width of the page control it puts up a couple of arrows to move back and forth. Which doesn't seem too user friendly to me.  Ideally I'd like to have them all be visible on the MainMenu page.
-
I'm wondering if there is any way to make 2 or more rows of tabs.  This way all of the choices would be visible on one page?  My tabs(categories) are all different so I don't think I can put them into groups.
-
Or maybe I need to do some redesign of my overall application?
-
FYI - I wrote my original application in Microsoft Access.  It works pretty well, in my opinion, and I was ready to deploy it.  That's when I ran into having to use MS Access runtime and also having to try to deal with the security permissions that are part of MS Access.  MVD takes care of all that stuff - automatically.  Very very nice.
-
See I told you I'd be back  smile
- Thanks, Frank

11 (edited by derek 2020-07-19 10:51:00)

Re: Setting Relationships

Hi Frank,
The easiest way is to set the 'multiline' property of the 'pagecontrol' object to 'true' (please see the screenshot in the attachment).  Then, as you expand or reduce the width of the pagecontrol, the tabsheets automatically re-arrange over 2 (or 3 or 4 etc) lines.
If you could group the tabs logically, then you could use a pagecontrol within a tabsheet and establish a hierarchy but from what you wrote, it sounds like that might not be an option.
Another way would be to have buttons (15 of them) on your main form that then call 15 discrete forms but you lose the 'form within form' effect that you have with tabsheets and pagecontrols.  I guess it's just 'horses for courses' and whatever works best for your particular project.
Without knowing your project, 15 tabs does seem quite a lot and I'm wondering whether there's an element of trying to replicate your  MS Access project (perfectly understandable) rather than redesigning it so that it plays to the strengths of MVD.

Post's attachments

Attachment icon franklotsoftabs.zip 540.41 kb, 306 downloads since 2020-07-19 

12 (edited by papafrankc 2020-07-20 03:37:26)

Re: Setting Relationships

Derek,
Super - your suggestions works perfectly. You get a gold star smile
-
I believe your suggestion to put a pagecontrol inside a tab will work out great for me.
-
I actually have 17 tabs, but 3 of them can be combined under one heading and another 4 can be combined under another tab.  So that will leave me with 12 tabs with 2 of them having sub-tabs. Sounds good to me.
-
Now that I know a little more about what MVD can do, I'm going to take a look at my MSACCESS design & see if it could be changed to maybe something better and simpler.
-
Maybe some day all these great suggestions from yourself and others could be put into a tutorial.  Along with the examples.  It sure would be nice for a newbie like myself.
-
By the way, I like your colored borders.  You were kinda sneaky though smile I was looking all over to see where to set the border to a different color.  I finally discovered that the colored borders are not borders, but are panels behind the object.  A pretty good lesson on how to use a panel. Pretty cool.
-
Thanks again, and please stay safe out there.  It's a crazy world, especially now.
-
Frank