Updating database with excel to record list

Updating database with excel to record list

  

Hi,


I am trying to update my database with excel to record list. My excel sheet basically is an add on of newer entries. I created a user upload feature to upload the excel file. i used a for each to update the database. However, i am getting duplicated entries together with the newer entries. and more each time i upload. 

Therefore I used a if logic to check for duplicate entries comparing between the table in the websrceen and the excel recordlist. But the duplicate is still happening.

I have a feeling that after my true branch (i used a not equal comparison) i am missing something before the create/update server action..


Hope to get some help.

Regards

Vinod


Hi Than,

I guess that you miss the old database record's ID. After comparing and the result is True, the ID must be assigned to the Source Record of the CreateUpdate action. Outsystems will do update if the ID is known/not null, otherwise it will do create.

regards,

indra

Indra Budiantho wrote:

Hi Than,

I guess that you miss the old database record's ID. After comparing and the result is True, the ID must be assigned to the Source Record of the CreateUpdate action. Outsystems will do update if the ID is known/not null, otherwise it will do create.

regards,

indra

Thanks for the reply, however i am still facing the same problem. When u say after comparing, the ID must be assigned to the source record - which ID are u referring to? The ID of the old database?


Am i right to say the course record of the CreateUpdate action should not be the excel to recordlist??



Hello Vinod,

When you do the update from the excellist, you'll need to do a check in the for each loop (query to get data from DB based on the 'unique key' (values from excellist which identifies the record to be the same). When the query gets a result (query attribute empty = false), you'll know it already exist and can go the to the next one.

When you'll check if the record already exist from the table in the screen, you have the possibility that not all records are in the list (because of paging and optimizing by the platform), so check the database table if that record already exist or not.

Hope this helps, else a screenshot(s) of the logic would really help to understand the problem.

Kind regards,
Evert

Solution

Hi Vinod,

here we go:

1. this is the current state of the entity, say it entity Student

2. then i want to upload excel, with this data:

3. say, the Name must be unique (case sensitive), then "Indra" should not inserted to the entity Student, but only update the Age column value (from 12 to 35), else - Tesla and Oracle- should be inserted as is:

4. The Logic is like this:

where:

a) b) filter to check if the inserting record is already exists in the entity:


c). If empty (not exists) just insert it (i put dummy assignment just for my comment to bind to..no worries about that), otherwise, update it: give the old database record's ID value to the source of the createupdate: 

where:

c1)  

c2)

c3)

c4)

That's it. May this helps.

regards,

indra

Solution

Hi Indra, Thanks alot for the insightful help. My filtering in the aggregate was done wrongly. Thus i had this issue. Now its solved.

Nevertheless, the additional info on the usage of the dummy assignment is something new to me. Learn something form this. Appreciate your help. Thanks again.


Avert - Thanks for your sharing on using the DB rather than using the table on the screen.