Subroutines or callable scripts?

[SimonMeadows]

I have a form with multiple date and time controls, I have a rather complex calculation against other datasources and the form controls values. I would like to write the formula/ calculation once then call it from each of the controls onChanged event.

Is this possible or do I have to copy and paste it into all of the controls onChanged box?

[v-yamao-msft]

Hi SimonMeadows,

I am not sure with your formula, but as long as you configure the formula against the data source and the form controls, when there are changes on the controls, the calculation would be done, you don’t need to trigger the OnChanged event of each controls.

Please feel free reply if you need more help.

Best regards,
Mabel Mao

[SimonMeadows]

Hi Mabel,

I may have been a bit vague with my question...

I will prvide an example.

I have a fairly simple form for calculating overtime.

I have a data structure in the background to compare against:

userTemplateTimes {Start:dateTime, End:dateTime, Day:int(1-7), OvertimeMultiplier:int(1 single, 1.5 time+half, 2 double time)}

The items in this collection are a sequence of timings for an entire week defining when users get specific amounts of overtime

I need to write a formula that does the following procedure (semi code) and displays the totals of each type of overtime.

Split the time range entered in to the form by day and Collect it to give the following result

workingDays{Start:dateTime, End:dateTime, Day:int(1-7)}

First item:{

Start: Start Date and Start Time

End: Start Date + 1 Day and 00:00

Day: Weekday(Start Date)}

Middle items:{

Start: Previous End

End: Previous End + 1 Day

Day: Weekday(Previous End)}

Last Item:{

Start: End Date and 00:00

End: End Date and End Time

Day: Weekday(End Date)}

Once I have this collection I need to do the following:

Define context variables

SingleTime: 0

TimeHalf: 0

Double: 0

then

For each workingDays {

For each userTemplateTimes {

if (workingDays.Day = userTemplateDays.Day){

Calculate the overlap of the time ranges

if there is an overlap add it as hours to the correct context variable based on userTemplateDays.OvertimeMultiplier

}

}

}

Display the result in the form for the user.

I would like this to happen when any of the form controls changes so the total calculated overtime is always accurate and displayed to the user.

I think I need to use OnChanged event of each form control to trigger a repeating timer to iterate through the splitting of the initial time range on the form.

Then have three versions of the calculation, one for each of the overtime types that is based on the nested ForAll of the two collections.

One problem I come across is you cant update a context variable from within a ForAll loop so I cant add to them on each iteration.

This leads me to think I need an intermediate data structure to contain the values of the overtime hours for a later sum calculation.

But if I create a new collection to hold this then have the displayed totals reference it, where does the calculation to poulate that collection go and what triggers it.

If I perform that calculation after the timer has completed as the next step in the form controls' OnChanged property that would probably work but it would mean having to copy and paste the calculation formua to all six form contols in the example.

In my actual app thare will be numerous other controlls that will also need to perform this calculation for things like time in lieu, holiday time etc.

The main question im getting at is, I would like to write this formula once without having to copy and paste it around so that when I need to make changes to it I only have to change it once without having to remember all of the controls to paste it into.

How can I write complex formuae that can be used in multiple places without having to copy and paste when I need to make changes?

[SimonMeadows]

Answering my own question but it may be helpful to others.

I am still working on my formula but this is what I have so far:

Patch(workingHolidayItem,First(workingHolidayItem),
{'Start Date':DateTimeValue(Concatenate(Text(inputStartDate.SelectedDate,"[$-en-US]yyyy-mm-ddT"), Text(inputStartHour.Selected.Value), ":", Text(inputStartMinute.Selected.Value))),
    'Start Hour':Text(inputStartHour.Selected.Value),
    'Start Minute':Text(inputStartMinute.Selected.Value),
    'End Date':DateTimeValue(Concatenate(Text(inputEndDate.SelectedDate,"[$-en-US]yyyy-mm-ddT"), Text(inputEndHour.Selected.Value), ":", Text(inputEndMinute.Selected.Value)),"en-US"),
    'End Hour':Text(inputEndHour.Selected.Value),
    'End Minute':Text(inputEndMinute.Selected.Value),
    'Holiday Days':0,
    Leiu:false,
    'Lieu Hours':0});

Clear(workingDays);

ForAll(Filter(looper,index<=DateDiff(First(workingHolidayItem).'Start Date', First(workingHolidayItem).'End Date')),
    Collect(workingDays, {
        'Start Date': If(index=0,
            First(workingHolidayItem).'Start Date',
            DateTimeValue(Concatenate(Text(DateAdd(First(workingHolidayItem).'Start Date',index,Days),"[$-en-US]yyyy-mm-ddT"),"00:00:00"))),
        'End Date': If(index=DateDiff(First(workingHolidayItem).'Start Date', First(workingHolidayItem).'End Date'),
            First(workingHolidayItem).'End Date',
            DateTimeValue(Concatenate(Text(DateAdd(First(workingHolidayItem).'Start Date',index+1,Days),"[$-en-US]yyyy-mm-ddT"),"00:00:00"))),
        workingDay: Weekday(DateAdd(First(workingHolidayItem).'Start Date',index,Days)),
        index:index
    })
);

Clear(workingSum);

ForAll(workingDays,
 ForAll(workingUserTemplateItemSet,
 If(Day=workingDay,
 Collect(workingSum, {
 daysCount: Max(0,
 DateDiff(
 If(Value(Time(Hour(Start),Minute(Start),00))+Value(First(refDate).Value)>Value(Time(Hour('Start Date'),Minute('Start Date'),00))+Value(First(refDate).Value),
 DateTimeValue(Text(Value(Time(Hour('Start Date'),Minute('Start Date'),00))+Value(First(refDate).Value),"[$-en-US]yyyy-mm-ddThh:mm:ss")),
 DateTimeValue(Text(Value(Time(Hour(Start),Minute(Start),00))+Value(First(refDate).Value)),"yyyy-mm-ddThh:mm:ss")),
 If((If(Value(Time(Hour(End),Minute(End),00))=0,
 Value(Time(Hour(End),Minute(End),00))+Value(First(refNextDate).Value),
 Value(Time(Hour(End),Minute(End),00))+Value(First(refDate).Value)))
 <
 (If(Value(Time(Hour('End Date'),Minute('End Date'),00))=0,
 Value(Time(Hour('End Date'),Minute('End Date'),00))+Value(First(refNextDate).Value),
 Value(Time(Hour('End Date'),Minute('End Date'),00))+Value(First(refDate).Value))),
 DateTimeValue(Text(If(Value(Time(Hour('End Date'),Minute('End Date'),00))=0,
 Value(Time(Hour('End Date'),Minute('End Date'),00))+Value(First(refNextDate).Value),
 Value(Time(Hour('End Date'),Minute('End Date'),00))+Value(First(refDate).Value)),"[$-en-US]yyyy-mm-ddThh:mm:ss")),
 DateTimeValue(Text(If(Value(Time(Hour(End),Minute(End),00))=0,
 Value(Time(Hour(End),Minute(End),00))+Value(First(refNextDate).Value),
 Value(Time(Hour(End),Minute(End),00))+Value(First(refDate).Value)),"[$-en-US]yyyy-mm-ddThh:mm:ss"))
 ),Hours)),
 start1:Value(Time(Hour(Start),Minute(Start),00))+Value(First(refDate).Value),
 start2:Value(Time(Hour('Start Date'),Minute('Start Date'),00))+Value(First(refDate).Value),
 end1:If(Value(Time(Hour(End),Minute(End),00))=0,
 Value(Time(Hour(End),Minute(End),00))+Value(First(refNextDate).Value),
 Value(Time(Hour(End),Minute(End),00))+Value(First(refDate).Value)),
 end2:If(Value(Time(Hour('End Date'),Minute('End Date'),00))=0,
 Value(Time(Hour('End Date'),Minute('End Date'),00))+Value(First(refNextDate).Value),
 Value(Time(Hour('End Date'),Minute('End Date'),00))+Value(First(refDate).Value)),
 workingday:workingDay,
 day:Day,
 start:'Start Date'
 }
 )
 ))
)

UpdateContext({contextVariableName:false})

As you can see this is quite a calculation.

I have a form with multiple date and dropdown controls (9 in total) and I need to run this formula when any of them changes.

It would be copletely unmaintainable if I were to copy and paste this into the onChanged event of every control so this is how I get arround it.

Use a timer...

Create a timer and set:

• Start: contextVariableName
• Duration: 1
• Repeat: false
• Reset: true
• AutoStart: false
• AutoPause: true
• OnStart: the formula above or what ever formula you want to run from multiple places

set the OnChanged event of any controll that shoudl run the formula to:

UpdateContext({contextVariableName:true})

Every time a control is changed the timer triggers the formula.

The end of the formula resets the contextVariableName to false to the timer will run again.

Caveat - This only works in the context of the screen in which the timer resides.

If you need to use the formula on multiple screens you'll need multiple timers, one for each screen.

It would be nice to have a function control where you could write a function and then call it from other controls anywhere in the app but untill then this works quite well and stops you haveing to copy and paste the same formulae to mutiple controls.