How to stop "For Each Loop" action creating new rows in database?

We use For Every Loop for literate over list of records, and on each cycle we set action to create a record in table.


The table contains a list of services that the user provides. Columns have service names, and the data type of the column value is Boolean.


For each creates completly new row in the table for each record, but I want to have everything in a one row rather multiple rows.


See, illustrations below


+ What currently For Each Loop produces


Services Table

---------------------------------

Logo Design|Coding     |User Id|

-----------|-----------|---------

|True      |False      |   17   |

|False     |True       |   17   |

---------------------------------


+ What I need


Services Table

---------------------------------

Logo Design|Coding     |User Id|

-----------|-----------|---------

|True      |True       |   17   |

---------------------------------


Is there any way to do this?

I am sure there is a way. I saw several examples that suggest using the Assign action and things like collections, and don't know how to make them work in this case.



The problem isn't the for each, the problem is the create action. The create action creates a new row in the database, if you want to update an existing row you must use the update action.

for this use create and update entity action , i guess will result in what you want


THe Create action always wil lcreate a record, you can use the CreateOrUpdate action and If you have an id with nullidentifier value the action will create a record else will update the record.


Best regards,

Leandro. 

Or you can modify the record during the "for each" and only create it in the data base after the end of the loop. 

Best regards 

Graça

Hi Daniel,

You can make this happen with more logic within the loop action, but I'm not certain about the rule you're looking for - do you want a User to only have one record detailing all of his services (no more than one record per user in your Services table)?

You could do this by storing the Id of the record that you create in a local variable and then reusing it in every loop to make sure you only have one line.

Afonso Carvalho wrote:

Hi Daniel,

You can make this happen with more logic within the loop action, but I'm not certain about the rule you're looking for - do you want a User to only have one record detailing all of his services (no more than one record per user in your Services table)?

You could do this by storing the Id of the record that you create in a local variable and then reusing it in every loop to make sure you only have one line.

I tried the "CreateOrUpdate" action, but now it start to overwrite values of the previous iteration .

The column values are boolean data type like "True", "False". 


Hi Daniel,

I think it's just really unclear to everyone what you want to achieve. Can you give a better example, e.g. what data is in the database, what data needs to be displayed on the screen, and why and when you are creating records?

Kilian Hekhuis wrote:

Hi Daniel,

I think it's just really unclear to everyone what you want to achieve. Can you give a better example, e.g. what data is in the database, what data needs to be displayed on the screen, and why and when you are creating records?

Sent you a PM


I've read the PM you sent me - it made things a little more clear. 

It looks like you want the True values in your boolean flags to take precedence over the False. So essentially, if I have two records with:

Flag1 - True

Flag2 - False

Flag 3 - True

and

Flag1 - False

Flag2 - False

Flag 3 - True

You want to process this information, and write a single record into the database with:

Flag1 - True

Flag2 - False

Flag 3 - True

Instead of writing to the database in each for loop, you can loop through all of your records and construct a final record to write in the database. For each flag inside the record, if the current value that is being iterated is True, then set the final record flag to True. This way, if any of the flags in your records are True, the final value will be True regardless of how many are set to False.

If that's really the case, just Group By the attributes and use a Max() on the column (as True = 1 and False = 0).

Afonso Carvalho wrote:

I've read the PM you sent me - it made things a little more clear. 

It looks like you want the True values in your boolean flags to take precedence over the False. So essentially, if I have two records with:

Flag1 - True

Flag2 - False

Flag 3 - True

and

Flag1 - False

Flag2 - False

Flag 3 - True

You want to process this information, and write a single record into the database with:

Flag1 - True

Flag2 - False

Flag 3 - True

Instead of writing to the database in each for loop, you can loop through all of your records and construct a final record to write in the database. For each flag inside the record, if the current value that is being iterated is True, then set the final record flag to True. This way, if any of the flags in your records are True, the final value will be True regardless of how many are set to False.

It doesn't work. Values gets overwritten.

I think this might be limitation of out systems, spent 5 hours for simple thing. I will stop using Out systems. It's easier do this in  Python instead.


I think what do you want to do, is to make this with an advanced query where output is a structure with 3 fields: islogodesign (boolean), islogocoding(boolean) and userid(user identifier)

Something like this:


WITH COUNTER as (SELECT

    COUNT(CASE WHEN logodesign then 1 ELSE 0 END) as logodesign_truecounter,

    COUNT(CASE WHEN coding then 1 ELSE 0 END) as coding_truecounter,

servicestable.userid

from servicestable

group by servicestable.userid)

Insert into servicestable

select

CASE WHEN counter.logodesign_truecounter>0 then 1 else 0 End,

CASE WHEN counter.coding_truecounter>0 then 1 else 0 End,

counter.userid

from COUNTER


Nuno Gonçalo Pereira wrote:

I think what do you want to do, is to make this with an advanced query where output is a structure with 3 fields: islogodesign (boolean), islogocoding(boolean) and userid(user identifier)

Something like this:


WITH COUNTER as (SELECT

    COUNT(CASE WHEN logodesign then 1 ELSE 0 END) as logodesign_truecounter,

    COUNT(CASE WHEN coding then 1 ELSE 0 END) as coding_truecounter,

servicestable.userid

from servicestable

group by servicestable.userid)

Insert into servicestable

select

CASE WHEN counter.logodesign_truecounter>0 then 1 else 0 End,

CASE WHEN counter.coding_truecounter>0 then 1 else 0 End,

counter.userid

from COUNTER


Sent you Private Msg.

Daniel Hobgood wrote:

I think this might be limitation of out systems, spent 5 hours for simple thing. I will stop using Out systems. It's easier do this in  Python instead.

Learning a new system is always a challenge, and you can expect to be proficient in two weeks. I understand how frustrating it can be to try doing something that seems simple and not succeeding, but remember that any new platform will take you at least six months before you're mildly comfortable (even though with OutSystems it may take less).

That said, I still have no real idea what you want to achieve, and given the replies of others, I think nobody really understands it, so it may be your design is just too complex?



Hello Daniel,

It seems that you want to do a "kind" of transposing, aggregating values on a single line for the same user id.

Please, take a look at the example I am attaching.

You can replace the "ListIndexOf" by an aggregate that fetches the line, from the database, and if you find someone already there, you do the OR thing on the attributes, otherwise, you create a new record.

Hope this helps.