Linked record field whose contents are autofilled with the results of query on another table

#1

I have a base with two tables:

Checks

  • Check number (string)
  • Amount (currency)

Itemizations

  • Check number (string; primary; the view is grouped by check number)
  • Item name (string)
  • Amount (currency)

Part of the purpose of this table is to ensure that the checks I received are for the correct amount (otherwise I need to call accounting to resolve the discrepancy). If everything is correct, then the query

SELECT "Check number", SUM("Amount") AS "Total"
FROM "Itemizations"
GROUP BY "Check number"
ORDER BY "Check number" ASC

should produce the same result as

SELECT "Check number", "Amount" AS "Total"
FROM "Checks"
ORDER BY "Check number" ASC

I’d like each record in Itemizations to have a link to the corresponding check, that is, a linked record field whose contents are

SELECT {a link to the record}
FROM "Checks"
WHERE "Check number" = {Check number}

So essentially, I want to perform a left join, and the field I want to join on happens to be a primary key in both tables. I don’t want to have to manually enter both the check number and the linked record. Is there any good way to do this?

(Perhaps the best way is to do away with the primary key in Itemizations and instead just group by the linked record and have the primary key be some dummy field.)

In general, the ability to do record lookup based on the contents of an existing field would be useful.

#2

That’s what I would suggest. In cases where I can’t use the primary field for one reason or another, I’ll often set it to the Autonumber type. It auto-fills with each new record, so I don’t need to touch it, and it takes up very little real estate on the screen.

1 Like