disappearing formulas

More specifically, a formula field with a 2 line formula (set a variable and use that to get the desired data) will be discarded and not added to the table list view. Instead, use a simple formula like a single column and it IS added and switching between 'admin' mode and back or viewing a different table does not discard the formula column.

Change the simple formula to the 2 line one that is actually required and the table displays as it should (so this change does not cause it to be discarded). However, if I now simply view a different table and then return, the forumla column has been discarded.

????

I did state (clearly I thought) that I WAS in 'admin' mode and had exited that to be sure the configuration was then saved. Made no difference.

That also does not explain why when trying to add the formula column (a formula with no errors and that later works perfectly) the last OK of the dialogs would then return me to the list but the column had NOT been added. No matter how many times I tried, 'admin' mode or not, the column would not be added and all work wasted. Do it again with a simple formula would work and then I could change it to the actual required formula which would stick. Until I viewed a different table and on return the formula column was gone. This occured on both tables.

This is nothing to do with not being in 'admin' mode. I am well aware of the importance of that. But that's not it.

Yesterday the formula columns (1 in each table) managed to last a little longer. This morning still showing in the displayed table (there'd been no redraw), but switch to other table and formula column is gone. Likewise switch tables and hey, formula column is gone.

So, is this a trial limitation, that formula columns won't save even in admin mode, or is this a bug in the web version? No idea if also a bug in the app version as there's no trial for that.

No, the relationship is 1:N (in the right direction), but the crucial point is that I cannot add the column containing the actual required formula, but have to add it via a simpler formular which works and then I can edit to the required formula which works perfectly as it should. So the formula is 100% correct. It works and displays the exactly correct data from the parent table, for each row. From which we can deduce the formula IS correct.

However, view a different table and on return, that column has disappeard from the list.

As I'm only just starting to test Ninox, I could easily believe I am doing something wrong, but the only way configuration is lost should be if not in 'admin' mode at the time (whcih I have repeatedly ensured I am and then exited to save the config) and also that it refuses to add a column that I am indeed able to create via a more convoluted process and which then works perfectly, which rather suggests that there's nothing wrong with the formula.

Let's be honest here, if software refuses to do something it is supposed to do and which does actually work, it pretty much HAS to be a bug. If that same software decides of its own accord to throw away part of a working configuration, that also has to be a bug. Unless anyone can shed any light on what arcane payers I need to conduct to make Ninox behave.

Any relationship between 2 tables is by definition 1:N (or N:1). The foreign key column in a child table can ONLY store the primary key of one single parent table record. The only way to obtain N:N is to use a third table to link the other tables. That could then be wastefully used as 1:N (N:1) if desired, but its real function is to provide a N:N relationship. A simple relationship between 2 tables can never be N:N and Ninox cannot consider it to be so.

Of course multiple foreign keys could be used, but that requires modification of the schema to change the value of N and that is unacceptable as a database practice. The actual value of N should be determined solely by the data.

As far as I can see, Ninox does treat 1:N correctly and the linked columns can be seen. So I'm not sure what Fred means by saying Ninox treats every parent/child relationship as N:N as that is impossible with just 2 tables.

With regard to the problem I am experiencing, I am currently discussing this support as Ninox is doing something wrong.

The formula I was using is:-

let marqueid := marques;
(select marques where Id=marqueid).name;

This was in the list view of the 'models' table, a child of the 'marques' parent table. When (in 'admin' mode) adding a Formula column to the 'models' list view the above formula showed NO errors, but on clicking all the 'OK' buttons to return to the data/list view, the column had not been added and returning to 'Edit fields' showed it was just not there. So it was as if I had clicked 'Cancel' instead.

However, if I used e.g.:-

let marqueid := marques;
marqueid;

This column would be added and I could see the correct Id number of the 'marques' record associated with each of the 'models' rows.

I was then able to edit the formula to what I originally wanted and 'marques.name' was now displayed in that column of the 'models' table list view.

This HAS to be some sort of Ninox error as at one time it refuses to utilise the entered formula, then by a different route I am able to use that same exact formula and it works perfectly. The only conclusion is that there is some programmatic error in Ninox, aka a bug.

Then, having exited 'admin' mode, that view should be saved, but if I switch to a different screen (e.g. a different table) and back, that Formula column had vanished. So again, barring some sort of 'trial' mode restriction (which apparently there is not) there is no way that Ninox should simply delete a column that was actually functioning perfectly. There is something screwed up in Ninox's logic here. I suspect it is sometimes mistakenly thinking that the query is returning multiple rows, which it is not as I know the data (only testing with half a dozen rows at present so easy to ensure it is correct) and clearly it is NOT returning any array since it was working perfectly as intended before changing screens.

So that is what is occuring, but I have now avoided the problem once I realised I could add the 'marques.name' column directly to the list view jsut by dragging it in the view's 'edit fields' screen. I had not originally noticed I could expand the parent table and simply drag the required field into place, just like any other field of the child table. So my lack of Ninox experience showing there - now I know better. However, my journey to the correct solution has exposed what has to be a Ninox bug as if an application can perform a function perfectly, it must perform that same function perfectly EVERY time and not behave in a different manner at different times. That is poor programming code and is of course, a bug.

I am not trying to have a go at Ninox as I am pleased with how it is working so far, but it is not perfect. 

Interesting Alain that you say Ninox "'select' statement *always* returns an array". it does explain some problems I've been seeing and initially cured with the use of e.g. first(), but…

This is not standard SQL behaviour. When 'selecting' a single row, e.g. by its id, true SQL should only return a single item and it is up to the 'user' to ensure the validity of what they are trying to do. I suspect Ninox is trying to avoid ambiguity in the name of simplicity - mistakenly in my opinion.

Also, the above code does actually work when first used and Ninox happily displays the Formula column using 'select' with no first(). Correctly for every row. It makes no mistake on this, apparently satisfied only one item is being returned. However, simply changing screens and back causes it to throw away the entire column for the reason I ascertained and as you have confirmed, it thinks an array is being returned. No matter which behaviour Ninox adopts, whether always an array or only when there are genuinely multiple records being returned, there are 2 problems with what actually occurrs. Firstly the success or otherwise of the formula should ALWAYS be the same. Display perfectly one moment then deciding differently after merely switching screens and back is clearly WRONG. It seems Ninox is executing different code at different times when displaying a column.

Secondly,  deleting the column without any error message and/or chance for the user to go back and edit the formula is simply a poor UI, very poor.

Only after puzzling with this for some time did I realise I could simply select the desired fields directly without needing any 'select' at all. I had initially been following examples I had seen probably on this forum.

I have since experienced this same column deletion problem when using numbers(MultipleChoiceDynamicField). This also will display initially, but the column will have vanished after switching screens and back. It is content to display those numbers at first, but not later. Wrap it in a text() function seems to solve the problem.

In both these cases, the problem is not so much that it behaves one way or the other. The issue is that whichever behaviour it adopts, it must ALWAYS adhere to that and behave consistently. Only then can it be documented so everyone knows and can deal with it appropriately.