Identifying the highest value in a field, relating to each primary key in a table.

[Karlos Fandango]

NEWBIE

 

I have in my database a table with its layout called DASHBOARD, it reports on certain date related things. 

 

However, although in a table called BLOODTESTS I wish to store a history of all employees blood lead levels, in DASHBOARD I only want a portal showing each employees next blood-test... I'll probably add within it a field showing how many days until or overdue.

 

However I do not possess the necessary skills to ask Filemaker to identify the next blood test for each employee, that has supersedes all of those that proceeded it for each employee.

 

I'm finding this difficult to explain so pressed for time decided to record a short two minute video expounding the issue, I hope that is allowed here at FM.

 

Here is the video.

 

In the video I wrongly identify the latest blood test for employee 2 as the highest [sort oder wrong] but I'm sure you'll understand just what I'm getting at.

 

I'm guess that the answer I require maybe to complex for me to implement, but thought I'd ask anyway... I'm holding my breath.

[eos]

A very simple solution would be to apply a portal filter: create a calculation field in Employees, defined as Max ( Bloodtests::theDate ) – don't forget to set the result type to Date! – and define the portal filter as (using your names from the Dashboard TOG)

Bloodtests 2::theDate = Employees 4::cMaxDateBloodtest

In the portal on the dashboard layout, show theDate from Bloodtests 2, and the employee name from Employees 4 (and maybe use some meaningful TO names …?!)

 

Be aware that a portal filter will be/become slow, depending on the number of bloodtest records that have to be filtered (and using an unstored calculation, at that).

 

A better approach would probably be to create a date field in Employees and set it to the latest date whenever you add/edit(/delete?) a bloodtest record; now you can simply show all Employees records, or apply a filter based on the existence of a date in that field, or e.g. use a relationship filter based on a date range.

[Karlos Fandango]

A very simple solution would be to apply a portal filter: create....

 

Hi Eos...

 

Forgive me but I'm struggling to understand your instructions... firstly what is the acronyms TOG [the open group?] and 'TO'?

 

With those understood I'll probably leave it until tomorrow to try to get my head around your suggested resolution. Thank you for taking the time... I do hope it is an easy solution as I have been looking at other means that seem to involve very heavy scripting.

 

I'm sure you understand that what I want to end-up with on the DASHBOARD is only one occurrence of each employee... with their next blood-test appointment beside it, all this while still storing all tests results with their relating info [especially the lead levels and advisory notes] in the BLOODTEST table.

 

I shall 'gird-up the loins of my mind' and make the attempt tomorrow, tonight I'm all burnt-out.

 

Thanks again, appreciated.

[eos]

firstly what is the acronyms TOG [the open group?] and 'TO'?

 

TO is the acronym for the (official) term “table occurrence”, and TOG is the acronym for the concept of a “table occurrence group”.

 

Now that we have that out of the way, don't get hung up on names. The portal filter should be quite easy to implement, since the instructions are straightforward (if I say so myself).

 

I'm sure you understand that what I want to end-up with on the DASHBOARD is only one occurrence of each employee... with their next blood-test appointment beside it, all this while still storing all tests results with their relating info [especially the lead levels and advisory notes] in the BLOODTEST table.

 

Yes, and that is what both the filter and the other approaches described will give you – unless (in the case of the portal filter for Bloodtests) an Employee would have two test on the same date.

 

(If you use a field in Employees to implement a relationship filter, you can only get one record per Employee – their very own Employees record – regardless of multiple Bloodtests records for the same day.)

[NLR]

I'm sure you understand that what I want to end-up with on the DASHBOARD is only one occurrence of each employee... with their next blood-test appointment beside it, all this while still storing all tests results with their relating info [especially the lead levels and advisory notes] in the BLOODTEST table.

 

I watched your video and that helped a lot.

 

Following from that, I was able to make a small file to cement my ideas, and I think it miht be what you are after..  Basically I used your Tables with minor changes.   I defined a new Employees field: "NextAppointment" which calculates the maximum date from all visits by that particular person (as per your BloodTests Table).

 

Then, in your Dashboard table where you currently have a portal displaying BloodTests, I chnged that portal to show Employees.  And with those employee records you can see the "NextAppointment" field.

 

I'm sure there are variations to how this can be done, and one consequence of using an unstored calculated field (of necessity because it's referencing a related field), is that a portal display in another window will need to be refreshed to show any changes, and this might be a problem if there are many records.  To get around this issue, a variation (which I've shown in a second file) is to use a so-called auto-enter Date field which actually uses the same calculation.  Because auto enter fields rely on changes to "Local" fields to update themselves, we need to employ a means of doing this artificially, and the method I used was to have a script trigger activated by any entry/change in the "NextAppoint" field in your Blood Tests layout.  Normally I'm hesitant to use triggers.  In this case it might be an opportunity to use such a script for other follow-up purposes such as checking an entry.

 

Regards

Ralph

NextBloodTest.zip

[Karlos Fandango]

A very simple solution would be to apply a portal filter: create a calculation field in Employees, defined as Max ( Bloodtests::theDate )...

 

Hi Eos, I instigated your first solution which works really well. Unfortunately my limited knowledge and understanding of terminologies prevented me from implementing the second, though I did try.

 

 

Thanks again eos for taking the time, it is appreciated. 

[Karlos Fandango]

I watched your video and that helped a lot.

 

 

Hi Ralph Learmont.

 

Thanks for the file, I have just downloaded it and shall soon take a close look at your solution. I implemented eos' first solution that works very well, but have other similar challenges with employees cards, certificates and qualifications... most of which have expiry dates and other issues that need constant monitoring on the 'Dashboard'.

 

Looking at your solution may not only resolve these issues but more importantly, educate me regarding FM and how competent users think and work with the program. Building databases is very different to anything I have worked with before, and I have worked with quite allot of different packages. Currently the scaffolding of my mind is refusing to budge from its traditional way of thinking... yet I press-on in hope.