Selecting records referencing second database

I need to “select” records referencing a second database.
Database A has 200 records with names and account numbers. I want to select/filter Database A and have ONLY the 30 records that are in Database B. Database B has some of the same fields as Database A: for example: account numbers. I want to select/filter by account number. Thanks

In Database A, you could use this statement

 select lookup("Database B",«account numbers»,«account numbers»,[Some Database B Field],"NG")≠"NG"

As long as account numbers or some other identifier is unique to each record, consider using selectrelated(.

It requires that you first create the relationship between the databases, but it’s actually pretty fast and easy to do. And even on sizeable databases, it’s very fast.

@JamesCook’s suggestion of setting up a relation is spot on. Once that is set up, the selectrecordsrelateto statement does exactly what you are looking for. This will be much faster than using a lookup( function.

As best I can tell, it is not possible to record as a procedure the linking of two databases. Assuming the two databases to link are open, I’d like to record or have a procedure linking the two databases as follows:

File > Database Options > choose Relations panel:

add (with the “+” sign) the database to be linked

then check/click (to create the “key” symbol) in the corresponding two fields

then add selectrecordsrelateto statement


Nothing in the Database Options window is recordable.

The intention of the Relations panel is that you set it up once and then can use it from then on. There’s really no reason to record it, because once it’s set up, it’s available. You don’t need to set up the relation over and over.

So your procedure would be just one line, the selectrecordsrelatedto statement. You can’t record that, because there is no user interface for that. So you have to type that into the code manually.