Hi Mark,
Thank you so much for your quick reply! I was not able to build a relationship because
One Topic in the "Topic Repository" can have multiple trainings in "Training Records"
and One Training in "Training Records" can have multiple Topics in "Topic Repository"
For example, a training done on 1/1/23 can cover Topic A, Topic B, and Topic C
While Topic A can have trainings that was done on 1/1/23, 2/1/23, and 3/1/23
I wasn't able to do a circular relationship (if that's even possible) because I could only reference one parent per child.
Currently, the Topics field in "Training Records" is a multi-select with values pulled from the other table.
Is there a way I can pull the Date of Most Recent Training as a Formula Date Field?
Thank you again for your reply
Qrew LegendCan you tell me how many topic repository records you have and how stable they are? I have a solution for you if they're fairly stable and you don't have a ridiculous number of them.
But right now I need to jump on a spin bike. Back at the computer later this evening.
Qrew LegendThe right way to set this up from a database design point of view is to have a middle "join" table called perhaps Topic Trainings.
When you set that up, initially it has no fields other than the 5 built in fields.
Make a relationship or one Topic Repository has many Topic Trainings. Look up the Topic and set the Related Training Topic field to have a Proxy field of the Training Topic if the Key field of Training Topic Repository is [Record ID#].
Make another relationship where one Training Meeting has many Topic Trainings. Set the field for Related Training meeting to be some identification field (maybe the date or a concatenated field of the date and the dept being trained) from that meeting that you look up into the joint table.
If you set up your application this way you have endless possibilities for reporting, including a super easy way to get the date of the most recent training up to the topic repository. That would just be a look up of the meeting date down to the Join table (Topics Trainings) and then a summary maximum field up to the Topic Repository table of the maximum of that meeting date field.
It would be possible with some data manipulation to coax your existing data in your multi select field into that join table.
I thought about whether it would be possible to use a formula query but unfortunately at Formula Query has a function to SumValues for a set of records but unfortunately there is no function for MaxValues at this time.
The only other way I can think to do this would be to create a helper table with a single record with Record ID of 1. You would join that to all of the Training Meetings with a related helper field with a formula of 1. Then you would run a whole bunch of summary fields up to that helper record looking for the Maximum training date which contains "HIPPA". Then another summary field for the Maximum training date where the multi select field captains "Hospice", Etc. etc. for each Training Topic.
You could look up those dates down to your topic repository table and then have a long IF statement to check the Training Topic and return the date of the last training. The problem of course is that every time a new training topic is added, you have to do actual programming to create a new summary field and a new look up field and adjust your IF statement. That's why the route of a proper database design with a middle join table is preferred.
Wow Mark.... Thank you SO very much for taking the time to answer. I've read and re-read your instructions about 100 times and am stuck.
I understand that I need to create a "middle man" table. I created the middle table:
Here is how I have it set up per your instructions. The only inputting that happens is from our trainers, who will input training logs into Table 3 (Training Logs). There are a bunch of topics already uploaded into Table 1 (Topic Repository). When a trainer inputs a training log, what do I want to happen? I am confused about the proxy fields.
Thank you again for your assistance.
