Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

How do I solve this Find Calculation?

Question asked by joshuaw07 on Jun 28, 2018
Latest reply on Jul 13, 2018 by philmodjunk

Like • Show 0 Likes0
Comment • 9

I am working with student profiles for an academy. Each profile has a series of responsibilities we keep track of. Each responsibility has its own unique due date.

I need a find calculation for a server side script that returns any record that is less than or equal to 7 days from the approaching due date.

What would be the best approach to this.

Conclusion:

I need to create a find calculation within a server-side script that finds the records that are approaching a deadline (7 days before the deadline). Once found, the script will mark the status of the records as "Due."

philmodjunk Jun 28, 2018 9:29 AM

Correct Answer

But the day due differs for each different responsibility correct?

I would add a calculation field that uses the data you have here to calculate the date that it's due so that you can find all records where the date due is <= the current date.

Enrollment_Date + day_due might calculate that date. It depends on how weekends and holidays factor into how you calculate this. We work from a school calendar table in our system and thus can factor such details into our calculations by referencing that calendar table.

See the reply in context

No one else had this question

Outcomes

9 Replies

nicolai Jun 28, 2018 7:43 AM
Mark Correct
Correct Answer
Find works on the server side, just search for the records with due date less or equal than target date - 7. Make sure you are in the right layout

I think you will need to use "Insert calculated result" instead of "Set Field" and set due date field to

target_date - 7 ... target_date
2 people found this helpful
Like • Show 0 Likes0
Actions
philmodjunk Jun 28, 2018 7:52 AM
Mark Correct
Correct Answer
Set field works just fine in current versions. Many versions ago, we had to use that "insert" option. Don't use steps that start with "insert" unless you have a specific reason to do so. They fail to work if the specified target field is not present on the current layout and since they also put the focus in that field, they can trip script triggers. These headaches are reduced when you use Set Field instead.

It would help to know how you list the different responsibilities in your system. Ideally, each student profile record should be linked to a related table of responsibility records where you have at least three fields: 1 to identify the responsibility, 1 to record the due date and 1 to link it to a specific student profile by the Profile record's primary key value. This makes it possible to perform the needed find on a layout based on this table of responsibilities.

The next challenge is what to do with the find results in order to communicate them back to your user(s). Will this be a scheduled script that runs on a regular schedule or will you be using a Perform Script on Server (PSOS) call to do it? The former can generate a report of the results and email it to designated recipients or even create a "report record" (Or group of records) that the users can pull up. With PSOS, you'd need to return a list of student and responsibility IDs as your script result back to the client that did the PSOS call so that your client side script can reproduce the resulting found set.
Like • Show 0 Likes0
Actions
joshuaw07 Jun 28, 2018 8:07 AM
Mark Correct
Correct Answer
Thank you for the feedback. philmodjunk yes i have all 3 fields you proposed.

The due date is actually a due day (a number field not a date field). the responsibility is due on a particular day of service.

So for example, when a student transfers to our school, a student's transcripts must be transferred by the 20th day of enrollment (or the 20th day served).

I have the following fields:
- ::Day_Served (DS) field which calculates the total number of days we have served the student since enrollment date.
- ::Day_Due (DD) filed which is a number field, not a date field that is preset when the student's profile is created.
So on the script find calculation I need to find all records with a DS of 13 (7 days minus the DD).

Thanks
Like • Show 0 Likes0
Actions
philmodjunk Jun 28, 2018 9:29 AM
Unmark Correct
Correct Answer
But the day due differs for each different responsibility correct?

I would add a calculation field that uses the data you have here to calculate the date that it's due so that you can find all records where the date due is <= the current date.

Enrollment_Date + day_due might calculate that date. It depends on how weekends and holidays factor into how you calculate this. We work from a school calendar table in our system and thus can factor such details into our calculations by referencing that calendar table.
1 person found this helpful
Like • Show 0 Likes0
Actions
joshuaw07 @ philmodjunk on Jun 28, 2018 11:02 AM
Mark Correct
Correct Answer
philmodjunk yes this is precisely what i'm trying to accomplish

We don't need to factor in holidays or weekends for this use in our academy. That should make it a bit easier.

Thanks. I'll let this run for approximately a week to determine if it is functioning right.

However, upon my first tests this works perfectly.

Thank you for the advise
Like • Show 0 Likes0
Actions
joshuaw07 Jul 12, 2018 4:58 AM
Mark Correct
Correct Answer
I was able to finalize this. Here is what I had to do:
- Our school uses a days served counter. So due dates are actually set by a "due days". Therefore I had to create a calculation field to convert the "due days" into an actual Due Date field. I did this by by:
  Calculating the Due Date by Enrollment Date + Due Day according to the thread above.
- Next I created the find criteria. Out of habit I used the Insert Calculated Result script step and calculated a find in the newly created Due Date field which was as follows:
  Get ( CurrentDate ) & "..." & Get ( CurrentDate ) + 7
- Finally, I uploaded the script to Filemaker Server to perform every day.
Thanks to nicolai and philmodjunk for helping piece together this Find Calculation
Like • Show 2 Likes2
Actions
philmodjunk Jul 12, 2018 6:26 AM
Mark Correct
Correct Answer
I suggest replacing the insert calculated result script with set field. Scripts that start with “insert” fail if the specified target field is not present on the current layout. Set field does not have that limitation.
3 people found this helpful
Like • Show 0 Likes0
Actions
joshuaw07 @ philmodjunk on Jul 13, 2018 5:31 AM
Mark Correct
Correct Answer
Excellent! Yes Thank you. I was unaware of that the Set Field did not have that limitation. Thank you for the clarification.
Like • Show 0 Likes0
Actions
philmodjunk Jul 13, 2018 8:16 AM
Mark Correct
Correct Answer
Copy, paste, clear and all the script steps that start with "insert" have this limitation. They also put the cursor into the specified target field--which can trip script triggers. And the triggers thus tripped might be ones added to a layout in the future as you don't get any kind of warning that adding a script trigger to a layout object might produce this situation. The script that is then performed by that trigger could interfere with the script that tripped that trigger. Triggers aren't nearly as big a concern for a scripted find as most triggers are enabled for Browse mode, but it's still another potential failure point that can creep into your solution without warning.

Thus, I avoid all of these steps unless I need some result that relies on a characteristic unique to that script step, using Set Field or Set Variable in their place if that is possible as neither will trip script triggers on a layout nor fail if the field is later removed from the layout during a future design change.

Note, however, that every script step that references data via the standard TableOccurrence::FieldName expression requires that you have the right "context" in place as determined by the current record of the current layout's found set.
Like • Show 0 Likes0
Actions

How do I solve this Find Calculation?

Outcomes

Related Content

Recommended Content