Time Offset Flag didn't re-evaluate when date threshold was passed

Hi Adrian,

I tried this locally and the future-dated change was correctly created, and was processed automatically at the correct time. There is no future-dated change in your database for this entity, so it has either already been processed and removed, or was lost. If you generate changes and the value updates correctly then it probably wasn't processed.

Would a Clear Adapter Changes have been run between the entity was updated and when the transformation should have been re-evaluated? This operation removes all changes for an adapter, including future-dated ones. Also, Generate Changes does not have the ability to regenerate future-dated changes, only to populate the adapter with entities from the connector.

Thanks Beau, I see.  That's very useful to know, thank you.  Clear Adapter Changes may indeed be the cause here (and elsewhere).

Is there any other way to regenerate the future dated changes other than to Clear Pre-Calculated Entities and then run Generate Changes?

Created https://voice.unifysolutions.net/en/communities/6/topics/4239-add-ability-to-recreate-deleted-future-dated-adapter-changes-time-offset-flag-etc following on from this.

Is there any other way to regenerate the future dated changes other than to Clear Pre-Calculated Entities and then run Generate Changes?

No, the only time future dated changes are generated are on an import with meaning full differences, ie a new entity that doesn't exist yet, or an existing entity with one or more updated fields (any field, not limited to the ones involved in the transformations).

Here's a second example that appears to be the same problem.

Actually no, the change is present in the database, but hasn't been processed and removed. There's actually number of unprocessed changes pending for that adapter. Can you confirm whether incoming changes from the connector are making their way into the adapter?

It looks like changes may not be going through at all.

I changed entity eecce27d-ff1d-4577-ba6d-5b29946e0d33's PreferredName from "Skippy" to "Stefan" and it hasn't reflected from the connector to the adapter.

Do you want me to hold off on clearing pre-calculated entities and generating changes? I was about to do that to restore the missing future-dated transform changes.

Never mind, that's because the adapter is disabled.  I'll re-enable it and see what happens.

This "second example" has gone away now that the adapter is enabled again.  I disabled it while investigating the original issue.

Cleared precalculated entities and generated changes.  Data for the problematical users is now correct.  UNIFY testers will re-run the overnight test cases that failed originally.

The EndTimestampUTC field for that entity is null, meaning the offset flag value is correct. Can you confirm that you provided the correct entity id, and that the test entities were added as expected?

Hi Beau,

This problem is affecting more than one Time Offset Flag transform, and the problem in this case is with the transform called "PreStart".  Here is it's configuration:

Here is the relevant entity data for that user (i.e. StartTimestampUTC and PreStart):

When StartTimestampUTC became less than the current date/time in UTC (i.e. midnight NZ time this morning) the PreStart attribute should have been set to "No", but it wasn't.

Sorry, missed this was for PreStart, not PostEndPlus14. The issue is that StartTimestampUTC and EndTimestampUTC are generated by a PowerShell transformation. PowerShell transformations break the chain of tracking which fields are dependent on other field, which also breaks the ability to create future-dated changes. I take it you can't not use the PowerShell transform due to requiring the Date -> Timestamp conversion, correct? I'll take a look at the transformations and see if anything can be done to support this.

Yes, that's correct.  StartTimestampUTC is indeed a field created by a PowerShell transform.  What if I used a Copy transformation to copy the field's value into a new (non-PowerShell transform) field?

Won't make a difference. The copy transform will associate the new field back to the one created by the PowerShell transform, but that still won't have any association with a connector field.

Hi Adrian

A patch has been deployed adds the ability to manually register which existing fields contribute to fields created by PowerShell transformations. This is done by adding one or more calls of the Register-Contribution function to the Schema Script, following the New-Field calls.

The signature of Register-Contributionis:

Register-Contribution -fieldName <string> -contributingField <string>

where fieldName is the name of the new field created with New-Field, and contributingField is the name of a pre-existing field that contributes to the new field in some way. This can be called multiple times for new fields that are contributed to from multiple pre-existing fields.

For example:

New-Field 'convertedValue' 'int';
New-Field 'joinedTS' 'timestamp';

Register-Contribution 'convertedValue' 'origValue';
Register-Contribution 'joinedTS' 'date';
Register-Contribution 'joinedTS' 'time';

Note that for most, normal usage of the PowerShell conenctor it won't be necessary to register field contributions. Time Offset Flag and Business Day Offset Flag transformations which use generated fields are the main reason why this would be needed.

That's awesome!

Unfortunately this did not work.  At Matt's request I have created a MVP to demonstrate the bug:

1. Configure UNIFYBroker on a server which is in the UTC timezone.

2. Create this CSV file, replacing "2021-05-14" with today's date.

Date
2021-05-14

3. Create this CSV connector.  Note that the "Date" field is type String.  DO NOT run Import All yet.

4. Create this adapter.  Edit the PowerShell transform to enter a UTC time ($HoursToday, $MinutesToday) that is a few minutes in the future from now, in UTC.

5. Run an Import All on the connector.

6. Confirm that the adapter has been populated similarly to the following example - with the TimeOffsetFlag set to "Greater Than".  Note that the Timestamp is currently in the future, so the TimeOffsetFlag attribute value has been set to "Greater Than" since it is greater than the current date/time.

7. Wait until the time you specified in step 3 above has passed (remember that it is a UTC time).

8. Check the adapter entities again.  Since the date/time specified in the Timestamp field has passed, the TimeOffsetFlag attribute value should have updated to "Less Than", but it has not.

9. Run Generate Changes on the adapter.  The TimeOffsetFlag attribute value will have now been updated to the correct value, "Less Than".

Hi Adrian

This issue with the debug reproduction is that the connector field is a string. It has to be a Date or Timestamp. Another that you may have experience was an issue with the patch I provided not taking the changes made in the PowerShell script into consideration. A new patch has been deployed now which attempts to correct this, but has some shortfalls which you'll need to so some reconfiguration to avoid.

Basically, transformations with Register-Contribution will now be executed many additional times during change detection on partial entities, where the fields and values that may normally be expected to be there won't be present. To account for this:

• Only one existing field can contribute to one output field. My example above where two Register-Contribution was made for one output field will either produce invalid futured-dated change times or crash change detection, depending on the script.
• That is because scripts must not assume any field to be present. You're scripts already null check the appropriate fields so all good there.
• Following on from that, you should move the timestamp conversion code into separate PowerShell transformations, with one transformation per field. These transformation should be focused on the conversion. Avoid any other unrelated logic, or field changes.
• No calls to external services or resources that would either slow down execution or produce some external side effect, as these could occur many times during every import. Don't think you have anything like that though.

This isn't an ideal solution, it's still in need of refinement, but should be suitable for your needs here. If you have any trouble setting this up feel free to give me a call on Teams.

Also, to speed up testing, if you let me know after the test entities have been imported, but before the time when future-dated change should be processed, I can check the database for you and let you know if they were created and when they're scheduled for, rather than having to wait until the expected time.

Hi Beau,

Thanks for your response.

I repeated my test above, after making the two changes you advised:

1. Change the connector's 'Date' field to type 'Date' rather than 'String'
2. PowerShell transform to ONLY perform the calculation of 'Timestamp', based solely on the 'Date' field.

I also ensured that all other conditions outlined in the comment were met.

When I did this I the test still failed in the same manner.  Could you or Matt please look at this again?  Same environment as above, same connector/adapter.  If you would like me to demonstrate the problem happening then please let me know.

Update: spent some time with Matt tonight and the key learning is that I missed this requirement:

"scripts must not assume any field to be present"

I assumed that since the field was known to have a value in my test case, this wouldn't be a requirement.  But it is, because transforms can be passed partial entities (with field values missing) when they are evaluated during future-dated changes.  In particular, Matt demonstrated that the PS transform is called three times when the future-dated change triggers, and at least one of those times the source field has a NULL value.

In my MVP test case the PowerShell transform is throwing an error (which is recorded in the Broker log) when this happens and the entity is consequently not being updated.

I can see one issue here - for a field where NULL is a valid value we have no way to distinguish between the "partial entity, don't re-evaluate the target attribute value" case and the "NULL value, do something specific" case.  But that should only be an issue where a PowerShell transform of a NULL date needs to set up a specific future-dated change, and I can't think of a use case for that.  But it is important to note that a PowerShell transform must not do anything like this if it also contains transforms that register future-dated changes:

if ($Entity["Date"].Value -eq $Null) { $Entity["ProcessedDate"] = [DateTime]"9999-12-31" }

This will cause problems, as ProcessedDate will be set to an incorrect value when the transform is called with a partial entity!

Anyway, I now need to rework the customer configuration to incorporate this change, in order to confirm that it works correctly there and not just in the MVP test case.

This was fixed under another voice ticket; unfortunately I can't find it :(