I'm hoping that the power of the community could help me out, I've found myself stuck and cannot find the solution to a problem. First some background information: as it goes with employees, their wage rises as time progresses. In order to keep track of this, I've created a table called 'Loonhistoriek' which has a N:1 relationship with the Table 'Medewerkers'. In Loonhistoriek I have four fields; the Employee's name (Medewerker), the starting date, the end date and the wage (number field called 'Uurloonkost'). In an ideal situation, the end date would be automatically calculated as soon as there is a new starting date but that is something I can't get done.
The second and more pressing problem I encounter is that I want to know what the Employee's wage is in a third table called 'Prestaties'. In that table I want to select the wage field (number field) that corresponds with the date on which the Employee worked. Keep in mind that any employee will have multiple entries in the Loonhistoriek table over time. I have the following syntax but it is not working:
let M := Medewerkers.Medewerker; (this is a reference to the table 'Prestaties')
let x := Datum; (This date is entered in the table 'Prestaties')
let SD := (select Loonhistoriek).Begindatum;
let ED := (select Loonhistoriek).Einddatum;
if SD <= x and x >= ED then
(select Loonhistoriek where M = Medewerkers.Medewerker).Uurloonkost
could somebody explain what I'm doing wrong and what I would have to do to solve these puzzles? Thank you in advance!
Your first question is pretty simple. If you put the following in the Trigger after Update of the Begindatum field:
Einddatum := Begindatum + 365
So after you enter in data in the Begindatum field and click out or to another field it will then update the Einddatum field to be 365 days after the Begindatum date. Of course you can modify it to match your needs.
If you need to be more flexible then you will need another field that you will enter the number of days after the Begindatum you need to set or if you have a small set of choices (90 days, 1/2 year, 1 year) you can create a choice field. Then you just need to change 365 to the modifying field name.
Ok, if John is correct in how you want to enter the Einddatum of the previous record when a user enters in the Begindatum of the current record, you can try something like this in a button and if it works like you expect then you can put it in the Trigger after Update of Begindatum.
let t := this; let x := Medewerkers.Loonhistoriek[Einddatum = null]; let cX := item(x, 0); if Begindatum != null and cX != t then cX.(Einddatum := t.Begindatum - 1) end
Line 1 gets the data of the current record you are on.
Line 2 gathers all of the records in Loonhistoriek of the current Medewerker you are looking at. Then it finds only records where the Einddatum is empty (null). At this point it should find two records. You could do a select statement but this way is much faster even though it seems weird for Ninox to go out through Medewerkers and back to Loonhistoriek. This way Ninox only finds the records of the current Medewerker without having to filter out a select.
Assuming that there are only two records, the current record and the one previous, line 3 get the data from the first record. The item command takes an array and allows you to specify which item in the array you want. Since everything starts with 0, we want the first item in the array x.
The if statement that starts at line 4 does a check to see if the Begindatum is not empty (no need to do anything if the field is empty) and that the record that is gathered is not equal to the current record (just to make sure that nothing happens if only the current record is found), if both conditions match then it will set the Einddatum of the previous record to be one day before the Begindatum of the current record.
wow, that does do the trick indeed! I tried the button, which worked - I now pasted the the formula into the Trigger after update and it also works its wonder. Lovely! Thank you very much indeed!
That solves part 1, great! Much appreciated!
John Halls I'm adding an image of the data model below, hopefully this could help you in solving that puzzle too?
As I stumbled through figuring out a solution I got stuck trying to figure out how to deal with the records in Loonhistoriek that have no Einddatum. The solution to your first question means that there will always be records in Loonhistoriek that have no Einddatum.
So my solution is to create a dynamic choice field that displays the Begin and Eind datum for each loonhistoriek for the selected worker.
So create a new dynamic choice field, you can call it werkdatums and put this in the Dynamic values:
Medewerkers.Loonhistoriek order by Begindatum
Then put this in the Dynamic value names:
Begindatum + " - " + Einddatum
You should see something like this (excuse the English field name):
Then you can change the formula for the wage field to:
let loonH := record(Loonhistoriek,number(werkdatums)); Medewerkers.Loonhistoriek[= loonH].Uurloonkost
The first line creates a variable (loonH) and since we have a dynamic choice field we have actually selected a record from the Loonhistoriek table. So we have to use the record command to get the data from the record that we selected in the werkdatums field.
Then we use the power of relationships to find the record from Loonhistoriek selected in werksdatums and then retrieve the value for Uurloonkost.
I kinda like this method better as you quickly see the relevant work dates for the selected worker. You don't need to guess a date to see if they worked in that period.
I hope this helps. If you want to continue using a simple date field then I can spend some more time trying to figure out a way around the blank Einddatum field for the most recent record for each worker. I'm sure smarter people than me have already figured it out and can post it.
Thank you for the help - I've tried what you said but something is not working. In the table Loonhistoriek I created the dynamic choice field and I get the same result as in your print-screen (apart from the date format but that is because of the properties of my Mac, I presume).
I then copied and past the formula into the wage field in the table 'Prestaties' but I don't get any results. Any idea as to what the reason could be? Does the dynamic choice field in the table Loonhistoriek have to be selected by the user or not? I tried it when I manually selected the different 'numbers' of the dynamic choice field but unfortunately got no result.
Yes, that would make more sense. Indeed; this works! However, is there a way in which the dynamic field is automatically populated by the "date" field in prestaties? The idea is to have our staff fill out a form on a third-party platform so that all the necessary calculations can happen automatically based on their input.
There is no need for the dynamic field if you are passing along only a single date. So it is back to the drawing board and this is one solution. On the Prestaties table, in the wage field or in another formula field you can put this in the formula:
let t := this; let datumcheck := if count(Medewerkers.Loonhistoriek[Einddatum = null and Begindatum <= t.Datum]) = 1 then 1 else 0 end; switch datumcheck do case 0: Medewerkers.Loonhistoriek[Begindatum <= t.Datum and Einddatum >= t.Datum].Uurloonkost case 1: Medewerkers.Loonhistoriek[Begindatum <= t.Datum and Einddatum = null].Uurloonkost end
Lines 2 - 6, assigns the results of an if statement into a variable called datumcheck. The if statement counts the number of records for which there is a null value in Einddatum and Begindatum value that is less than the Datum field (which should be the most recent record of a user that still has an open contract). If the count = 1 then it will assign a value of 1 to the variable. If the count doesn't equal 1 then it puts a 0.
There is not a lot of error checking. I am assuming the data is clean and there will never be two records with an Einddatum that is null.
Lines 7 - 12 is the switch statement that uses the two values in the datumcheck. Case 0 will find the record where the Datum value sits between the Begindatum and Einddatum and return the Uurloonkost of that record. Case 1 will find the record where the Begindatum is less than or equal to the Datum value and where Einddatum is null and then return the Uurloonkost value of that record.
It doesn't tell you if the datum value is out of the range, so you could see empty Uurloonkost values.