Queries and References

Ask for Help
,
1

Scope: I have 2 tables, Inventory(BigQuery) & Parts(AirTable)

Parts Table
I created a reference to the Inventory Table because I’m only interested in available Inventory and I’d like to create a view for only parts associated with available inventory.

In the Data Editor I see the connection is good, there are multiple referenced records however when I use the Reference as a source for a collection only 1 record appears.

I’ve also tried this using a Query.

2

When you say reference, do you mean a relation column?
Can you show how you configured it?

3

Yes, Relation.

IMG_0228
IMG_02281116×892 70.5 KB

4

You need to check the Match multiple box.

5

I’ve also tried that, with no luck and from my understanding Match Multiple is for a 1 to Many situation.

6

Yes, that’s correct. I thought that’s what you wanted?
Maybe I need a little more context. Can you tell me how you determine whether or not any given part is available?

7

It is based on whether or not Inventory is available.

I’m essentially using the relation or query as a filter for the parts table

If Part has a record match in the Inventory table show in the collection

8

ah, okay.
So the single relation that you showed earlier should be good enough.
All you need to do is use the Parts table as the source of your Collection, and then filter it to only show rows where the relation is not empty. (the filter is applied to the Collection itself).

9

I’ve also tried that but I’m unable to filter using a relation or query column.

10

oh, yes… ugh, that will be because the source table is a Big Query table.

That makes it tricky. I’ve not used BQ myself, but I have done a lot of work with Big Tables, which do have similar limitations. So I might be able to help with a workaround.

I suppose - creating a separate BQ table that’s already filtered isn’t an option, because the Inventory table is connected to Airtable, right?

11

@Peter_Berumen - okay, I’m thinking that your best approach here might be to use the Inventory table for your Collection instead of the Parts table. I’m assuming that there is only one row per part in this table, so it’s essentially already filtered, yes?

A couple of things you might need to do:

  • If there are specific attributes of a part that you want shown in the Collection that aren’t in the Inventory table, create a relation column in the Inventory table that matches the PartID with the PartID in the Parts table, then use Lookup columns to fetch the values you need.
  • If you want the Collection Item click action to navigate to the Details screen of the associated part in the Parts table, you can modify the default action to do a Show Details screen, using the aforementioned Single Value as the source.

I think that should work for you.

1 Like