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?
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.
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
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.
User | Count |
---|---|
36 | |
31 | |
30 | |
20 | |
17 |