syntax problems

Hi Jarno

We can help! Could you show me a screen shot of the table structure of your three tables. Are they joined together with Reference fields?

Many thanks

John

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.

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.

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.