field to display latest date from a portal with many records

[baweld123]

Hello all, I was wondering if you could help me?

I have a portal for service records for equipment with each portal line being a different certificate. On this portal line i have a retest date. We have been missing some services for customers and this has created problems so on the main customer screen i wanted a field in company details showing the latest retest date.

I just copied the retest date field from the service certificates layout and it does in fact give me data. but it gives me old data. If i then for the same customer make a new certificate the  date field on customers layout still only shows the older  date not the new one. How do i make this pick the latest certificate retest date?

 

In the picture Top left i have the service field ( this is simply a copy of the retest date field in the right hand bottom corner) it shows a date but this is from a previous service date in that same portal.

 

Thanks in advance for your help

 

PS i was also thinking maybe i could get Filemaker to do something trendy like tell me a month in advance by a popup when i login but ill save that for another day :-)

Thanks

 

 

[baweld123]

Ahhh Ok ive done what i think is right let me tell you what i did.

I created a new portal with only the retest date field visible and created a filter of the date. 

The field then shows the latest certificate date. 

But is this an efficient way of doing it ?

thanks.

 

Love filemaker Love learning it and really pleased with what i can create although scripts still seem like another world to me :-)

[comment]

We have been missing some services for customers and this has created problems so on the main customer screen i wanted a field in company details showing the latest retest date.

 

I got a bit lost in your explanation. Shouldn't you be looking for the earliest retest date that hasn't occurred yet?

[baweld123]

Thanks for the reply, what i meant was when i add another service after the first retest date a new line is entered in the portal showing the machine test date, machine tested and lastly the retest date. What was happening was in the field i created in the customer layout HIGHLIGHTED with this retest date it only showed the retest date from the first portal entry. It wouldn't show the new one with the new retest date.

So i created  a portal with only one field (retest date) and only one portal line in customer layout and asked it to sort ascending. This now shows the latest retest date and ignores all previous retest dates in the portal. 

[comment]

So i created  a portal with only one field (retest date) and only one portal line in customer layout and asked it to sort ascending. This now shows the latest retest date and ignores all previous retest dates in the portal. 

 

I got the impression (mostly from your screenshot) that a customer may have more than one machine. Let us assume a customer with two machines: Widget and Gadget. And let's say that the records in the ServiceLog table (for these two machines only) show:

Machine     ServiceDate     NextServiceDue
-------     -----------     --------------
Widget      2013-05-15      2014-05-15
Gadget      2013-09-21      2014-09-21
Widget      2014-05-10      2015-05-10
Gadget      2014-09-20      2015-09-20

Now, if you have a one-line portal line in the customer layout that shows only the latest retest date, then for this customer it will show "2015-09-20" - suggesting that there is nothing going on until September. But actually the date to watch for is the one in May, is it not?

[baweld123]

yes you are correct, so how would you do that ? A lot of my services are all site so this scenario would be quite rare but i would get this when I'm weld testing a person as sometimes i cannot test all the people on a site at once. So this function would be helpful yes. 

[comment]

You could define a calculation field in the Machines table (result is Date) =

Max ( ServiceLog::NextServiceDue )

With this is place you can have a portal to Machines, sorted by the calculation field, showing all future due dates. If you prefer, you can make it a one-row portal to show only the next i.e. the earliest upcoming) date.

 

 

Another option you might consider:

http://www.briandunning.com/cf/1266

[baweld123]

hello Thanks for the replay but calculations scare me :-)

i tried what  you said but i get error ( a number text constant, field name or "(" is expected here but to be honest i haven't a clue what I'm doing with the calc.

this is what i have 

Table- BS Testing Input

Fields 

Date Tested - Creation Date

Retest Date - Creation Date Auto Enter Calculation ( Calculation is  Date Tested  + 729)

I have created another field for your calculation called NextTestingDue and tried to enter calculation but i get the error above. 

what i entered was this but to be honest i don't understand it. (I'm Guessing)

Max ( Retest Date {; Date Tested} ) with this i get field cannot be found 

 

thanks for your help

[comment]

 calculations scare me :-)

 

I am not sure I can do anything to help you with that...

 

This should be really easy - see if the attached helps.

NextTest.fp7.zip