Importing from Excel to update an entity on button press.

Hi all

I have been working on an action to allow Users to update the data within an Entity by clicking a button to import a new file from Excel. I have been given some great help on here and the solution is in sight but i have one remaining issue to get around. 

What i have built so far works, one time only. My action is calling the file from the resource folder. From what i can gather, the resource folder only calls the file location within my C: on the first request and then perhaps stores the data so future calls are only picking up the cached file within the resources folder rather than making a fresh call to my File location each time. 

Can anyone help me with how i can enable users to click a button, pick up the new file from an external location (in this case my C:) to update the entity. I have the structure to update the entity in place (Credit to Bryan Minton for helping build a loop), its just getting it to pick up the new file I'm having difficulty with.

Thank you in advance for your help

Mike

mvp_badge
MVP
Solution

Meanwhile, I also found this post, that contains other print screens that can give you more insights.

mvp_badge
MVP

Hi Michael,

You will need to provide the user a screen with the upload widget.

The way that you are doing, via resource folder, is the way used to bootstrap and it's normally a one-time shot process to populate an empty entity.

I would say that you need to go Learn and check some videos and do the exercises in order to be more comfortable with OutSystems.

Hope this helps you.

Hi Nuno

The screens and widgets are in place, as is the loop to update the entity. i have been unsuccessful in finding information/videos on how to import from excel for the purpose in which i need it. Videos are all about bootstrapping  to create an entity, one time only. which isn't what i need. Hopefully someone here knows how to do this and can help me out. 

Thanks for your reply.

mvp_badge
MVP

You will need the Excel to Record List widget in your action in order to transform the excel content in a list that you can iterate.

mvp_badge
MVP
Solution

Meanwhile, I also found this post, that contains other print screens that can give you more insights.

Thanks Nuno, 

I have this in place. My issue is the Excel to record list is pulling the data from my resources folder. I'm trying to figure out how to get it to contact my C: to pull in the latest version of the spreadsheet and then update the entity with the data. The file content of the Excel to record list needs to make a fresh call to my C: this is where i am having difficulty.

Hi Nuno

The link you gave me to a previous thread contained the solution with a bit of manipulation. I have encountered another issue which i will research and hopefully figure out but the entity now updates. Thanks for your help. Have marked this as a solution.

Mike

mvp_badge
MVP

Need to change the origin of the binary content to be the one from the upload.

What's the new issue? Maybe I can give you another help.

Perfect. The new issue is with the ID of the Entity. As a temporary measure, to prevent duplicate rows im running an SQL to delete the existing contents and then upload the new file.  The issue with this is that the ID column doesn't start again from "1" it picks up and numbers the ID where the old file left off.

To give you some context, the Entity is a list of Employees. Employees come and go so the idea is to compare the existing List with the new import, ignore anything that already exists, update anything that may have changed i.e. someones Surname. Delete anything that has changed i.e. an employee has Quit and ADD anything new i.e. new starters. 

Deleting the full table and just replacing everything is the easiest way but i need it to Number the ID from "1" 

I have been working on a loop that i believe to be the correct thing to do correct but its not working. Here's my "in progress" loop:

mvp_badge
MVP

You can't control the ID it's an auto number, which means that you have a trigger that gets the next value in the sequence.

Maybe you can change the ID and set the Is Auto Number property to False, but this will force you to manage the Id's

Thanks Nuno

Is there not a way of using a for loop like the one i am working on to get it to cycle through the data and just add new entries where needed and updating existing ones? this should keep the ID in tact for anything that is already present. 

For example, Each Employee does have a unique Employee Number (a 9 digit number, seperate to the Auto generated ID. So if i create a loop and put a condition in to say if the Employee Number (not the ID) in the entity = the employee number in the upload - update rows. If false Create new.   or something similar such is if Employee number in Employee Entity = Null - add if true, update if false. 

mvp_badge
MVP

You can use that unique number (employee id ) to check if the record already exists, but looking into your logic it seems you already have that condition there, when you check if it's empty.

Maybe i didnt explain the issue so well. Lets say i have a list of 100 employees, the Auto generated ID ranges from 1-100.

If i I have an action to delete this table and repopulate with my new upload it doesnt number from 1-100 it numbers them from 101-200. Even though the original rows have been deleted. This is likely to be updated on a daily basis and will contain around 8000 Rows. You can imagine it wont take long until the ID is Auto numbered into the millions. 

Yes its got me a little puzzled as its not working. The logic makes sense to me but i feel like it needs something else to knock it over the finish line. 

mvp_badge
MVP

But this way, having a match to perform an update you don't need to delete all, and this way the Id is the same.

Thanks Nuno. I have it working. in a nutshell, I removed the AUTO Generated ID column and instead set the ID as the Employee reference number as it is Unique. This way i was able to map the New import file to the existing Entity with no issues. I didn't even need the for loop. 

Thanks again

Mike

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