Look Up tables
I am new to Ninox. I do building surveys and really like the ease of carrying out a site survey and getting the information onto the system.But I have a question to see if I can get more detail added to my reports automatically.
I have created a data base called [SFG20]. I did a CSV upload. Its two columns 1st column is the asset name ie Boiler and the 2nd column is the code ie 03-01
I have a Database called [Master Asset Collection And Condition Report] that I will use for collecting the asset information. I would like when I select from a drop down (Multichoice) field say i select Boiler then the txt field called SFG20 autofill to the correct SFG20 code.
Is this possible? And how would I do it?
ps I tried to insert images from my ninox. But for some reason what ever I did it made squashed them and it wouldnt change them.
Thank you for your help
Yes you can lookup values dependant on your drop down selection choice - at the moment not sure if you are saying boiler 1 selected may then have one or more SFG20 options to lookup and autofill. ie Ideal Boiler might have an SFG20-1 - SFG20-2 etc.
Loading images has been broken for while and as of yesterday you are able to load them again. Don't worry about image appearing squashed- Once you have loaded and saved - it presents okay.
(Agree what ever changes you make it still is squashed (Ninox still need to fix this!)
Hi Mel. Thanks for the reply
The SFG20 database is a simple 2 column spreadsheet.
Column 1 Column 2
there are a couple probably 200 lines
In my survey database, I can put everything that is in Column 1 into a multiple choice field to be selected. I have another field in that database (at the moment just a txt field) but can change that to whatever is needed for this to work.
I would like that additional field to be auto-populated. So in the multiple-choice field I select [Boiler] the SFG20 field auto selects [03-01]
Looking at this - ther might be a better way
as doing a giant combo drop dowm might have limitations - ie having to add extra names etc. Might be better to have an Inventory table listing both the name an code
and linking that table to your form....
I've done a sample so follow this as a test (igmoring what you have so far - ie do a another new APP for a test
Create new database (eg i've called it plumbing)
create Table 1 - INVENTORY add 2 text fields ItemName and SFGcode
and fill it with your sample data lines (boiler in. itemname and 03-01 in SFGcode.... just do 3 or 4 lines for now
Create Table 2 - SURVEY - at the foot select link to and choose INVENTORY
now add anothet text field called SFGcode
Still in edit mode click to edit the link field . select more and show as and select ItemName
in trigger after update copy this code
let myC1 := Inventory.SFGcode;
SFGcode := myC1
edit SFGcode field and in in Trigger after update
copy this code
if SFGcode = null then
SFGcode := Inventory.SFGcode
This lets you clear the sfgcode and reload linked data - so if you want you can overtype info and clear and it will redo the lookup
exit app then re go back in and select SURVEY table and clink iside the link box (itemname) this will open up the rlated table - you can pick one/search /add more products etc.
when you have select it it will return and fill in SFGcode for you.
whne you click the box again you will see it linked.
the funny symbol at then of this box is so you can break the link and slect another item
you will see it ---makes sense when you do it
I'll keep my sample for a few days - just in case !!!
Maybe Sean/Steve etc could offer a btter solution directly with the drop dowm combo but I think you ill run into difficuties tryig to populate big lists maintenace wsie and they are not dynamic (well not in Ninox anyway)
It would be great if Ninox allowed you to direcly load a data set into a combo from a table list !!!
Keep holding on for a few weeks, the new version will contain a dropdown list based on a table.
Have you seen some release notes then? ... if so where
Thanks Mel. That worked. I created the database as you showed.
I then tried to input those formulas (edited for the file names on my other database and it didn't like it!
So I built my database around the working formula. i also inputted all the SFG20 codes uploaded via CSV and it works.
So next problem is I want to get a 2nd table of data to do something similar. Its called CIBSE. Basically it is a table of life expectancy of equipment. Ie a commercial boiler should last 20 years.
I have loaded this data the same way into a new Table called LIFE.
If I try and adapt the formulas to suit the names it doesn't work. I must be doing something wrong.
As above I want to be able to select from a drop-down the exact bit of kit (already uploaded on the table) ((Say Commercial Boiler)) as you did with the Inventory. then a file to auto-populate with the years (say 20)
I would love a second field to look at today's date but only the year (2021) then do a calculation.
Years Left = CIBSE life - ((today's Year 2021) - (Asset DOB 2000))
Years left = 20 - ((2021)-(2000))
Years left = 20 - (21)
Years left = -1 (this number I want to go into the text field) in this example the asset is 1 year over life expectancy
But again the formula section I cant seem to get to do this.
So I have these tables
INVENTORY (this is sorted and is the SFG20 data)
SURVEY (this is the one I will use for carrying out the survey and where all the data I input will go)
LIFE (this is the CIBSE data)
Within LIFE I have 2 columns again CIBSE & YEARS
Thanks again for your help.
Hope I have intepreted this correct.. as i was not sure wher you wanted to out the asste DOB field yu mentioned - but you surley have to have t somewher I have popped it into the survey form
add new table (Life) 2 fields Kit (text field) and Lifesan (Number field)
add some dat ie
Commercial boiler and 20
Fan and say 5
Goto and edit Survey Table
select link at foot and choose Life
in trigger after update on this link field copy this code
let myC2 := Life.Lifespan;
Lifespan := myC2....
also change show as to Kit
Now add another file called lifespan (number field)
in trigger after update copy this code
if Lifespan = null then
Lifespan := Life.Lifespan
add a new field AssetBOB (date field)
add a forumla field - i've called it lifeLeft
add this formula to it
Lifespan - (year(today()) - year(AssetDOB))
Save - exit and go back in...
So this gives you the survey frotm linked to 2 other subforms !