Adapt fórmula to new Structure.
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;
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
Copy
Fred
Now I Have the Documents on one table and all Dates ( Plan and Real ) on Subtable, is possible adapt the formula.
Appreciate help.
22 replies
-
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.
-
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
-
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.
-
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'?
-
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 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.
Content aside
- Status Answered
- 1 yr agoLast active
- 22Replies
- 99Views
-
2
Following