My Visual Database — Database Design Question

Re: Database Design Question

null@example.com (derek) — Sun, 13 Dec 2020 00:31:55 +0000

Perhaps the bigger benefit is that the id of the logged-on user (and any related information held in the _user table) is now available, if required, from anywhere else in your program with a simple reference back to 'form1.edit1.text'
Derek

Re: Database Design Question

null@example.com (asawyer13) — Sat, 12 Dec 2020 19:34:11 +0000

Nice, I did get it to work with loading the grid by code, but I like your way much better. 4 less lines of code.

Re: Database Design Question

null@example.com (derek) — Sat, 12 Dec 2020 19:32:15 +0000

Hi,
All you need to do is pass the userid of the person logged on and use it as a search criteria (see attached).
Try it with users Alan (password is alan) and Derek (password is derek) to see different doctors depending on who added them.
Derek.

Re: Database Design Question

null@example.com (asawyer13) — Sat, 12 Dec 2020 19:10:00 +0000

OK, all issues resolved except for one.

When I open the form that has the doctor grid on it, I don't know how to filter for only doctors that have the userid of the person that's logged in.

I think that's my only issue left...

Is there a magical way to do that or will I have to look at loading the grid via script?? If so, I'll have to track down an example of that.

Thanks
Alan

Re: Database Design Question

null@example.com (asawyer13) — Sat, 12 Dec 2020 18:43:32 +0000

I ended up doing a dbupdate upon opening the form, and so now it loads values into my combobox, however if the record has a value it doesn't show.

Obviously I'm doing something a bit wrong.

I can't attach my project here as it's getting much bigger and more proprietary.

Alan

Re: Database Design Question

null@example.com (asawyer13) — Sat, 12 Dec 2020 18:24:54 +0000

I added the relationship between Doctor and _user and I can get the proper value loaded in the table, however I don't know who to get the Grid when I want to list the doctors for that _user to work properly. If I say show all then it indeed shows all, but that includes other users records.
If I say Show child records (if any, I don't get anything

I could try adding a filter but don't know if there is anyway to add the application.user.id field in the Filter since the filter I believe is actually a sql statement filter.

and then I still have the combobox not loading values when the form starts up. It doesn't show what was loaded into the record also

Re: Database Design Question

null@example.com (asawyer13) — Sat, 12 Dec 2020 17:47:26 +0000

Right now I have the combobox in medications form set to Medication.id_Doctor and DrName for the foreignKey and FieldName.

Do I have to do something special to get the combobox to first load with values?? If I go to my Add Doctor form and then come back, the combobox is loaded with the proper values.

If that can be made to work, then my only issue is the best way to load the grid with the doctors for the family, hoping that maybe the _user relationsihp will fix that?

I feel like I'm close.

Alan

Database Design Question

null@example.com (asawyer13) — Sat, 12 Dec 2020 17:35:02 +0000

I am not sure how to define this in the Database Tables area.

I have a user table. It's called _user because I'm using the roles feature.

I have a Person table so each user can add a number of persons. I have a relationship of _user in it.

I have a Medication table so each Person can have a list of their medications. I have a relationship with Person in it.

I now want to have a table of Doctor names. It probably would be one set of doctors for the whole family so at the _user level, however each medication will need something that identifies which doctor this is for.

I don't know how to design that. I want a combobox in the Medications form that would show the doctors, then be able to pick one.

Maybe I need to relationships??? One to Medications and one to _user????