Haywire formulas while duplicating a table
Hi, I am trying to duplicate a table that contains several formula fields with references to other fields in the same table. Unfortunately in the duplicated table all formulas end up pointing to the wrong fields in the new table (to oversimplify a:="purchase value"*"VAT rate" gets converted into a:="shoe size" * "Grandma's last name"). Now, I would not necessarily mind correcting this by hand, but there are rather complex accounting and tax calculations there that correlate with about 100 fields per table, and need to repeat this for 16 tables per year... so it quckly becomes impractical. I'm new to ninox and I am unaware if I may be doing something wrong while duplicating: COG -> Edit fields -> Duplicate table ... ->All fields selected --> Duplicate table. Any ideas as to what's going on ?
6 replies
-
I've noticed the same thing when I tried importing tables to another database. I had to duplicate the database and then delete the tables I didn't need. I'm just confirming that you are most likely not doing anything wrong.
-
Could you develop an alternative stragegy, to maybe archive a section of a table periodically, either by moving records from a live table to an to an archive table and then deleting the archived live records, or by using a flag in the live table and filtering to only show live data. The filtering could be done in the Readble if section of the table so it doesn't involve seerching the list for just the live records each time.
Regards John
-
Thanks for your comments guys. I ended up pooling all the records into a single children table, I added service description fields that were assigned a tag automatically. When needed, records are filtered and recalled using those tags from a parent "service table" that contains all the associated functions. Each function just has to look for the corresponding tag in the record (select MyTable where MyField like "MyTag"...). To account for large amount of records in the pooling table impacting the system performance, the records are sorted into different pooling tables, depending on their tags (QI - 2021, QII-2021, etc.), flicking a switch in the parent table looks for records in specific subtables. That simplified things a lot and increased the overall flexibility!
-
Having the same problem. I need to use part of a table in another table (formulas etc) and rather than have to recreate it all from scratch, I thought I'd duplicate the table and simply remove what I don't need in the new one - leaving the old one intact and that I can then delete those parts that are now in the new table.
But duplicating a table is hopeless. I first tried deselecting the fields I wouldn't need, but eventually simply tried to duplicate the whole thing, but no matter how I tried, Ninox screws it all up. Formulas end up pointing to non existent or the wrong fields, when the actual fields they should point to are there.
We know that the fields we see in Ninox are an overlay and linked to the actual SQLite columns, but when duplicating a table, Ninox is losing or mixing up many of those links and since this seems fairly random, it makes the new table useless. Having to go through everything and correct all the errors introduced by Ninox means it will be quicker to just create a new table and re-create all the complex formulas from scratch, which totally defeats the point of having a 'Duplicate Table' function in the first place.
Anyone raised this issue with Ninox support?
-
UKengGB - short answer YES (raised it) Ninox response todate is "null" "nowt" zero etc. Most time you get the standard answer of thank you for informing us - we will put it on the back burner and by the way thank you for your patience !!!
The ball ache of trapping screwed formula refs is a real pain. Its bad enough looking at C7 := etc when C7 was previously a valid fieldname !!!! but when it gives you another field name as stated by Vluethen it is not always easy to spot espicially if that field name means the formula script works but give you mixed up data in other fields.
I'm with John on this - I have given up moving files into an archive and deleting where I can as found it much better to use archive flags and filters and wher I do have archive I have removed formulas so that the data is staic data and I simply pull it as needed
-
Do "We"?
Do you have the Ninox Mac app? If you have SQLite databases, if not you can find an example online, you can quite easily open one with a text editor and compare it to a Ninox database file. I think it might be easier to parse their JSON format and simply deliver it to their HTML interface than convert it to SQLite and back.
Content aside
- 2 yrs agoLast active
- 6Replies
- 391Views