How do I use a cascaded form to get a value

Ask the Community Get Answers
1

This may be a basic question... and if so something quick and easy to get answered...

I've got an Object table with some data in which I want to use in a form / another object. It basically looks a bit like this:

Year - Type - Factor

2017 - Gas - 0.210

2017 - Diesel - 0.400

2018 - Gas - 0.250

2018 - Diesel - 0.450

I now want to have a form that writes to another Object table called Results (say)

In the form I want the user to select the Year (via a drop down - which should offer 2017 and 2018 in this simple example), then in the next field the Type (via a drop down - so offering Gas and Diesel options) and then I want the 3rd field (Factor) to be automatically populated with the number that relates to the Year and Type selected. The Factor is then used in another calculation field in the Results Table. I can do the calculation bit, but can't work out how to get the Factor field to be automatically populated in the Results Object Table.

I'm quite new to Knack, so forgive me if this is a bit of a basic question to post here. Any help would be much appreciated though :-)

2

Hi John

The simplest option, using your existing data structure, would be to create a text formula field to concatenate the Year and Type:

{Year} - {Type}

Then you could use this field as the Display Field (Object Settings) so that your user would have Year and Type (2018 - Diesel) as the values in the drop down.

You would then use an Equation field to pick up the Factor (assuming your first table were called Years):

{Years.Factor}

If you want separate drop downs, then you must create an additional table linked to Years for Fuels used in the year - with the type and factor as fields. Your first look up would be to Years and the Second to Fuels and the latter drop down would be based on Fuels linked to the Year selected - in the Show box you would select "Fuels linked to this form's Year". Both tables would need a connection from Results.

Probably not explained very well - but I hope this helps?

Julian