Creating a function for a data directive

[mto29 (Mic)]

Hi All,

Hope you’re doing well.

Off the back of a topic I raised about creating a customisation that would populate a field on Job Entry, using criteria from a field in PartPlant, this is now working great for us.

Directive to populate UD field based on criteria

Hi all, We have created a UD date field within JobHead which we are looking to use an in-transaction data directive to populate the field with todays date minus the production prep buffer days stored against the Part [image] I’ve created a Set Field rule, and can see there is a ‘AddInterval()’ function, but struggling to see how I’d then bring in the Part.ProductionPrepBuffer to this expression when the only available data within this directive is from ttJobHead, as expected. [image] I wa…

Using a data directive on JobHead, bringing in an Update Table by query, joining in Part Plant and setting the calculation on the JobHead.BufferDate_c we created.

ttJobHeadRow.PlannedKitDate?.AddDays(-queryRow.PartPlant_PrepTime)

However… we now need to make this look at working days (Mon-Fri) rather than calendar days. As far as I can tell, there’s no way to add a function to the same expression as it errors saying ‘Expression should contain only one statement’.

Does anyone know any ways we could create a WorkDays function that sits in front of this Update Table by query block or do we maybe need to rethink this whole method?

Really appreciate the tips/advice as always!!

[cory.davis]

ChatGPT gave me this, said to adjust the ‘30’ if you expect PrepTime to be longer than that. I did not test this.

ttJobHeadRow.PlannedKitDate?.AddDays(
    -Enumerable.Range(1, 30)
        .Select(i => ttJobHeadRow.PlannedKitDate.Value.AddDays(-i))
        .Where(d => d.DayOfWeek != DayOfWeek.Saturday && d.DayOfWeek != DayOfWeek.Sunday)
        .Take(queryRow.PartPlant_PrepTime)
        .Count()
)

[mto29 (Mic)]

Thanks Cory!! It’s not quite using the weekend exclusion yet, (Prep Time = 10 days) yet

but it accepted the Syntax so gives a base to work off, thanks so much

[mto29 (Mic)]

I’m just thinking if we need to switch this to a pre-processing method to give us more functionality, maybe using the ‘Invoke Function’ to work out weekdays before we set the ‘Update Table by Query’ do you know how we might be able to use this please?

I’ve checked in Dev tools/debugger and it doesn’t need to be a data directive, we are only to looking to update JobHead.BufferDate_c based on if the JobHead.PlannedKitDate has been changed.

[cory.davis]

Try this tweaked expression

ttJobHeadRow.PlannedKitDate.HasValue 
    ? (DateTime?)Enumerable.Range(1, 30)
        .Select(i => ttJobHeadRow.PlannedKitDate.Value.AddDays(-i))
        .Where(d => d.DayOfWeek != DayOfWeek.Saturday && d.DayOfWeek != DayOfWeek.Sunday)
        .Skip(queryRow.PartPlant_PrepTime - 1)
        .FirstOrDefault()
    : null