0

Date alert

Hi all, a few years ago, Fred kindly supplied me with an answer to a date alert question but now I need to adapt the solution.

I have tried searching the forum and support files for an answer so apologies in advance if I missed something.

Simply put; I want my users to be alerted when they select a date on a record that will be a public holiday or weekend. To that end I have created a separate table that contains specific Holiday dates with a choice field indicating the holiday type.

Using Fred's guidance, I have been successful in providing an alert that the date is marked as a public holiday but I want to be more specific and indicate which type of holiday this is; we have an office in the UK and one in Germany so they need to see which office will be closed. 

My current formula is:

let curRec := this;
let xDate := cnt(select Holidays where Date = curRec.Date);
if xDate > 0 then "public holiday" end

I'm just not sure on the next layer to add that would also look at the choice field on the Holidays table to return a more specific result i.e. German public holiday, UK public holiday, Saturday etc,

I have tried with a switch function but it returns no data, I assume I am missing a function somewhere to call the correct data.

Any advice is greatly appreciated

3 replies

null
    • Fred
    • 4 days ago
    • Reported - view

    With a minor change you will have access to the Holiday record so you can pull any data you need.

    let curRec := this;
let holidayRec := first(select Holidays where Date = curRec.Date);
if cnt(holidayRec) > 0 then holidayRec.Name end

    Line 2: we rename the variable to be more meaningful, remove the cnt() command and replace it with the first() command which allows us to get the record Id. Whenever you do a select, Ninox returns an array, so the first() command is used to make sure only 1 record is returned.

    We then move the cnt() command to line 3 and in the then part we can use the new variable name to access fields. I just used Name but you will replace it with the appropriate field name.

    • Rob
    • 3 days ago
    • Reported - view

    Hi Fred, once again you have given me exactly the solution I was looking for. Can't thank you enough for this. This function will allow me to updated several different tables and save a whole lot of work moving forward. Again, thank you

      • Fred
      • 3 days ago
      • Reported - view

       As I rule I always try to have a variable that stores the records I need to use so I can reference the data in them when I need to.

Login to reply

Content aside

  • 3 days agoLast active
  • 3Replies
  • 39Views
  • 2 Following