Update a custom field on all Entities with same expiry date

[RickH]

So we have customer entities with a membership expiry date field and a membership block field.

Manually updating the block is fine and automation works to check the block field for either YES or NO and block customer entity loading to ticket. What I want to do is automated the update of the Block field to YES when the current date matches the expiry date in the custom field of the customer entity.

So maybe on work period started, any customers who have an expiry date of “today” will have their block custom field update to YES automatically. Then when they are attempted to be added to the ticket it wont allow it and a message appear saying membership has expired

Ive done everything and everything else works, i just need a way to each date check for the expiry date on the customers entity and then update the block to YES when expiry date and current date match

is there an easy way to do this using samba? maybe something using the loop action?? i dunno ive never used it before or had to do check and update multiple entities custom fields at the same time

any help would be great

[RickH]

actually thinking about it, it doesnt have to check all entities on the date, to make it simpler i can do it on adding the entity to the ticket instead !

[Memo]

I think you’d have to touch every entity record, read and parse the CustomData JSON, make your changes, and update the table, no?

[JTRTech]

Script definalty easily do that.
There are helpers for entity data handling.
Use a report call to get lost of entity names and loop through them, job done.

[RickH]

This is where im coming stuck with the right formula, ive tried using examples in other part of my system but i cant get something right.

Basically i want the action in this rule to fire if “todays date” is AFTER the Customer Membership Expiry Date

this is the format of the date in the entity custom field

[JTRTech]

Dates suck lol.
One method I seem to often fallback on is the timestamp number (the milliseconds since whatever date they decided to make start).
It gives for reliable comparason of dates.

[Memo]

Dates in javascript suck.

Anyhoo, here’s what I have:

Script Name: Membership Check
Handler: membership

function check(currentDate, expiryDateRaw)
{
    var expiryDateSplit = expiryDateRaw.split("/");
    var day = expiryDateSplit[0];
    var month = expiryDateSplit[1];
    var year = expiryDateSplit[2];
    var expiryDate = year + "-" + month + "-" + day + "T00:00:00";

    if (expiryDate >= currentDate)
    {
      return 1;
    }
    else
    {
      return 0;
    }
}

I couldn’t get [=FD(ADM('{ENTITY DATA:Customers:Membership Expiry}'), 'yyyy-MM-ddT00:00:00')] to pass to the script for some reason, so the membership expiration date is formed in the script.

If the membership expiry date is today or later (valid) the script returns 1. If the expiration date is before today (invalid) the script returns 0.

[=TN('{CALL:membership.check('{DATE:yyyy-MM-ddT00:00:00}','{ENTITY DATA:Customers:Membership Expiry}')}')] [EQUALS] 1

EDIT:
This assumes the date I see in your entity is D/M/Y

[RickH]

Hi thanks for the help, i had to change the script to be:

(currentDate >= expiryDate)

instead of
(expiryDate >= currentDate)

as the membership will expire when the current date is greater than the membership expiry date. if the expiry date is greater than the current date then the membership is valid

however its not working, what format does the date need to be in the entity, as it doesnt matter the format you display it in in the entity, the output on the screen is 8 digits with no spaces e.g 04072020, it doesnt matter what mask you use in the entity setting, on screen it will show as 8 digits no spaces

[RickH]

so here ive set the Memberhip Expiry Field as Date with format ##/##/####

this format displays in the entity info as per the setup ##/##/####

however on screen in samba it uses it as 8 digits no spaces

[Memo]

@RickH

(currentDate >= expiryDate)

instead of
(expiryDate >= currentDate)

If the membership expires tomorrow then the expiration date is greater than today. If it expired yesterday then the expiration date would be less than today. If it expired today the expiration date would equal today. I think if (expiryDate >= currentDate) as it is will work. But test it and see.

So, you’re entering in an 8-digit integer that is being masked for display. I was testing without a mask. Let me make some changes brb

[Memo]

If you will always have 8-digits, then this should work (the other will work if you enter the date like this 04/07/2020 or 4/7/2020 ):

function check(currentDate, expiryDateRaw)
{
    var day = expiryDateRaw.substr(0,2);
    var month = expiryDateRaw.substr(2,2);
    var year = expiryDateRaw.substr(4,4);
    var expiryDate = year + "-" + month + "-" + day + "T00:00:00";

    if (expiryDate >= currentDate)
    {
      return 1;
    }
    else
    {
      return 0;
    }
}

If you want more fine-grained results:

    if (expiryDate > currentDate)
    {
      return 1;
    }
    else if (expiryDate == currentDate)
    {
      return 0;
    }
    else
    {
      return -1;
    }

This will allow you to evaluate if the expiration date is before today, today, or after today.

[RickH]

I think were thinking about it wrong, we need to be checking the current date against the expiry not expiry against current date. Expiry dates will always be set to the future so will always be greater and that membership is valid

What i need to know is when the expiry date has passed, so when the current date is AFTER the expiry date, so

expiry 03 07 2020
Current date 04 07 2020

current date is AFTER expiry therefore membership needs blocking until renewed

so rather than greater or smaller, which i dont think works properly with dates we need BEFORE and AFTER, but in my case i need AFTER

is it possible for your script to be amended to that it is CURRENT date AFTER expiry date?

[RickH]

you new script works, i just needed to swap (expiryDate >= currentDate) around so that current date is greater than expiry date, as membership expires when current date passes (get greater) than membership expiry date

Thanks for you help

[RickH]

ive now slightly changed the setup and use membership number as the primary field and deleted an unused custom field, none of those have anything to do with the expiry field but since i made those changed the script isnt working, on checking rule debugger its not even firing the rule on ticket entity changed

at least i know it works (and all backed up) so just need to figure out why this rule isnt firing now

[Bob_be]

I don’t know if this will fix the field removed problem. But in your original attempt to constrain the rule (nothing to do with Memo’s script), I noticed you used ADM. ADM is used Add Month(s). However, you used it on both sides, so 2 wrongs will make a right.

Here is what ADM looks like:

Here is what used for a show message action:
Date: {DATE:dd-MM-yyyy}\rEntity: [=FD('{ENTITY DATA:Customers:Membership Expiry}','dd-MM-yyyy')]\rADM: [=FD(ADM('{ENTITY DATA:Customers:Membership Expiry}'),'dd-MM-yyyy')]

Hope this helps in the future.

[RickH]

thanks, i have ADM in another part of my setup that Emre helped me with (I didnt know what it meant lol) but was a similar setup so was trying to work it out lol

[Bob_be]

Here is one of the topics I found that explains it:

Age Verification or 18+ reminder with calendar

Sure. Use this formula: [=FD(ADM('{TICKET DATE}',-216),'dd/MM/yyyy')] ADM function adds Months, so 18 years x 12 months = 216 months. In this case I used -216 (negative) to subtract months from current ticket date. This is 1 solution that could be modified to suit your needs. [image] [image] Ensure you add a Default mapping to the following Rule: [image] MessageToDisplay: Customer must be Born on or before this date: [=FD(ADM('{TICKET DATE}',-216),'yyyy-MMM-dd')] [image]

[RickH]

looks like one of the responses to the setup i was working on lol, forgot all about that description

[RickH]

@Memo

so this is what i dont get, the system works perfect, so when the expired entity it attempted to be loaded to the ticket and is blocked the script works and the rule is fired, once customer pays their membership we press the button on the pos which asks for the customer name and new expiry date, so we type those in and the data updates fine and the block is lifted

perfect!! Almost lol

I now want to change the primary field from customer name to membership number, when i do this the script no longer seems to work and that rule doesnt fire and i cant work out why as nothing to do with the dates or rules for the date checking has been changed so now i have this

Add Member with number 123456

the expiry date is 01012001 so should not load and the warning message should appear

any ideas why changing the primary field around has stopped it working?

new entity data

[Memo]

I haven’t the foggiest. Does {ENTITY DATA:Customers:Membership Expiry} still return the data you expect?

Post your entity type setup for Customers with custom fields and I’ll play around and see if I can figure something out.