Need Help to create a History Control Table

Do you have sample of the code you have created already?

Can you try creating the code in a button that creates a new record in the control table and copies the correct data? That would be a good start.

When that works then we can move on to the next step.

Since the date fields are all in one record it took me some time to figure out how to check them. So I came up with this button code that goes in the main table (I think Documentos for you. It is called Table4 for me.)

let t := this;
let F3 := if FechaR3 <= currentDate then
        1
    end;
let F4 := if FechaR4 <= currentDate then
        2
    end;
let F5 := if FechaR5 <= currentDate then
        4
    end;
let addAll := F3 + F4 + F5;
let newArray := switch addAll do
    case 1:
        [F3]
    case 3:
        [F3, F4]
    case 7:
        [F3, F4, F5]
    end;
for loop1 in newArray do
    let fDate := switch loop1 do
        case 1:
            FechaR3
        case 2:
            FechaR4
        case 4:
            FechaR5
        end;
    if count(months[Date = fDate]) < 1 then
        let newRec := (create months);
        newRec.(
            Date := fDate;
            Table4 := t
        )
    end
end

Lines 2 - 10 creates three new variables (F3,F4,F5) and checks to see if the date in the Fecha date fields are less than or equal to a date in a field called currentDate. You can change this to today() if that works better for you. I used a date field so I can control the date that triggers the following code. I use different values for each fecha so I can track which ones needed.

Line 11 creates a new variable called addAll and adds all of the three above variables to get a number.

Lines 12 - 19 then creates a new variable called newArray then uses a switch command based on the value of addAll to create an array of the correct dates.

Line 20 - 36 is the loop command to create the appropriate records in a child table called months. Of course change it to match yours.

Lines 21 - 28 creates a new variable called fDate and depending on the value in loop1 it will pull the appropriate fecha date value. As you can see this is where using distinct numbers in the first variables come in handy.

Line 29 is an IF statement to do some error checking. We don't want to create duplicate records in the months table, so it checks to make sure that the date from any earlier fecha hasn't already been created by searching for any records in the months table where Date equals to fDate. If that count is less than 1 it will then run the code to create a new record. If it is not less than 1 then nothing happens.

I hope this helps. It was an interesting request.

Rafael said:
I have Problem on line 29 (Field not found months at line 29 column 19

 Sounds like CONTROL_TABLE is not linked to Documentos, so then you have to use a select command.

Rafael said:
I have Reference form CONTROL_TABLE to DOCUMENTOS 1:N

Ok, then the next issue is the direction. It sounds like you created the link in Documentos to CONTROL_TABLE, 1 record in CONTROL_TABLE is linked to many records in Documentos. You can tell this by which table has a view table of the other. Or which table only allows you to select a single record from the other table.

I think it should be 1:N Documentos to CONTROL_TABLE. Which means 1 record in Documentos is related to many records in CONTROL_TABLE. Which is what you said you want.

Rafael said:
ok for now in the screenshot you saw two Real Dates ready FechaR3 3Aug and FechaR4 20Aug, on _CONTROL_PANEL saw the two Dates but need the Document Name and Code

Once you have the link to the record in Documentos you don't need to copy the name (unless you want the ability to change it for a record in CONTROL_TABLE but not change it for Documentos). Just use the relationship to get the info that you need.

You can create a formula field in CONTROL_TABLE and put:

Documentos.Documento

And it will show you the info you need. No need to duplicate data and no need to add lines to your code.

Try to think in relationships and it will save you lots of time and energy.

Rafael said:
The scrip you send me works great very useful, but there are options to generate all documents one one click on the button, no one by one ? 

 You would have to use the for loop command. Have you tried writing the code yet? Do you want to try to do it first and then we can troubleshoot it? Or do you want me to just post a possible final solution?

Good start. Thinking it over you have to put the new loop at the very top since you want all of the current code to run for each record in Documentos. It took me awhile to figure it all out so here is what I came up with. You can put this in a new button so you can still have the code for the other one as well.

for loop2 in (select Table4)[Edit] do
    let F3 := if loop2.FechaR3 <= loop2.currentDate then
            1
        end;
    let F4 := if loop2.FechaR4 <= loop2.currentDate then
            2
        end;
    let F5 := if loop2.FechaR5 <= loop2.currentDate then
            4
        end;
    let addAll := F3 + F4 + F5;
    let newArray := switch addAll do
        case 1:
            [F3]
        case 3:
            [F3, F4]
        case 7:
            [F3, F4, F5]
        end;
    for loop1 in newArray do
        let fDate := switch loop1 do
            case 1:
                loop2.FechaR3
            case 2:
                loop2.FechaR4
            case 4:
                loop2.FechaR5
            end;
        if count(loop2.months[Date = fDate]) < 1 then
            let newRec := (create months);
            newRec.(
                Date := fDate;
                Table4 := loop2
            )
        end
    end
end

I know it is weird to have loop2 before loop1, but I left it this way so you can see the difference between the two versions.

Again you will have to replace Table4 with Documentos and currentdate with whatever you are using.

So we first start creating the loop (called loop2), where we select all records from Table4. Here I limited the selection to only 3 records where Edit is true. You may want to do something similar as you test it out so you don't modify your whole table while testing.

Then you can see where I had to add reference to loop2 to get the correct data to then put into the new records that are created during loop2.