Script examples

Example: Append new records to a table

#FlowScript #Join #If statement #For loop #With #Table concatenation #Create table

This script creates a new table and fills it with data from an already existing table, where one column will be updated with new data.

Input table:

DS_periodDates

date	dayStatus
2019-12-01	Confirmed
2019-12-02	Reported
2019-12-03	3 hours left to report
2019-12-04	8 hours left to report

DS_favorites

customerId	customerName	project	accountDate
3245	abc	4565	2019-12-01
2456	adc	5465	2019-12-01
5974	acb	4564	2019-12-01

Script:

let generatedFavorites = table(customerId, customerName, project, accountDate, objversion, objid);

for recDate in DS_periodDates do

    if recDate.date < now() and (recDate.dayStatus like '%hours left to report' or recDate.dayStatus = 'Reported') then

        for recFavorite in DS_favorites do 

            let addedFavs = recFavorite with [accountDate: recDate.date];

            set generatedFavorites = generatedFavorites & addedFavs;

        done

    end

done

return generatedFavorites; 

Description:

1. A new table is created: generatedFavorites (row 1 in script below)

2. The script will loop for every row in table DS_periodDates (3) where the date is in the future and the day status is like '%hours left to report' or 'Reported' (5)

   1. The script will loop for every row in table DS_favorites (7) where the account date in DS_favorites will be updated with the date from DS_periodDates (9)

   2. The script adds the result from each loop iteration to generatedFavorites by concatenating the row with table addedFavs (11)

3. The script returns generatedFavorites (19)

Output table:

generatedFavorites:

customerId	customerName	project	accountDate
3245	abc	4565	2019-12-02
2456	adc	5465	2019-12-02
5974	acb	4564	2019-12-02
3245	abc	4565	2019-12-03
2456	adc	5465	2019-12-03
5974	acb	4564	2019-12-03
3245	abc	4565	2019-12-04
2456	adc	5465	2019-12-04
5974	acb	4564	2019-12-04

Example: Join into a record

#FlowScript #Join #If statement

This script joins values and assigns the values to new variables.

Input variables:

Script:

let criticalBugsNoTeamJoinedKey = '0';
let criticalBugsNoProgressJoinedKey = '0';
let criticalBugsNotStaredJoinedKey = '0'; 

if count(criticalBugsNoTeam) > 0 then
    set criticalBugsNoTeamJoinedKey = Join(criticalBugsNoTeam.key,'%27%2C%27');
end

if count(criticalBugs) > 0 then
    set criticalBugsNoProgressJoinedKey = join(criticalBugs.key,'%27%2C%27');
end 

if count(criticalBugsNotStarted) > 0 then
    set criticalBugsNotStaredJoinedKey = join(criticalBugsNotStarted.key,'%27%2C%27');
end 

let joined= [criticalBugsNoTeamJoinedKey: criticalBugsNoTeamJoinedKey, criticalBugsNoProgressJoinedKey: criticalBugsNoProgressJoinedKey, criticalBugsNotStaredJoinedKey: criticalBugsNotStaredJoinedKey]; 

return joined;

Output variable:

Example: Convert a complex table into a simple table

#FlowScript #Join #Loop #For loop #Create table

This script will create a simple table where for example join is used to join all components together into one string so all values can be put into one column.

Input table:

The input table blockerBugsFiltered is a complex table with different levels of tables, records and simple values in the table.

Script:

let blockerBugs = table (key, summary, description, priority, components, assignee, created);

for rec_ in blockerBugsFiltered do 

    let joinComponents = join(rec_.fields.components.name, ', ');

    set blockerBugs = blockerBugs & [key: rec_.key, summary: rec_.fields.summary, description: rec_.fields.description, priority: rec_.fields.priority.name,
    components: joinComponents, assignee: rec_.fields.assignee.displayName, created: left(rec_.fields.created,10)];

done

return blockerBugs; 

Description:

1. A new table is created: blockerBugs (row 1 in the script below).

2. The script will loop for every row in table blockerBugsFiltered (3).

3. Join the values in the components table (5).

4. Append new records to table blockerBugs (7).

5. The script returns the table blockerBugs (12).

Output table:

The output of this script will be a simple table including only simple values.

Example: Sum values in a table

Script:

let stuff = [locno: "A", something: "x", qh: 20, qt: 5] & 
[locno: "A", something: "x", qh: 8, qt: 3] &
[locno: "B", something: "x", qh: 21, qt: 5] & 
[locno: "A", something: "x", qh: 5, qt: 3] &
[locno: "C", something: "x", qh: 2, qt: 5] & 
[locno: "A", something: "x", qh: 5, qt: 3] &  
[locno: "C", something: "x", qh: 10, qt: 1]; 

let t = select locno, 
sum((stuff as s where s.locno = locno).qh) as qh 
sum((stuff as s where s.locno = locno).qt) as qt 
from (select distinct locno from stuff); 


return select locno, qh, qt, (qh - qt) as qty from t;

Description:

1. Create table variable stuff and add 7 rows of data (1-7).

2. Create variable t and select and sum values distinct from table stuff (9-12).

3. Return table t. In this case qt is subtract from qt (15).

Output data: