7 Replies Latest reply on Aug 1, 2016 4:29 PM by Vaughan

Calculation/Script problem with join table

Hi,

I'm working on education solution that requires some changes.

Our students (vocational school) during theirs education are hired for practical learning. Student during education time can have one or more employers, but only one at the time. Every employer can have many students hired. Every contract have information about when contrat has started and when it should expire (in case of present employers) or when it had ended (in case of past employers).

I've got tables Students, Contracts (which is join table) and Employers.

Students –< Contracts >– Employers.

Students::id = Contracts::id_Student

Employers::id = Contracts::id_Employers

But most of the work is done considering the latest employer (printing mails to them, creating letters or other info).

In Form Student Layout need to have adress, name of latest employer displayed. So far i did this by putting filtered portal based on join table on layout.

If(

Get ( CurrentDate ) > Contracts::endDateOfContract;

0;

1)

I also have putted same filtered portal on letters for print.

And that worked fine during the whole academic year, as solution was build to work on current year. Now the academic year has ended and I need to rebuild it.

Now in database I have graduates and still need info about the latest employer nad obiviously current way doesn't working as contracts has ended.

I was thinking about two solutions.

Students —<The Latest Contract >— Employers.

Students::id = The Latest Contract::id_Students and

Students::LatestContract (GLOBAL that contains value of 1) = The Latest Contract::ifLatest (Boolean, 1 if employer is the latest).

I could go and manually mark which employer is latest, but it seems to be pointless as I'm sure that it can be done with FM automatically.

What I need is to either calc or script that can evaluate if Employer is the latest.

Is there any way to create calculation in join table that can compere all endDateOfContract for specific Student and take value 1 for only the latest one (which endDateOfContract is the biggest)? Or is it job for script?

Students >– Employers

Students::latestEmployerId = Employers::id

And in this point I need calculation or script to check all records from join table and then put id of the latest employer.

Which way is better or simpler to create? Or maybe there is even simpler way to achieve what I need.

Any help would be appriciated, and if my explanation  is unclear ar blurry in some way, please tell me and I will add additional info.

• 1. Re: Calculation/Script problem with join table

So far i managed to establish that kind of relationship to solve my issue.

Students – The Latest Contract – Employer

Students::id = The Latest Contract::id_Students and

Students::endDateOfLatestConract = The Latest Contract::endDateOfContract

The Latest Contract is TO of Contracts table.

Students::endDateOfContract is date field filled with following script:

Set Variable [ \$EndDatesOfContracts ; Value: List ( Contracts::endDateOfContract ) ]

Set Variable [ \$Total ; Value: ValueCount ( \$EndDatesOfContracts ) ]

Set Variable [ \$Counter ; Value: 1 ]

Set Variable [ \$LatestDate ; Value: GetAsDate ( GetValue (\$EndDatesOfContracts ; \$Counter ) ) ]

If [ \$Total < 2 ]

Set Field [Students::endDateOfLatestContract ; \$LatestDate ]

Else

Loop

Set Variable [ \$Counter ; Value: \$Counter + 1 ]

Set Variable [ \$Nextdate ; Value: GetAsDate (GetValue ( \$EndDatesOfContracts ; \$Counter ) ) ]

If [ \$NextDate > \$LatestDate ]

Set Variable [ \$LatestDate ; Value: \$NextDate ]

End If

Exit Loop If [ \$Counter = \$Total ]

End Loop

Set Field [ Students::endDateOfLatestContract ; \$LatestDate ]

End If

Is it good approach? Or there is some more neat way?

How to set script trigger to reevaulate the latest employer whenever record is created, deleted in join table or endDateOfContract is changed in join table?

• 2. Re: Calculation/Script problem with join table

macc wrote:

What I need is to either calc or script that can evaluate if Employer is the latest.

Is there any way to create calculation in join table that can compere all endDateOfContract for specific Student and take value 1 for only the latest one (which endDateOfContract is the biggest)? Or is it job for script?

Have you looked at the Last() function?

1 of 1 people found this helpful
• 3. Re: Calculation/Script problem with join table

Vaughan thank you for helping me.

I've sorted Contracts TO by ascending endDateOfContract.

Then I have set up a calculation field endDateOfLatestContract in Students table with:

Last ( Contracts::endDateOfContract )

After that i have set up relationship:

Students – The Latest Contract <– Employers

Students::id = The Latest Contract::id_Students and

Students::endDateOfLatestContract = The Latest Contract::endDateOfContract

As calculation is unstored i cannot take id of Employer from join table and put it in Students table to create direct realtionship from Students to Employers (as Students is many side of relationship).

Was it a proper way? Still need some testing before I will implement this to my solution, as it will require to change several layouts.

• 4. Re: Calculation/Script problem with join table

Eventually i decided to replace Last () with Max () – this will be more bulletproff – this way i can skip sorting TO.

• 5. Re: Calculation/Script problem with join table

Max() will require pulling the value down from all the related records then sorting them to return the value. With the Last() function, FM just grabs the value from the last related record. It may be faster to sort the relationship and use Last() but testing both techniques would be good.

If you're going to do some testing...

1) Max() with unsorted relationship

2) Last() with sorted relationship ascending

3) sort the relationship descending so the last record is first, then just call the field directly in the calc

1 of 1 people found this helpful
• 6. Re: Calculation/Script problem with join table

Thanks Vaughan to pointing that out to me.

All in all I will remain rookie for some time .

So far my solutions has around 600 students. 95% of them have one employer, 4 % have two. Only one student has 4 employers. And it is hard to think about situation when one will have more than that. So as far I'm unable to see any speed differences between those 3 ways. Will stick to Max () because if I suddenly start tampering with solution and change sorting nothing bad will happen .

And I am much appreciate for 3rd way – wanted to ask why there is no First () function, and now, I guess, I know the answer.

• 7. Re: Calculation/Script problem with join table

Yes, calling a related field always returns the value in the first related record.