Reading the (n)th record from a related table

[Greg Hains]

Good evening,

I am looking for some help please - is anybody able to give me a clue as to how I can achieve what I need quicker than what I'm already doing?

Description is:

Two tables and their fields.
   CLIENT
       CLIENT_ID   
       HIRE_STATUS

   HIRE
       CLIENT_ID
       HIRE_OUT
       HIRE_RETURN

They are linked together via a CLIENT_ID field. It's a one-to-many relationship.

Any given client can hire a widget.
The date they hire it out is HIRE_OUT, and the date it is returned is HIRE_RETURN
They may hire it for one day, or they might hire it for several days. 
They may have hired it a few days ago and as it's not yet returned so whilst the HIRE_OUT data is populated, the HIRE_RETURN date is still empty.

What I'm trying to do is to establish from the CLIENT table if a client currently has a widget out. I achieved this in a cumbersome way by:

1. Flicking back to the HIRE table to the related records,
2. Locating the most recent hire entry after having been sorted,
3. Determining if the hire was closed (populated HIRE_RETURN field)
4. Returning to the CLIENT table (layout) and updating the HIRE_STATUS field.
Very manual and clumsy - I know.

I am looking for the likes of a calculation (or any method really) that will read the most recent HIRE record without having to go to another layout.
Is there a function that will allow me to read specific fields, from the last (most recent) record from a related (sorted) table so as to estbalish the current hire status?

Any help is appreciated.
Thanks,
Greg

 

[comment]

How about counting the HIRE::HIRE_OUT values and comparing that to the count of HIRE::HIRE_RETURN? 

Note that this requires you to fill the HIRE_RETURN field after settling a case of loss, for example.

 

Another option is to do what you said in the title: count the related records (i.e. the HIRE::CLIENT_ID field) and use that to check the value of the HIRE::HIRE_RETURN field in the last record using the GetNthRecord() function (this is assuming your relationship is unsorted).

 

Edited August 24, 2017 by comment

[Greg Hains]

Hi Comment

Thank you again for your help.

What I failed to mention too - my apologies - was that there is a HIRE_TYPE and that was a value of DAILY or PERIOD, so if DAILY was used then the HIRE_RETURN was not used. I'm thinking of changing that so both HIRE_OUT and HIRE_RETURN are both populated with the same date with a daily hire so that an empty field definitely means a widget is still out for hire. Much cleaner I suppose.

I originally had that method you suggested, but with the way it was recorded (see above) counting the populated fields did not work.

Thank you.

Greg

[comment]

3 minutes ago, Greg Hains said:

if DAILY was used then the HIRE_RETURN was not used.

Do you not record the fact of the widget being returned in any way?  

[Greg Hains]

You're right Comment.
It is assumed (awesome attitude in a logical world, lol) that the widget was returned that day. It's not failed in that respect, but now they want to report on it, the wheels fall off.

My post now turns from requiring a tricky method to going back to basics.

Thank you. :-)  Appreciated as always.

Greg

 

[comment]

5 minutes ago, Greg Hains said:

It is assumed ... that the widget was returned that day

Well, if you wanted you could support such assumption by auto-filling the HIRE_RETURN field for them when they select DAILY as the HIRE_TYPE. Or use a calculation field that would be true if HIRE_RETURN is filled or HIRE_TYPE is DAILY. Not the best practice, IMHO, but it's their business rules. 

 

[Greg Hains]

I agree Comment. I will put that to them.

It was their decision to not do it in the first place (apparently), but it's only going to cause (me) headaches - it's already a discrepancy in the logic.

Thank you.