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!

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.

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?

SOLVED. Mr. Comment, the last paragraph in your post steered me in the right direction. When you said, "... if you perform a find in the Customer_Type field in the Client table from a layout of the AdHistory table, you will find ads that were placed by clients of the specified type..." the light bulb lit above my head. I had to rearrange two layouts and change my FIND requests to accommodate those changes. Once I did that, it worked the way I wanted it to. Thanks very much for taking the time to help me.

I used the wrong term in my first sentence. I meant DATA, not RECORDS. You can see that I’m displaying related data in the image I included. This script is used to produce a page of small ads, something I’ve done successfully in this database for years, using only a single search/find. It is the need to perform a find on two related fields that is causing the problem here. The layout has only a body…no header, footer or sub-summary section. Again, this has worked perfectly for years doing only a single find. Your last paragraph gives me a clue. I’ll dig into it more tomorrow.

I am trying to run a report using a layout that displays records from the Ad History and the related Client table. The records in that report should be the results of TWO finds: 1 - A value in the Customer_Type field in the Client table 2 - A value in the Ad Run Number field in the related Ad History table There are no duplicate records in any of the tables.

I am making progress...but I still have a problem here. The scenario is this: I'm using a LAYOUT that displays records from the Ad History table. I can successfully find a correct set of Client records that match a specific magazine issue number in the Ad History table. This find works properly and the display of the records on the chosen layout is correct. When I do a second find as you suggested above, with a group of records based on a field in the Clients table, the problem surfaces. (The problem is exactly the same if I use the Extend Found Set script step.) Some of the records added in the second find (or the Extend Found Set) have previously run ads, so they have records in the Ad History table. None of those Ad History records match the initial magazine issue number find from step one, however they show in the report multiple times. The number of duplicates corresponds to the number of past ads they have run. I have tried swapping the finds and doing them in reverse order from what I have stated here, and the result is the same. I am at a loss to understand why I am picking up the extra duplicate records in this report.

Yes, the way I stated it, I was not clear enough. There are two tables involved - Clients and Ad History. I want to find Client records based upon a logical "or" search. One of the search fields is in the Clients table. The other is in the Ad History table. I want to display records from the Clients table based upon this two-field "or" search from two different tables. Does that make more sense? I hope so. I should have stated it this way in my OP.

I am trying to build a FOUND SET based upon field data from two different tables. The FIND logic would be a logical "or" search. From within a script, I want to find a set of records from one table based upon a field value, then add records to that found set based upon a field value in another table. Is this possible? If so, what's the process?

Fitch and comment, thanks so much for your help. This project is done, up and running fine, using your suggestions. MUCH easier than my old way of doing this!

OK! GOT IT! The BIG LIGHT BULB just lit up above my head! Thanks very much! Now I've got work to do...

Thanks for the quick reply, Fitch. I'm not sure I fully understand what you've told me. Can you explain in a bit more detail? Do you mean I should set up a separate table for each company and it's logo?

What is the best way to choose a company name in a drop-down list in one field and have another field display that company's logo? I originally coded this process many years ago using a calculation field and series of "case" statements within the field definition. That worked fine when there were only a handful of choices. However, now that this list has expanded to 20+ companies, adding new companies and their logos to the list is extraordinarily cumbersome. The logos themselves are stored as Global fields in a related table. There are four separate fields within each customer's record where I need to do this. There has to be a better way to do this via a script instead of a calculation field, but I'm not sure of the best way to approach this. Help will be appreciated!