1 Topic by asawyer13 (edited by asawyer13 2020-12-12 17:37:40)

  • Registered: 2019-07-15
  • Posts: 73

Topic: Database Design Question

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????

  • Registered: 2019-07-15
  • Posts: 73

Re: Database Design Question

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

  • Registered: 2019-07-15
  • Posts: 73

Re: Database Design Question

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

  • Registered: 2019-07-15
  • Posts: 73

Re: Database Design Question

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

  • Registered: 2019-07-15
  • Posts: 73

Re: Database Design Question

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

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,688

Re: Database Design Question

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.

Post's attachments

Attachment icon alan.zip 336.91 kb, 273 downloads since 2020-12-12 

  • Registered: 2019-07-15
  • Posts: 73

Re: Database Design Question

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.

8 Reply by derek (edited by derek 2020-12-13 01:23:36)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,688

Re: Database Design Question

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