I have a project where I have a table with some dates (bankholidays).
I have another table (appointments) where I need to check that the date of the appointment is not present in the bankholidays table.
Who can help me out ?
Define a relationship between the two tables, matching on the two date fields. Then you can check if a related record exists. What exactly do you want to happen if it does?
Another option without the need for a relationships would be to use an ExecuteSQL function in your flag field of script step
not isEmpty (
ExecuteSQL ( "SELECT ''||Date FROM BANKHOLIDAYS WHERE Date = ?" ; "" ; "" ; APPOINTMENT::Date )
Will return a value of 1 if there is a matching date in your BANKHOLDAYS table
I hope this helps
Hi Michael,Thanks for your reply.If the entered appointment-date is on a bankholiday, another field (TarifJourFerie) should be filled with a value "Yes".
Not sure having a field with a value of "Yes" is that useful. But if you want, you could use =
If ( BankHolidays::Datefield ; "Yes" )
Hello again guys,I can't get it to work, not with the SQL query, nor with the relationship...
The relationship operator should be the = operator, not the “does not equal” operator shown. The calculation field will also need to specify Appointments 2 as its context table occurrence.
Why are you using a separate TO of Appointments to define the relationship? This will only make things more difficult for you. You won't be able to do even simple things - like placing the Name field from the BankHolidays table on the layout of Appointments, to show on which holiday the appointment falls, for example.
Retrieving data ...