Adapt fórmula to new Structure.

Fred
1 yr agoMon, April 24, 2023 at 4:03 PM UTC
Reported - view

I don't know where this code is supposed to go in your DB.

Looking over the code and your DB I have the following questions:

let check := dialog(" Warning ", " ¿Confirm the Update Transmittal ? ", ["Yes", "No"]);
if check = "Yes" then
    for loop2 in (select 'wbs by Documents')['Name Document'] do
        let F3 := if loop2.Real_3 <= loop2.today() then
                1
            end;
        let F4 := if loop2.Real_4 <= loop2.today() then
                2
            end;
        let F5 := if loop2.Real_5 <= loop2.today() 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;

Lines 4,7,10, in 'wbs by Documents' there are no fields Real_3, _4, _5. So you need to replace the field names with appropriate new field names. They also have loop2.today(). I don't think you need the loop2 part since today() is not a field name.

One issue I see is that you now have 5 Real data fields, so if you need to account for all 5 that will greatly expand the previous and following section of code:

 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.Real_3
                case 2:
                    loop2.Real_4
                case 4:
                    loop2.Real_5
                end;

It seems like you have two different ideas trying to use the same bit of code:

for loop2 in (select 'wbs by Documents')['Name Document'] do
        let F3 := if loop2.Real_3 <= loop2.today() then
                1
            end;
        let F4 := if loop2.Real_4 <= loop2.today() then
                2
            end;
        let F5 := if loop2.Real_5 <= loop2.today() 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.Real_3
                case 2:
                    loop2.Real_4
                case 4:
                    loop2.Real_5
                end;

The variable newArray creates an array telling you which fields has data in them.

Then in line 20 you start a new for loop using newArray, which at this point has an array of data, i.e. F3 or F3,F4, or F3,F4,F5. So the switch command at line 21 won't work since it is looking for the number 1 or 2 or 4.

In addition you can't put human field names into a variable and then use it later to reference the real field name like you try to do in Lines 23, 25, 27.

- Rafael Sanchis
- Rafael_Sanchis
- 1 yr agoMon, April 24, 2023 at 4:19 PM UTC
- Reported - view
Fred Hi Fred.

Lines 4,7,10, in 'wbs by Documents' there are no fields Real_3, _4, _5.

No this fields are now on 'dates by Documents' table 'Real 3' ,'Real 4' and 'Real 5'

This code work OK on 'wbs by Documents with all dates ( Plan and Real) and the button on the same table.

Now I like have the Documents on one table and a Dates on Subtable.

And need copy the 'Real 3', 'Real 4' and 'Real 5' on 'Control Table '

https://forum.ninox.com/t/h7hnwwm

The script is yours and work fine but when all information in a single table.
- Fred
- 1 yr agoMon, April 24, 2023 at 4:42 PM UTC
- Reported - view
Rafael wow, looking at it now it looks like it shouldn't work. :)

can you send me that DB again?
- Fred
- 1 yr agoMon, April 24, 2023 at 8:00 PM UTC
- Reported - view
Rafael Wow, I guess I was smarter back then.

Rafael Sanchis
Rafael_Sanchis
1 yr agoMon, April 24, 2023 at 4:29 PM UTC
Reported - view

let check := dialog(" Warning ", " ¿Confirm the Update Transmittal ? ", ["Yes", "No"]);
if check = "Yes" then
    for loop2 in (select 'dates by Documents')['Document'] do
        let F3 := if loop2.'Real 3' <= loop2.today() then
                1
            end;
        let F4 := if loop2.'Real 4'<= loop2.today() then
                2
            end;
        let F5 := if loop2.'Real 5' <= loop2.today() 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.'Real 3'
                case 2:
                    loop2.'Real 4'
                case 4:
                    loop2.'Real 5'
                end;
            if count(loop2.'1N_Control Issues'[Date_ref = fDate]) < 1 then
                let newRec := (create 'Control Issues');
                newRec.(
                    Date_ref := fDate;
                    'wbs Documents' := loop2
                )
            end
        end
    end
end

The Problem is from line 31 and follow

if count(loop2.'1N_Control Issues'[Date_ref = fDate]) < 1 then
                let newRec := (create 'Control Issues');
                newRec.(
                    Date_ref := fDate;
                    'wbs Documents' := loop2
                )
            end
        end
    end
end

- Fred
- 1 yr agoMon, April 24, 2023 at 4:33 PM UTC
- Reported - view
Rafael is the forum broken? I don't see any line numbers in your or my posts.
- Rafael Sanchis
- Rafael_Sanchis
- 1 yr agoMon, April 24, 2023 at 5:11 PM UTC
- Reported - view
Fred don't know

The line 31 begin on the if count (loop2 .........

Fred
1 yr agoMon, April 24, 2023 at 7:53 PM UTC
Reported - view

if count(loop2.'1N_Control Issues'[Date_ref = fDate]) < 1 then
                let newRec := (create 'Control Issues');
                newRec.(
                    Date_ref := fDate;
                    'wbs Documents' := loop2
                )
            end
        end
    end
end

Line 1: what date field are you going to use. currently there is no Date_ref field in Control Issues.

- Rafael Sanchis
- Rafael_Sanchis
- 1 yr agoMon, April 24, 2023 at 8:13 PM UTC
- Reported - view
Fred Sorry Fred the name is Date_s in Control Issue

Fred
1 yr agoMon, April 24, 2023 at 8:18 PM UTC
Reported - view

let check := dialog(" Warning ", " ¿Confirm the Update Transmittal ? ", ["Yes", "No"]);
if check = "Yes" then
    for loop2 in (select 'dates by Documents')['Document'] do

you changed line 3 to 'dates by Documents' when it was 'wbs by Documents'. Since you changed tables:

if count(loop2.'1N_Control Issues'[Date_ref = fDate]) < 1 then

Is '1N_Control Issues' a field in 'dates by Documents'?

- Rafael Sanchis
- Rafael_Sanchis
- 1 yr agoMon, April 24, 2023 at 8:23 PM UTC
- Reported - view
Fred Yes now I like Have a table 'wbs by Documents' and a subtable 'Dates by Documents' here I have all Dates Plan and Dates Real.
- Rafael Sanchis
- Rafael_Sanchis
- 1 yr agoMon, April 24, 2023 at 8:25 PM UTC
- Reported - view
Fred This is a part of the model

Fred
1 yr agoMon, April 24, 2023 at 8:57 PM UTC
Reported - view

You have to follow your links to get the correct data now that you have changed your loop2 starting point.

if count(loop2.'1N_Control Issues'[Date_ref = fDate]) < 1 then
                let newRec := (create 'Control Issues');
                newRec.(
                    Date_ref := fDate;
                    'wbs Documents' := loop2
                )
            end
        end
    end
end

So loop2 starts in 'dates by Documents' now. 'dates by Documents' is a child of 'wbs by Documents' so you use that link to get the related record from 'wbs by Documents'. In your DB you call that reference link 'Documents' in 'dates by Documents'. In addition you don't have a field called 'Date_ref'. So you would change the code to look like:

if count(loop2.Documents.'1N_Control Issues'[Date_s = fDate]) < 1 then
                let newRec := (create 'Control Issues');
                newRec.(
                    Date_s := fDate;
                    'wbs Documents' := loop2
                )
            end
        end
    end
end

Now you need to fix line 5 since:

1) update the field name to match the new name

2) the variable loop2 is in 'dates by Documents' you can't use that to link.

You will have to make the following changes:

if count(loop2.Documents.'1N_Control Issues'[Date_s = fDate]) < 1 then
                let newRec := (create 'Control Issues');
                newRec.(
                    Date_s := fDate;
                    'wbs by Documents' := loop2.Documents
                )
            end
        end
    end
end

- Rafael Sanchis
- Rafael_Sanchis
- 1 yr agoMon, April 24, 2023 at 10:09 PM UTC
- Reported - view
Fred Thanks Fred I try it tomorrow morning.
- Rafael Sanchis
- Rafael_Sanchis
- 1 yr agoTue, April 25, 2023 at 10:32 AM UTC
- Reported - view
Fred Hi Fred the script don't give me error, but no copy the 3 dates Real 3 4 5 on Control Issues, update the Date-s, Week, Id Doc and document but no the 'Real 3' on Rev A , 'Real 4' on Rev B and 'Real 6' on Rev 0 are empty fields.

I place the Button on 'wbs by Documents'

• Modelo.ninox
- Fred
- 1 yr agoTue, April 25, 2023 at 1:17 PM UTC
- Reported - view
Rafael Your code doesn’t copy those fields over. You need to add them to the newRec part.
- Rafael Sanchis
- Rafael_Sanchis
- 1 yr agoTue, April 25, 2023 at 1:59 PM UTC
- Reported - view
Fred Sorry Fred how Add them
- Rafael Sanchis
- Rafael_Sanchis
- 1 yr agoTue, April 25, 2023 at 3:07 PM UTC
- Reported - view
Fred Hi Fred

One Question. Wouldn't it be easier to create separate tables for each of the Real 3, 4, and 5 ?

Fred
1 yr agoTue, April 25, 2023 at 3:54 PM UTC
Reported - view

Rafael said:
One Question. Wouldn't it be easier to create separate tables for each of the Real 3, 4, and 5 ?

separate tables? or separate records?

generally when you find yourself creating multiple instances of the same field, date1, date2, date3, etc, you should create a new child table that tracks each instance of the data.

- Rafael Sanchis
- Rafael_Sanchis
- 1 yr agoTue, April 25, 2023 at 4:03 PM UTC
- Reported - view
Fred Separate Tables Fred
- Fred
- 1 yr agoTue, April 25, 2023 at 4:14 PM UTC
- Reported - view
Rafael Generally, you create new tables to track different sets of data. If the only difference between Real 3,4, & 5 is a date then, in my opinion, you would put them in the same table with a field that differentiates between 3,4, or 5.

You can always test it out both ways to see what the limitations are when trying to use the data.
- Rafael Sanchis
- Rafael_Sanchis
- 1 yr agoTue, April 25, 2023 at 4:57 PM UTC
- Reported - view
Fred OK Thank Fred

Welcome to the Ninox community

Adapt fórmula to new Structure.

22 replies

Content aside

Categories

Overview