Interesting Challenge in custom functions

[Tusif Ahmad]

Hi Every one.

I am trying to make a custom function or calculated field? What so ever is possible.

 

One table will have two date fields - start_date and finish_date

Same table will also have calculated fields Day1, Day2, Day3, Day4..... upto Day31

When we will write any start date of month and finish date of month, automatically in calculated fields 1 will come. For example Start date = 1/4/2013 and Finish Date = 6/4/2014   This should auto fill Day1, Day2, Day3, Day4, Day5, Day6 with 1. I do not want to use script or script trigger etc.

Is it possible?

I am attaching you test_dates filemaker 12 file as well with all fields.

 

Thanks in advance for your help.

 

Regards,

Tusif

[jbante]

I'm not entirely sure from your description, but I'm presuming that you want each Day[number] field to be 1 if the difference between start_date and finish_date is at least that many days (which I presume is for some display purpose, like triggering conditional formatting on a calendar widget). Please correct me if I'm misunderstanding.

 

First, consider using a repeating field that allows up to 31 repetitions instead: day[1], day[2], ... , day[30], day[31]; not day_01, day_02, ... , day_30, day_31. This way, you'll have one calculation to maintain instead of 31.

 

Second, the calculation I would start with would be something along the lines of: 

finish_date - start_date + 1 ≥ Get ( CalculationRepetitionNumber )    // 1 if True; 0 if False

 

Third, consider putting this calculation in conditional formatting rather than in the data schema. This will give you the ability to modify the calculation in the future without locking up the schema while you do it.

[Wim Decorte]

why would you not want to use a scripted trigger?  unstored calculations can become a real performance burden if you want to use those fields...

[Raybaudi]

unstored calculations can become a real performance burden ...

 

In this case I do not see any need for unstored calculations