Methods for adding filtered lists to forms

[PaulBappoo]

I have 3 tables, a master BusinessPartners (BP) table, which is linked to addresses and contacts, so there can be multiple contacts and multiple addresses per business partner.

On the BP table I use filter formulas to list the associated addresses and contact records.

Then on the Addresses and contacts I use rowlinks to associate to the BP data.

Now, when I build a BP form, I want to show two lists, one with the list of associated Addresses and one with the list of associated contacts.

The BP form is an auto created form with an InputRow which takes the BP name

I added a column list for addresses and it appears to have created a header in a block who's source is $[InputRow]

and a data field who's source is something that I dont fully understand.

So part 1 of this question is what does this syntax mean?

The list control seems to reference this combination of objects

And thus provides a list of associated addresses.

So I'm a bit confused at how this mechanism works.

When I added my contacts list, the default action was to simply link to ALL contacts, rather than a list filtered by the $InputRow

So I worked out that if I create a block and a data cell and populate them as the address block and cell are populated then I can use the same reference here as in addresses and it shows me the filtered list, so that's great.

I also worked out that I can do it with a filtered source like this:

The filtered source makes sense, so part 2 of this query is, is there a recommended way of doing it and why would one method be better than another?

Finally, part 3 of this query is, are there (or could there be) any plans to allow us to type in standard SQL statements into sources?

Since it would be quite simple to add a simple select and where rather than try to learn a new and slightly strange syntax.

[Sid]

Hi @PaulBappoo,
Thanks for your question.

So part 1 of this question is what does this syntax mean?

I think you are referring to this expression: =BusinessPartners[[#Headers][Addresses]]
This expression returns the header for the Addresses column in the BusinessPartners table.

The filtered source makes sense, so part 2 of this query is, is there a recommended way of doing it and why would one method be better than another?

Both expressions are perfectly valid ways of achieving it.
The first syntax (=$[InputRow][Addressess]) is an example of dereferencing. Dereferencing enables you to access data from a rowlink's column. $[Inputrow] contains a row from the BusinessPartners table and =$[InputRow][Addresses] returns data in the Addresses column for that row, in this case a list of related addresses.
More details here:

How to Dynamically Retrieve (Dereference) a Rowlink's Column

Topic Dereferencing or retrieving a cell is similar to a spreadsheet vlookup and can save you some keystrokes when setting up tables that use similar data. Steps Build a table (we call our's Table1) with data that you would like to use again in another table. [2020-07-08_16-50-13-0000] Build a second table. Select the Person column and click the Formats button. Builder tip: Our second table is called Table2, as shown in Step 3's image. We also named the columns differ…

The Filter syntax is more flexible as it will work even if your table doesn't have a column with the relevant filtered data.
If you already have a column in your table with the relevant data, the first syntax is preferred otherwise take the second approach as that is more flexible and gets you the relevant data without requiring additional table columns.

Finally, part 3 of this query is, are there (or could there be) any plans to allow us to type in standard SQL statements into sources?

We appreciate you sharing your feedback with us. We'll submit a feature request for the team to consider in a future update.