0

Manual Restore of Entire Database, Table by Table

I download a copy of my DB in Ninox format and as a XLSX file every month (just in case Ninox disappears or I decide on another platform). I decided to test a manual restore of my database. The first issue I ran into is that I can't depend on the rec Id to link records. Here is an example of the csv for a table called Seasons:

Id,Year,League,Stats Needed,SeasonID,LinkID,

10,2023,1,"R1, R2, GCL SuperCup, Full Event",10,1,

11,2023,2,"R1, MLSJ JO, Event Atm",11,2,

12,2024,3,"R1, R2, Full Event",12,3,

In this table   there is a reference field called League and that shows up with the record Id in the csv. The League table only has 3 records and will always import in the same order so I can use the reference field.Id to link Seasons records to League.

The problem happens when the saved record Id doesn't match the newly imported data.

I have a parent table called Teams. The csv has 57 records, but the Id field goes up to 60. Some records were deleted over the years. In the child table called TeamNames, there are references to records 58, 59, and 60, see below under Team:

Id,Start Year,Name,Team,LinkID,TeamNameID,

57,0,Brazil,57,155,57,

58,0,Maccabi United,58,156,58,

59,0,Archers,59,157,59,

60,14,KPF,44,144,60,

61,0,Italy,60,158,61,

So if I try to use the reference record Id to make the link Ninox wouldn't find 58, 59, or 60 since they don't exist in the newly imported table.

My solution to this is to create two new fields in all my tables. Currently I created a number field with [tablename]ID and a text field called LinkID. I probably could have just called the first field recID or something simple like that. Which is probably something that I'll do.

The LinkID field is a string that combines the table name and the assigned recID. Something like:

Id,Riders,Horse,Current_Partner,PartnerID,LinkID,

1,1,471,Yes,1,"People:102,Horse:573",

2,218,691,Yes,2,"People:667,Horse:799",

So now I know that I link to the People table and Horse table.

Then I can just create a button that creates an array at the first ",". Then subsequent arrays at ":", then I can get the data I need.

I've also created a list of which tables needed to be imported in groups so subsequent tables can be linked properly.

I also had to add to each table Trigger on new record to find the max recID and add 1.

Then go through each bit of code that creates records to include the code to create the LinkID data.

I used the Bulk Edit feature of Ninox to populate the recID and LinkID. I just used rec Id to be my recID.

I did this cause I wonder how difficult it would be if I made major changes to my DB and wanted to transfer all the data over to the new DB. Also I guess you would need the same data if you were to leave Ninox and needed to import into your new DB.

In the end it looks like you might want to consider creating key fields in Ninox as a backup system.

I hope this helps others. If you have any tips, I would love to hear them.

1 reply

null
    • gold_cat
    • 5 days ago
    • Reported - view

    I once had this idea because Ninox initially mentioned that different accounts have record limits. I simply edited the numeric backup below. Of course, this method requires the backup and the database being backed up to have matching database IDs.
     

    Due to the translation, I still need time to understand the solution you provided afterward.

    Thank you for sharing.