Hello Is there any way to segregate data in the database into 2 different tables.
The scenario is as follows:
I upload an excel file in the screen and all the data gets stored in the db. Now I want to segregate the data in db into 2 other tables (valid and invalid) on the basis of location. Can you please help me with it?
I have attached my oml file in it.
Thanking you in Advance
Hi Athulya,
Yes, that should be possible, but if it is the best solution depends on your use case.
Why you want to separate records based on valid state into two entities. What needs to happen if the data gets corrected? Then you need to add the corrected record to the entity with correct records and remove it from the entity with incorrect records.
Would it not be more efficient if you create an extra boolean attribute in your entity called IsValid.
Set it to true or false, based on your validation. Include it in filters when you query your data and also consider it to be part of relevant indexes to speed up queries.
If your use case is to just ignore the wrong data, (so it will not be corrected), then a separate entity seems to be the right choice.
In this case you can copy the entity you have, and rename it to a meaning full name like Sheet1InvalidRecords.
In your server action BootstrapSheet1s you should add an extra if in front of the CreateSheet1 and UpdateSheet1 action to determine if you need to write the valid record to it or to the Sheet1InvalidRecords entity.
Regards,
Daniel
@Daniël Kuhlmann
I actually want the whole process to be automatic
Like when I upload an excel file it already contains both valid as well as invalid records. So I want to create a function or something that checks them and then segregate them into 2 other tables i.e. valid and invalids
Hi @Athulya Panicker,
What's your definition of valid/invalid ?
Is it considered invalid if the record already exist in DB ?
I checked your sample oml and it seems like there are no checking yet if the uploaded file contains invalid/valid record. I might misunderstood something here.
@Marco Mateo
My valid records means the one which consists of proper Location which should be either Mumbai or Banglore And invalid records means the rest locations or these locations added with wrong spellings (eg. Mum_bai)
And yes I need to do a checking after the file is uploaded and then also segregate them into 2 other tables.
Hi @Athulya Panicker
Got it. You should have a list of valid location and check if the location from excel exist in the valid list.
If it exist, create/update record in Valid Table. Otherwise, put them in Invalid Table.
I attached the sample oml.
PS: In the sample, I used an entity to store the valid locations but this depends on your preference. you could have an JSON or static entity that stores all the valid locations
Hope this helps
Cheers
This is the upload flow in my sample oml
@Marco Mateo I tried the solution you gave me but it is not exactly the way I want to implement the valid and invalid records in my systems.
How do you want to implement it ?
I might be misunderstanding your real issue here.
Hi Athulya Panicker,
As the size of the file being uploaded is unknown will suggest you to separate out the uploading and processing of the file. This is what you can do.
On the User Interface:
1. On Upload, save the File in a Table say File_Upload as a Binary with additional details like when uploaded, by whom, size, is processing completed, unique key for checking the processing status. Also launch a Timer that will be taking care of processing the records.
2. On the User Interface, on successful file upload, you can generate a unique key using which one can get to know the status of the processing and if completed, links to see valid and invalid records. OR a Mail can be triggered to the User on completion of File processing that will contain a link that redirects user to a page that displays the required details about the processing.
See what is the best way to get a good User experience
Timer Logic:
Pre-Requsites: Have a Table(s) to store valid/invalid records. This can be achieved in one table itself by having an additional boolean flag that dictates if the record is valid/invalid. The Table should contain a reference to the File_Upload record so that one knows these records are for which file upload
Process the file that is uploaded, and accordingly insert data of valid / invalid records (Row Number in the file, column name etc...). Once completed you can mark the processing as complete and if need be get the User notified of the same.
Saurabh