Look Up tables

P

 

Hi

 

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?

 

Thanks

 

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

 

M

Peter

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!)

Mel

 

P

Hi Mel.  Thanks for the reply

 

The SFG20 database is a simple 2 column spreadsheet.  

 

Column 1   Column 2

Boiler          03-01

AHU            03-02

Pump          03-03

Fan             03-04

 

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]  

M

Hi Peter

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

SAVE !!

Screenshot 2021-02-21 at 21.59.10

 

 

Still in edit mode click to edit the link field . select more and show as and select ItemName

Screenshot 2021-02-21 at 22.01.49

in trigger after update copy this code

let myC1 := Inventory.SFGcode;
SFGcode := myC1

click ok

edit SFGcode field and in in Trigger after update

copy this code

if SFGcode = null then
SFGcode := Inventory.SFGcode
end

click ok

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.

Screenshot 2021-02-21 at 21.59.46

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

 

Mel

 

 

 

 

 

 

 

 

 

 

M

I'll keep my sample for a few days - just in case !!!

 

M

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 !!!

Ninox Profile

Keep holding on for a few weeks, the new version will contain a dropdown list based on a table.

M

Steven

Have you seen some release notes then?  ... if so where

P

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))

ie

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.

 

 

 

M

Hi Peter

 

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

Save(ok) it

Now add another file called lifespan (number field)

in trigger after update copy this code

if Lifespan = null then
Lifespan := Life.Lifespan
end

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...

 

Screenshot 2021-02-22 at 14.05.55

 

So this gives you the survey frotm linked to 2 other subforms !

 

1 2
Reply