Finding a Specific Related Record

[Rich M]

I have a one-to-many relationship in play here. The ADVERTISERS table contains the ad clients (the "one"). The AD HISTORY table contains all the ads a client has run over the years (the "many"). The tables are related via an account number field.

• At the beginning of the script, I generate a variable containing the issue for which I want to see a report. This variable is named $$RunNumber.
• I can successfully find all the ADVERTISERS that have placed an ad in the chosen Run Number.
• I want to display one field of data from the AD HISTORY table in the report.
• The use the "Go to Related Record" function to get to the AD HISTORY table.
  Here's where the problem starts.
• I want to find the ONE record in the AD HISTORY table that matches the $$RunNumber variable, and display a field from that record on another layout.
• There is a field in the AD HISTORY table that contains the Run Number.
• The script finds the FIRST record for each client in the AD HISTORY table.
• The report layout properly displays the AD HISTORY data, but it's the wrong data.

How do I find the specific record in the AD HISTORY table that matches the $$RunNumber variable?

[comment]

I am a little confused about what is the actual result you are looking for. You mention a report; I suspect the easiest way to produce the report you want is to go to a layout of the AD HISTORY table, find all the ads in the issue of interest, sort them by their parent advertiser and show them in layout with a sub-summary part by advertiser showing data from the ADVERTISERS table, and the body part showing the ad details.

Note that you speak of "the ONE record in the AD HISTORY table that matches the $$RunNumber variable" - but at least in theory an advertiser could have more than one ad in the same issue.

 

Edited July 27, 2021 by comment

[Rich M]

The way I built this database, an advertiser has only a single record in the AD HISTORY table for any specific Run Number of the magazine. The Run Number field contains that information. If an advertiser has more than one ad in the issue, the Ad History record for that Run Number will have multiple line items contained within the record.

The "report" is actually a page of small ads that will appear in the back of a magazine. You helped me with the FINDS related to this problem back in June in THIS THREAD. That part of the script now works perfectly, thank you very much!

The "ad" (report) contains basic contact information for each advertiser in the Run Number selected (Name, Address, Phone, and Web URL) along with a calculation field (MAGADPAGE) in the Advertiser Table that says, "See Our Ad on Page nn."

It is that "page nn" that is giving me the problem. As the script is set now, the page of ads will properly display a page number in the MAGADPAGE field, however the page number displayed is from the FIRST (oldest) related record in the AD HISTORY table. I want it to display the data from the ONE record in the AD HISTORY table that matches the $$RunNumber variable. That is what I cannot figure out how to do.

Here is the script:

It is line 4, right after the first "Loop" command, where the problem is. Everything else in this script works OK.

Edited July 27, 2021 by Rich M
Added Image of Script

[comment]

When you do Go to Related Record [Show only related records ], you will create a found set of all child records related to the parent record. If you want to see only some of these records, you will need to either constrain the found set or use a relationship that makes only some records related - e.g.

Advertisers::AdvertiserID = Ads 2 ::AdvertiserID
AND
Advertisers::gIssueNumber = Ads 2::IssueNumber

I am afraid I could not fully understand what are you trying to accomplish here - but I still suspect it would be much simpler to accomplish it from the Ads table, after  performing a find for the issue number.

There is a reason for this: you are trying to isolate a specific record among the "many"; I say that when you are on the "many" side, there is only one "one" and you already have all you need to get data from it.

 

[Rich M]

SOLVED!

Once again you pointed me in the right direction. The key was this: "...it would be much simpler to accomplish it from the Ads table, after  performing a find for the issue number..."

It took a little head-scratching and a few trial and error runs, but I finally got it all working exactly the way I want it to run.

Thank you again Mr. Comment!