Finds from Two Tables

[Rich M]

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?

[comment]

5 minutes ago, Rich M said:

add records to that found set based upon a field value in another table

It's not exactly clear what you mean by that. You can find records in one table based on a value in a field of a related table. You cannot have a found set showing records from more than one table.

Please provide a less abstract example, with meaningful names for the tables being used, and explain the purpose of this exercise.

 

[Rich M]

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.

[comment]

An OR search is performed by creating a separate request for each criteria. The fact that one of the searched fields is in a related table is irrelevant for this purpose. Your script should look something like:

Enter Find Mode []
Go to Layout [ Clients ]
Set Field [ Clients::SomeField ; "SomeCriteria" ]
New Record/Request
Set Field [ AdHistory::AnotherField ; "AnotherCriteria" ]
Perform Find

This will create a found set of records in the Client table that is the union of (1) clients that satisfy "SomeCriteria" in SomeField and (2) clients that have at least one related record in the AdHistory table that satisfies "AnotherCriteria" in AnotherField.

Another way to produce the same result is to perform the first find request first, then enter find mode again, create another request and do Extend Found Set [] instead of Perform Find []. But it would be an unnecessary complication in this case.

 

[Rich M]

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.

[comment]

6 minutes ago, Rich M said:

I'm using a LAYOUT that displays records from the Ad History table.

If you want to create a found set of records of the Client table, then you must perform the find from a layout of the Client table. For both criteria. Hopefully you do not have duplicate records in this table.

I am afraid that's all I can say at this point, because you still haven't explained what are you actually trying to accomplish here.

 

[Rich M]

• 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.

[comment]

Sorry, this still makes no sense to me. You cannot have a layout display records from more than one table. You need to choose between showing records from the Client table, using a layout of the Client table, or showing records from the AdHistory table, using a layout of the AdHistory table.

If you chose the latter, you can sort the records by Client, and show data (not records) from the Client table in a sub-summary part of the layout. You can even remove the body part of the layout, so that your result looks like a list of clients - even though it is not.

Do note that 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. IOW, your report will NOT include clients of the specified type that did not place any ads.

 

[Rich M]

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.

[Rich M]

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.