Help

Re: Page Designer -- Lookup related record + Display record details

Solved
Jump to Solution
844 5
cancel
Showing results for 
Search instead for 
Did you mean: 
CAHerrmann
7 - App Architect
7 - App Architect

I have a project management base I’ve built over the years. I store project specific information in a ‘Project’ table, which relates to a ‘Proposals’ table. Each project can have multiple proposals, but each proposal can relate to only one project.

Each proposal contains a linked record to an organization in a separate ‘Organization’ table. When I check a proposal as awarded, the record in the ‘Project’ table then displays the corresponding awarded organization (we bid out to several general contractors per project, but only one is awarded).

What I am trying to accomplish is a page designer block that is based on the ‘Project’ table but can display the fields from the organization referenced in the awarded proposal, without having to pull in the contractor information into the ‘Proposals’ table and then into the ‘Project’ table.

I hope there is a simple solution, but I have not found it yet.

@Jeremy_Oglesby – any chance you have an idea on this?

1 Solution

Accepted Solutions
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

By “simple solution”, you mean not having to do the legwork of creating all the Lookup fields required for pulling the information you want?

Because, in general, I would call exactly that the “simple” solution – it is the most straight-forward and least error-prone way to get what you want… even if it requires some tedious up-front set up work.

Another potential solution is to use the Scripting block, in conjunction with a Button field, and write a Script that will query the data you want out of the “Organization” table based on awarded “Proposals” in the “Projects” table, and write it into the fields you want to use for your Page Designer block. Writing a script to do that would probably take just as long as creating all the chained Lookup fields, but would save you all the intermediary Lookup fields littering the “Proposals” table.

You could also try to minimize the amount of Looking up or Script Querying that needs to be done by using one or more Formula fields to concatenate any relevant information you want out of the “Organization” table into a smaller number of fields that need to be Looked up or Queried.

See Solution in Thread

6 Replies 6
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

By “simple solution”, you mean not having to do the legwork of creating all the Lookup fields required for pulling the information you want?

Because, in general, I would call exactly that the “simple” solution – it is the most straight-forward and least error-prone way to get what you want… even if it requires some tedious up-front set up work.

Another potential solution is to use the Scripting block, in conjunction with a Button field, and write a Script that will query the data you want out of the “Organization” table based on awarded “Proposals” in the “Projects” table, and write it into the fields you want to use for your Page Designer block. Writing a script to do that would probably take just as long as creating all the chained Lookup fields, but would save you all the intermediary Lookup fields littering the “Proposals” table.

You could also try to minimize the amount of Looking up or Script Querying that needs to be done by using one or more Formula fields to concatenate any relevant information you want out of the “Organization” table into a smaller number of fields that need to be Looked up or Queried.

Jeremy,

I suppose my hope here was that I could make the Page Designer behave the same way for look up fields as it does for linked records.

Here is the situation:

Projects > Proposals > Organization

When proposal is marked awarded:

  • Projects pulls in Organization from Proposals
  • This displays the same way as a linked record (I can click on the looked up field and open up the corresponding record)

In Page Designer, if I have a linked record, I can access the information within that table and display it as a table on the page I am creating. It does not work this way with the look up field, despite behaving in a similar manner.

I can, of course, create a series of lookups in the Proposals table to pull in the desired information (Organization Name, Address, Phone Number, Website, etc…) and then look that up again in the Projects table, but that seems like an unnecessary step, considering the information is already present.

My assumption is that I am missing something simple to make this work. The alternative is a bunch of tedious work and extra fields just to pull in the same information as already displays when I click the link in the look up column.

The Scripting block could be used to establish a link to each “Organization” that has an approved “Proprosal” in a new Linked Record field in the “Projects” table. Then you can just use that Linked Record field to output your table in the Page Designer block.

That script shouldn’t be too terribly hairy to write - and linking it with a Button field would allow you to just click the button to establish the links any time you “approve” a Proposal.

I think that’s about the simplest way I can think to make this happen with Airtable’s current toolset.

Thanks, Jeremy. I suppose I need to dive into the scripting block a bit. I have coded in the past, but minimally and a long time ago now. I have to admit, I feel a little lost with this added functionality.

Apart from the example scripts from Airtable found here, do you know of any examples and/or tutorials I can refer to?

Related to this topic — do you know what happens if I change the name of an entity referenced by a script? Is it updated in the script, or does that break the script?

Actually, I just tested this – I have a sample script set up to run from a button in my table ‘Projects.’ This runs fine, but if I change the name of the table to ‘Project,’ it breaks everything.

So… it seems that the scripting block doesn’t have the same level of live updating that other areas of Airtable have (e.g., formulas – if I change the name of a field in a table, the reference in a formula is also updated). This is problematic, and a major deterrent from use, considering the ease with which fields and tables can be deleted and renamed by any user.

Hi @CAHerrmann - I started a blog with some introductions to Airtable scripting - have a look here

Thanks so much, @JonathanBowen!