Importing Data
Application Type
Traditional Web

Hi All, 

Hope you are fit and well. 

In my application i have 2 modules, one that i use for the UI and another is the "Core" module which i use to store my Entity Data. When i create an Entity i build it according to the columns in my Excel Sheet and then "Create an action to Bootstrap from Excel" which successfully imports the data. 

That's all fine however, if i then Add a new row of data to my Excel, go to the entity and click "Update action to bootstrap from excel" in order to pick up the new row of data it doesn't add the new entry.  The only way it works is if i delete the existing entity completely and build it in again. Is this normal? is there something i need to do for this to work?

Thanks

Mike

Hi Mike,


If we take a look at the outsystems action that was created when you went to bootstrap your data you will see that it actually looks for data in that entity and if outsystems sees that the entity has data in it it will end the flow right there. 

This flow can be modified to accept any data regardless of weather there is data in that entity or not but that will be on the developer and not an outsystems generated action. 



Hope this helps!!!

Bryan 

Hi Bryan,

Thanks for your response, This makes sense. As a test i removed the condition and ran the bootstrap which successfully adds the new data. Obviously it duplicates everything else too. Would you say the easiest way would be to amend the condition to say "GetMovies.List.Empty = False" - Add server action to Delete/Drop the table then run the "True" action to rebuild the Table in its entirety?

Or is that cheating/Lazy?

Thanks

Mike

Hey Mike,

No that's defiantly a flow that you could do on the false branch, to easily accomplish the delete its recommended to use a SQL statement as stated in this guide https://success.outsystems.com/Documentation/How-to_Guides/Data/How_to_delete_data_from_Entities

This would be the easiest solution, if you want to get fancy however you could make some logic to compare records from the records that exist in the entity and the records coming from the excel sheet but that will cost some more performance resources.

Also be sure to look at the timer that was created when you created the update action from excel this timer will run every time the module is published. You may want to delete this timer and assign this action to a button so it doesn't delete and reupload the resources every time you publish the module (unless you've already done this of course) 

Let me know if you have any more questions!!

Bryan 



Thank you, here is a bigger purpose behind this. My main goal is as follows....

The entity is a table of employee data, Employees come and go so this table needs to be maintained. The idea is to add a button into my UI Module that runs the "Update Bootstrap for the entity" 

Thanks.

Hello,

Could I ask why the need to bootstrap? Is the excel import a requirement? You could always build a detail page where you can edit or delete existing employees and add new ones. 

Another note bootstrapping data is only available at design time and its made more for importing data to start off with, most of the time we create our own actions to create/update/delete records from an entity.

-Bryan  

Hi

If only that was possible, Its a large organisation. To update the database manually would be too much work. The main database holding employee data is not yet directly contactable by outsystems due to security barriers shall we say, So the idea is that we will export the data from the main system into CSV, then build a robot which will log into my system and click the button which will pull the CSV in and update my Employee Entity. I am learning Outsystems as i go so please let me know if i am overthinking this and if there is a simpler way of doing this. 

Thanks again

I wouldn't say you're overthinking it but a couple things to consider. If you delete all the records and load new ones into that entity in outsystems any relational data that was present in the entity is now lost. Not sure if you planning to do any relational data modeling in outsystems but just something to think about. Also if you are to take the approach of deleting and adding new records, with the exception that you are providing and identifier for the records, all of the records are going to pick up where the last ones left off for the identifier as outsystems sets the Id field to autonumber by default for new entities. 

My recommendation for this is to run an action that converts the csv to a record list and use some comparison logic to see if that user exists, update that user data if it does exist or create a new one if there is no matching user in the outsystems entity. That way you keep any relational data pertaining to a user as you are just updating it and not deleting it and adding a new record for that user in the outsystems entity.

Here is a simple example of what that action might look like. Were looping through the CSV list then querying the DB to look for a match in the Id (there is changes that can be made here to reduce performance but for simplicity sake I used this logic), if the list is empty it creates a new employee if the list has a record then it updated that record with the information from the CSV 

I hope this helps!!

-Bryan 

Brill, That is exactly whats happening, it building on from the previous ID's, i dont like that. A loop to Ignore/Add/Replace is definately preferred. Ill have a look through your example Flow and fingers crossed i can succeffully integrate it into my App.

Thank you so much for your help.

Mike

Your welcome let me know how it goes!! And if that answer solves your issue don't forget to mark it as a solution it really helps us out!! 

Thanks!

Bryan 

Hi Bryan, Ive been locking horns with this over the past few days and wondered if you can help me with something. Im bringing my Excel sheet into Outsystems VIA the resources folder, My server action to import from Excel calls the resources folder and updates the Entity accordingly. This works, but on a one time basis only. its almost as if it uploads once and then caches the data so any further requests to run the server action just pull back the cached data rather than making a fresh call to the file location on my C:

If i Delete and re-add the resource and run the request it updates as it should. 

How do i get my server action to make a fresh call to the file in my C: to pick up the changes. 

i have thinned out my server action to keep it simple and show the relevant parts and clicked on each step so you can see what is assigned to where.

Hello,

Sorry for the late reply but I was doing some research on your use case and I came across this approach that might be a better answer than what you are currently trying to do. Specifically the post from Eduardo on 2018-07-11 

https://www.outsystems.com/forums/discussion/37210/how-add-file-to-resource-folder/

His answer was along the lines of using OutSystems to store the file as binary data in a entity then use a timer to process that excel file into a record set, so instead of using the resources folder you can use an entity and process the data from there. Let me know if you think this will work for you 

-Bryan

mvp_badge
MVP

Hello Michael,

Hope you're doing well.

Most likely, your new Bootstrap action has a logic by default that validates if the entity is empty or not. If it is, it executes the rest of the action, otherwise it does nothing. Something like this:

That's why it only works if you delete the existing data. You will need to adapt your logic accordingly if you don't want this behavior. In this case, you can (as an example) implement a logic that deletes all the data from the entity and creates all the records from the Excel afterwards, removing the validation from the flow (if widget).


P.S.: Please be careful with this:

"The only way it works is if i delete the existing entity completely and build it in again."

Everytime you do this, it will lead to the creation of a new table in the Physical Database, but the older table will still remain there. Everytime a new entity is created, it is empty, that's why it works in this scenario. But you should be careful because you are creating new tables for the same purpose and this may lead to several unnecessary tables using space in the Database.


Kind regards,

Rui Barradas

Hi Rui

This makes sense, and certainly my initial thought would be to Drop/Delete the table if it exists i.e. the condition=False. And then run the "true" condition to rebuild the table. 

The main purpose of why i need to be able to update is because i am adding a button into my UI module to allow Users to Import and update the table. 

The table itself contains employee data, so as employees join and leave the business the table needs to be updated. The idea is to add a button in my UI which when clicked runs the Bootstrap action to update the entity. 

Thanks

Mike

Mike,

bear in mind that this bootstrapping you have started with, is not intended for keeping your entity in synch with changing data in excel.  It is a great tool for quickly seeding your database with some example or pre determined data.  But just fidgeting a bit with the bootstrap action will probably not bring you where you need to be.  For example, during design time (at the moment developer is adding the bootstrapping) a copy of the excel is stored as a resource in the module.  

If you want your entity to get updated everytime an end user pressed a button on the UI, you will also have to add additional steps in your logic to go and fetch the most recent version of the excel.  (be it the end user uploading it, or your logic retrieving it from some ftp server, or ....)

So you'll have to do a bit more that just get around the 'if empty' and get around the duplication.

Dorine

Thank you Dorine, I think you're right, messing with the existing bootstrap isn't the way to it. I'm going to play around with a separate action to grab the CSV, loop through the the entity and disregard anything that already exists and is unchanged but adds anything that is new. 

Bryan Minton ha given me an example of a flow that i will have a look through and tweak to suit my needs, fingers crossed i can get this to work. 

Many thanks

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.