Hi! I need tips on improving my script step for handling a large number of tasks, and I want to avoid using a lot of if statements as much as possible. Can someone point me in the right direction for assigning my tasks and their status to a table in a more efficient way. This just a small part of the code. It’s 28 tasks…
Really appreciate the help!
Code:
// declare table
let tasks = table(taskName, taskId, activitySeq);
// Loop through my tasks and check if it’s 1 or 0.
FOR i IN InputTable DO
let activitySeq = i.Aktivitetsekven;
IF i.task1 IN (1,0) THEN
let taskName = i.task1;
let taskId = i.task1_ID;
set tasks = tasks & [taskName: taskName, taskId: taskId, activitySeq: activitySeq];
END
IF i.task2 IN (1,0) THEN
let taskName2 = i.task2;
let taskId2 = i.task2_ID;
set tasks = tasks & [taskName: taskName2, taskId: taskId2, activitySeq: activitySeq];
END
IF i.task3 IN (1,0) THEN
let taskName3 = i.task3;
let taskId3 = i.task3_ID;
set tasks = tasks & [taskName: taskName3, taskId: taskId3, activitySeq: activitySeq];
END
IF i.task4 IN (1,0) THEN
let taskName4 = i.task4;
let taskId4 = i.task4_ID;
set tasks = tasks & [taskName: taskName4, taskId: taskId4, activitySeq: activitySeq];
END
IF i.task5 IN (1,0) THEN
let taskName5 = i.task5;
let taskId5 = i.task5_ID;
set tasks = tasks & [taskName: taskName5, taskId: taskId5, activitySeq: activitySeq];
END
IF i.task6 IN (1,0) THEN
let taskName6 = i.task6;
let taskId6 = i.task6_ID;
set tasks = tasks & [taskName: taskName6, taskId: taskId6, activitySeq: activitySeq];
END
IF i.task7 IN (1,0) THEN
let taskName7 = i.task7;
let taskId7 = i.task7_ID;
set tasks = tasks & [taskName: taskName7, taskId: taskId7, activitySeq: activitySeq];
END
Yes, I understand that it doesn’t look professional. But if we put that aside for now, the data comes from an SQL query with a pivot where you should be able to update information via the portal. Therefore, I need ‘task’ = what is the name of the task, ‘task_id’ = what is the ID of the task, and ‘activity sequence’ = a unique number for those tasks.
I need to group them in a table and then check if the input is 1 or 0. Then do a modify update on the task table.
Not sure if this helps, if not please ignore. But I have found use to do this below to unpivot in the update when working with pivoted data. Like this:
It’s my first attempt at creating a portal, so I understand that it may not be perfect. The portal consists of a table that retrieves data from a database as its data source. This table is populated with tasks that will be later checked and updated within our ERP system, IFS.
I’m open to suggestions for improvement as I continue to develop and refine the portal.
SELECT
*
FROM (
SELECT
ifsapp.SUB_PROJECT_API.Get_Description(a.project_id,a.sub_project_id) as “Beskrivning”,
a.name as “Namn”,
a.status AS “Status”,
a.activity_seq “Aktivitetsekven”,
a.task_id “Uppgiftsid”
FROM ifsapp.ACTIVITY_TASK_EXT2 a
WHERE
ifsapp.activity_api.Get_Activity_Responsible(activity_seq) = ‘:UserName’
AND a.project_id = ‘7700’
AND a.activity_no = ‘010’
AND a.sub_project_id LIKE ‘400%’
AND a.activity_no IS NOT NULL
AND a.early_start > ‘2023-10-01’
AND a.status IN (0, 1)
AND a.project_id IN (
SELECT project_id
FROM ifsapp.activity b
WHERE b.objstate = ‘Released’
AND upper( b.C_PROCESS_FLOW ) <> upper( ‘Fakturerad’)
AND trunc(b.date_created) >= ‘2023-01-01’
AND a.activity_no = b.activity_no
)
)
PIVOT (
MAX(“Status”),
MAX(“Uppgiftsid”) AS “ID”
FOR “Namn” IN (
‘1.01.1 - Platsbesök Bokad & DK Signal 1’ as “task1”,
‘1.01.2 - Platsbesök’ as “task2”,
‘1.02.1 - Slot-Begäran Skickad’ as “task3”,
‘1.02.2 - Slot-Begäran Godkänd’ as “task4”,
‘1.03.1 - FBL & DK Signal 2’ as “task5”,
‘1.04.1 - GVB’ as “task6”,
‘1.05.1 - Mängdning’ as “task7”,
‘1.06.1 - AMP’ as “task8”,
‘1.07.1 - Arbetshandlingar Skapade’ as “task9”,
‘1.07.2 - Arbetshandlingar Delade’ as “task10”,
‘1.08.1 - Materiel Beställt’ as “task11”,
‘1.08.2 - Materiel Levererat’ as “task12”,
‘1.09.1 - Tillstånd Ansökt’ as “task13”,
‘1.09.2 - Tillstånd Godkänd & DK Signal 4’ as “task14”,
‘1.10.1 - Avtal Fastighetsägare Ansökt’ as “task15”,
‘1.10.2 - Avtal Fastighetsägare Godkänd & DK Signal 5’ as “task16”,
‘1.11.1 - TA-plan Ansökt’ as “task17”,
‘1.11.2 - TA-plan Godkänd’ as “task18”,
‘1.12.1 - Utförande Resurser Bokade’ as “task19”,
‘2.01.1 - Schakt & DK Signal 6’ as “task20”,
‘2.02.1 - Geodetisk Mätning Beställd’ as “task21”,
‘2.02.2 - Geodetisk Mätning Mottagen’ as “task22”,
‘2.03.1 - Dagböcker’ as “task23”,
‘2.04.1 - Dokumentation’ as “task24”,
‘2.05.1 - Fakturor’ as “task25”,
‘2.06.1 - Återställning Beställd’ as “task26”,
‘2.06.2 - Återställning G01-order Begärd’ as “task27”,
‘2.07.1 - Avsluta Aktivitet & Delprojekt’ as “task28”
)
)
Did you consider un-pivoting the input table in the PL SQL window? Or if you are using Odata, maybe just un-pivot it in a SQL-step and then work with the outgoing unpivoted table?
I had a…questionable …idea how you can evaluate data pairs per row of a table by concatenating them to a string and then using flow string functions to hack away at them by data separator.
(Only 2 IFs…were harmed during the making of this flow )
So i played around with it…and here is a test flow. Testa.flow (20.2 KB)
(Not exactly an easier way of doing it…or the best optimized way…but its a way …maybe you can also automate the concatenation by including the JSON.encode… )
I just wanted to express my gratitude for the solutions provided by @ivstde. That have significantly improved my workflow, making it faster and more user-friendly. A big thank you to Ivan for his contributions!
Glad i could help!
(Also i find other peoples issues are a welcome distraction from my own issues and also sometimes turn out to be my future issues…so best to be proactive at helping to solve those )
. Isn’t it possible to fetch the data in a correct way from the start?
