search/filter records based on its associated records in another table

[San_Tou]

I have two tables

tbl_account which store all basic information about a Customer or supplier account: reference, property, customer/supplier names etc

tbl_account_details which store all information that is specific to that account, e.g. the case worker's name, which council we are dealing with, what type of building it is, what services are we offering etc

the fields are:

ID, type (which reads another reference table), description

that way I can store flexibly much information, like:

Council: Manchester

Case Worker: John Smith

Services: building work

Services: licence application

etc

This pose a challenge: how can one filter down a list in tbl_account, based on a record in tbl_account_details?

e.g.

I would like to filter the tbl_account records

where the tbl_account_details linked to, has a [type] = 1 , and description = "John Smith"

is that possible? Is there a better way to achieve that?

[TeeSee1]

Hi

Could you clarify what you mean by "filter down a list in tbl_account, based on a record in tbl_account_details"?

By default, the system generated view of the detail table offers filtering capabilities on what you mentioned. Once you apply the filter, your detail records contain the ref field to the account table, which you can use to get account detail. 

[San_Tou]

Apologies for the late reply.

tbl_account has two fields

accountID (key), title

tbl_accountDetails has four fields

ID(key), parentID, title, descriptions

there is a one-to-many relationship between ID (many) and accountID (one)

I would like to filter tbl_account, to show record that

if any of the record in tbl_accountDetails is:

title = name; description = John Smith; parentID matching the tbl_account ID

I hope this makes sense

[TeeSee1]

A demo app which I trust does what you want.

For this I created a table Filters with two (you can have as many as you want.) filters (type and value pairs) and a VC to get the result of the filters.

The VC expression is

INTERSECT(
SELECT(
 accounts_details[account_id],
 OR(ISBLANK([type_id_1]),
  AND(
   [type_id]=[_THISROW].[type_id_1],
   [value] = [_THISROW].[value_1]
  )
 )
),
SELECT(
 accounts_details[account_id],
 OR(ISBLANK([type_id_2]),
  AND(
   [type_id]=[_THISROW].[type_id_2],
   [value] = [_THISROW].[value_2]
  )
 )
)
)

The filter to select a type is set like this...

And in the suggested values of the value columns I have 

SELECT(
 accounts_details[value],
 [type_id]=[_THISROW].[type_id_1],
 TRUE
)

You probably want to use enum for values also if you want to avoid messy dropdown values.

Hope this helps.