Please help with very simple select statement
what is the formula looking for expected end
5 replies
-
Hi Andrew -
is addnum a number field like your picture shows or is it a reference field to another table?
If it is a number field then you can drop it from the select statement. You only need the table name after the select.
select Test where Choice = "Yes"
From the looks of your picture Choice is a choice field. If you want to use text "Yes" or "No" in a choice field then you need to put text() in front of the field name.
select Test where text(Choice) = "Yes"
By default Ninox uses the number associated with the choice. If you edit your choices you will see a number next to each choice. If Yes is 1 then you could write it like:
select Test where Choice = 1
The next thing is what do want to do with the select statement. You have gathered all the records that match a criteria, now what do you want to do?
Good luck and let us know how it goes.
-
Hi Fred, Thanks for your reply. The code works in that resolves without error, however the formula returns blank.
To answer your question above, yes the addnum is a number field.There are no table relations involved. I was just trying to do the most simplest example I could think of where it would sum up the values where the record has a choice = x or y (i.e Yes or No)
When I got this working I need to place this into a larger database with a few table relation going on, but wanted to make sure I could get it working on a simple one first.
I tried both your options above with using the text(Choice) and just using 1 or 2 instead
I am obviously missing something very fundamental because i cant see why you would only need a table name, how would it know I wanted to add up the addnum field without
specifiying it.
Again thankyou for your help, much appreciated
-
Hi Andrew
A select statement in Ninox is a way for you to tell Ninox to go to a table and gather data and then do something with it. So you are on the right track if you want to find all records in table Test where Choice = Yes. Now why would you need to use a select statement for the same table you are in? Think of it this way, you want to do a sum of the field addnum which is in the same table. But a field holds data that belongs to a single record, so there is no other way to tell Ninox to go outside the record and gather data beside a select statement.
Now on to the issue of "no results". You got "no results" becuase you haven't told Ninox what to do with the results. As a teaching moment, you can modify your formula to:
concat(select Test where Choice = 1)
This will list all the record Ids of the records that match this criteria.
Now that we know what you want to do with your results you can change your formula to:
sum((select Test where Choice = 1).addnum)
I hope this helps. Let us know how it goes.
-
Thankyou Fred, that worked and also I followed your logic on a formula that is trying to sum up all records, but doing so within a single record doesn't make good logic. I guess if I was doing this within a parent table it might have worked out better for me
Thanks again
Andrew
-
Hi Andrew -
Yeah it does look at little strange to have the same data in all records. When you are ready you can look into dashboards then hyper dashboards. But you should also be aware that select statements are very slow so now is a good time to learn to not to depend on them.
Good luck.
Content aside
- 2 yrs agoLast active
- 5Replies
- 813Views