Log in to AnswerLog in to Follow
UserImage.jpg
Michael Freath
21Views
6Comments
Solved
Housekeeping - Data Transfer/Backup
Question
Data
Traditional Web
Application Type
Traditional Web

Hope you are well

I have working application and i am scratching my head over an issue with data Backup. Within my application i have a bookings table. The bookings need to be kept for a period of 3 years however, i don't want this amount of data stored in the Booking table that is in use in the interface. 

What i have done is created a duplicate of the table and build a process that i will run on a timer which loops through "BookingTable" assigns each attribute to a record variable and then a server action to create or update the "BookingTableBU" from the record variable. Other than the ID, there is no other unique column. The BookingTableID is shared with the BookingTableBU to ensure they match and prevents it duplicating the entries each time the process runs. This is working fine. 

The problem comes when i build the process to Delete entries from BookingTable that are older than 1 months old. To do this i have a simple SQL Delete but of course it doesn't like it because the BookingTableID is shared with BookingTableBUId. 

I have tried several things, The obvious one is to leave the BookingTableBU ID as Auto number rather than sharing it but the result of this is of course that each time i run the process it duplicates the data as there is no unique identifier. 

I then tried not linking the ID, setting AutoNumber to No, and use the Assign statement in the loop to assign the BookingTableBU.ID variable to the BookingTableID. This throws an error saying "BookingTableBU identifier expected instead of BookingTable Identifier, Please use Data Conversion or try something else" 

Other things i have tried result in data being deleted from Both Tables. Assuming also related to the Linked ID.

Is there a data conversion in the assign statement that will work for this? Am i over complicating it? How would you complete the task.


Here is My process for building BookingTableBU (In itself, This Works)




Here is my Delete process with the SQL error:


All Help is Much Appreciated.

Thanks

Mike

0
0
13 days ago

Hi Michael,
I have created the following Entities 
BookingTable - Main Table
BookingTablesBU - BU Table has an ID field referenced to BookingTable. I had set the DeleteRule to Ignore.

Since BookingTableBU.Id is referencing BookingTable.Id, we need to insert a record into the BU table while the record exists in the Main table.

Once the record is inserted into BU, we can delete the record in the Main table and the DeleteRule Ignore will make sure there won't be any error while deleting.


I have created 3 screens 

  1. BookingTableBU - to display records in BU Table
  2. CombinedData - to display data fetched from both tables BU and Main
  3. BookingTables - to display the records in the Main table, with a button and action to move the data to BU Table.

You can try using this URL.
https://kavinilammurugu.outsystemscloud.com/IDMaipulation


I have attached a sample OML for your reference.


See solution in context
IDMaipulation.oml

0
0
12 days ago

Hi, Thank you so much for taking the time to do this. I will try it out and let you know how it works. 

Thanks

Mike

0
0
12 days ago

I can confirm that changing the Delete Rule to Ignore works as a solution. In order to make sure the data is copied across to the Backup File before it is deleted i have moved the Delete process. Instead of running it separately, i have added it into the Data Transfer process, as the last step and this seems to work ok.

0
0
12 days ago

Hello Michael,


To delete main table entry, which reference is used in any extension/child entity, we must define delete rule as 'Ignore' as mentioned in other answer.

Addition to above answer, Now if you are going to modify existing entity( delete rule for entity) you may face some issue, to accomplish this, you have to Cut and Paste your existing entity, modify delete rule and publish. 

Another suggestion will be, instead of deleting record from entity, make it InActive for around 3-6 month data and archive older data.


Cheers,  

Sanjay

See solution in context
0
0
12 days ago

Thank you for this Sanjay.

Yes this is a good point, Deleting records from the Backup Table will certainly encounter issues. There is nothing wrong with me keeping all data in here as the project is more than likely not going to continue beyond 3 years. But i will keep your suggestions ready in-case i need to clear items from the backup file.


Thank you very much for your help.

Mike

0
0
12 days ago

Hi Michael,
I have created the following Entities 
BookingTable - Main Table
BookingTablesBU - BU Table has an ID field referenced to BookingTable. I had set the DeleteRule to Ignore.

Since BookingTableBU.Id is referencing BookingTable.Id, we need to insert a record into the BU table while the record exists in the Main table.

Once the record is inserted into BU, we can delete the record in the Main table and the DeleteRule Ignore will make sure there won't be any error while deleting.


I have created 3 screens 

  1. BookingTableBU - to display records in BU Table
  2. CombinedData - to display data fetched from both tables BU and Main
  3. BookingTables - to display the records in the Main table, with a button and action to move the data to BU Table.

You can try using this URL.
https://kavinilammurugu.outsystemscloud.com/IDMaipulation


I have attached a sample OML for your reference.


IDMaipulation.oml

0
0
12 days ago

Hi, Thank you so much for taking the time to do this. I will try it out and let you know how it works. 

Thanks

Mike

0
0
12 days ago

I can confirm that changing the Delete Rule to Ignore works as a solution. In order to make sure the data is copied across to the Backup File before it is deleted i have moved the Delete process. Instead of running it separately, i have added it into the Data Transfer process, as the last step and this seems to work ok.

0
0
12 days ago

Hello Michael,


To delete main table entry, which reference is used in any extension/child entity, we must define delete rule as 'Ignore' as mentioned in other answer.

Addition to above answer, Now if you are going to modify existing entity( delete rule for entity) you may face some issue, to accomplish this, you have to Cut and Paste your existing entity, modify delete rule and publish. 

Another suggestion will be, instead of deleting record from entity, make it InActive for around 3-6 month data and archive older data.


Cheers,  

Sanjay

0
0
12 days ago

Thank you for this Sanjay.

Yes this is a good point, Deleting records from the Backup Table will certainly encounter issues. There is nothing wrong with me keeping all data in here as the project is more than likely not going to continue beyond 3 years. But i will keep your suggestions ready in-case i need to clear items from the backup file.


Thank you very much for your help.

Mike

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