Select Parent record based off two fields

Layton,

This is an interesting question. I've provided a TLDR at the bottom.

Assumptions
General Table Structure:

SQL Version of Search for Records:

SELECT f.Frequency_Blocks
FROM Frequency_Blocks f
JOIN Channels c ON c.Related_Frequency_Block = f.Record_ID
WHERE
  c.Center_Frequency >= f.Start_Frequency AND
  c.Center_Frequency < f.Stop_Frequency​

Steps for your question:

1. Record Added/Modified in Channels
2. Search for single matching range in Frequency Blocks
3. Edit Record from Step 1 in Channels, and make Related Frequency Block = Record from Step 2

Potential Solutions
Conditional Lookup Fields (No go)
In similar situations, I have used conditional lookup fields matching a field on the Parent Record with a validating formula field on the Child Table. For example the following would only show the most recent 100 Child Line Items to select for a Parent Invoice.

Parent [Invoice Number Show] == Child [Line Item Show]
Parent [Invoice Number Show] == "X"
Child [Line Item Show] == IF(Line Item Show > [Max Invoice Number] - 100, "X", "")​

However, that type of solution won't work for your use case (matching on a range) because there are an infinite number of potential combinations in a range.

Coding or Microservices (maybe)
Unfortunately, QuickBase doesn't yet have a "search" feature I know of in a native feature (Automations, Webhooks, etc.) that would allow you to do Step 2. You would need some sort of automated solution (code page, microservice provider, QuickBase Partner, etc.) to do the above query and match everything up. If you're up for coding something, QuickBase's API_DoQuery is very useful.

Looking at Problem a Different Way (recommended)
You might consider adding a step to your workflow where users manually select the Frequency Block for the Channel. There are some simple UI items to make this seem less burdensome. For example, you could create a formula text field on Frequency Blocks called [Frequency Range].

List(" - ", [Start Frequency], [Stop Frquency])​

You could then use [Frequency Range] as the Default Record Picker (found under Table Settings - Table Structure - Advanced Settings - Identifying Records) in Frequency Blocks. When users are on Channels, they'll now select something like "12 Hz - 20 Hz" for [Frequency Block]
​
TLDR:
Based on your use case, there's not currently a solution I know of for what you are trying to do. Coding might work, but a more looking at your problem a different way may give you a more simple solution.

------------------------------
Evan Westbrook
PRIME Developer
Harder Mechanical Contractors Inc.
Portland OR
------------------------------

(Evan I think you're on the right track, though we can get away without adding the extra step of the user manually selecting a range!)

Hi Layton, I've helped others roll up data under ranges or time periods and you can do this with a little setup. I'm making the same assumptions Evan did, and I'm also assuming here your Frequency Blocks table has a text field giving the range, and that the range field is the key field. You'll need that to automatically associate the channels with the proper frequency block.

On the Channels table, you'll need a text formula to calculate the proper frequency range, plus an automation to take the formula's value and update the reference field (which will relate the channel to the proper frequency block). So when a new channel is added, the automation will allow the new record to update itself.

The formula will look something like this:

If(
[Center Frequency] > 75 and [Center Frequency] <= 100, "76 - 100",
[Center Frequency] > 50 and [Center Frequency] <= 75, "51 - 75",
[Center Frequency] > 25 and [Center Frequency] <= 50, "26 - 50",
[Center Frequency] > -1 and [Center Frequency] <= 25, "0 - 25")

The automation will be set up like this:

WHEN a record is added to the Channels table,
MODIFY records in the Channels table.

In the modify records action, click the "Filter Records" heading and change the filter so it looks like this:

(Record ID#) (is equal to) (the value) (Trigger:Channels - Record ID#)

Finally, under "Select fields and specify values", change the line below so it looks like this:

(Related Frequency Block) (Copy value from another record) (Trigger:Channels - Center Frequency Formula)

------------------------------
Brian Cafferelli
------------------------------