What you described with your global field should work.
Create this relationship:
StockMaster::Symbol = Prices::Symbol AND
Stockmaster::GlobalDate = Prices::PriceDate
This matches the current record in StockMaster to the first record in Prices that matches the stock symbol and the date entered into the global date field. The key here is to have both pairs of fields defined in one relationship.
A calculation field defined in StockMaster can then compute number of shares times Price:
NumberOfShares * Prices::Price
Thanks for your response. I have both pairs of fields described as you have outlined in my relationship. In the relationship graph when I duplicate the StockMaster table (306 records) and relate it to the Prices table (21,957 records) I receive 306 records. I expect to receive 60 (the number of records that match both the PriceDate and Symbol). When I duplicate the Prices table (21,957 records) and relate it to the StockMaster table (306 records) I receive 21,957 records. Again, I expect to receive 60 (the number of records that match both the PriceDate and Symbol). An ideas on what I am doing wrong?
Don't duplicate the table occurrence. Just drag from StockMaseter::Symbol to Prices::Symbol, then drag from Stockmaseter::GlobalDate to Prices::PriceDate in the same occurrence box. You can double click this relationship line to see that you have two pairs of match fields defined in the same relationship and it should look pretty close to what I put up in my last post here.
Thanks for your resonse.
When I click on the relationship line I see:
StockMaster::Symbol = Prices::Symbol
AND Stockmaster::GlobalDate = Prices::PriceDate
It has to be something pretty simple I am doing wrong. I tried exactly what you said, but when I look I receive 100% of the records not just the filtered ones. Any ideas on what it could be?
Since the relationship looks right, we'll need to look at the design of your layout.
On what table occurrence is your layout based? (This will be the occurrence selected in "show records from" in Layout Setup...)
You indicated that a "filtered portal will not work". Does that mean you do not have any portal here or did you set up a portal based on this relationship?
What I was trying to do was to create a layout that would show only the filtered records. I could filter the original layout in a portal but I preferred having the layout filtered so only the records I want are showing.
I think I have a wrong understanding of how a new Table Occurence and Layout work, probably because I keep thinging back about how it worked in SQL. It must be in the design of my layout, however I not sure how to communicate it thru the forum.
Records from which table, the stocks or the prices? Your layout can only be based on one or the other and then your relationship controls what data from the other table is displayed here.
Here's one possible interpretation of what you have requested:
Keep the relationships as written, start with a layout that specifies StockMaster in Show Records From. Add the PriceDate and Price fields from Prices to this layout. You can arrange your records in a single row in the body, shrink the body down to just that one row tall and view the data in view as list mode, or you can use view as table mode. Either way, you can see multiple records listed in rows with both the stock symbol, the price and the price date. (Added that last field just to make it easy for you to see that this works.)
Specify the desired date in your global date field.
Enter find mode while on this layout and enter an * into the Prices::Price field. Click Perform Find.
You should now see the records you expected to see.
What you have done is tell FileMaker "Find every record in StockMaster that has a related Prices record." Your relationship specifies that there will be a related record in prices for each record in StockMaster if both the Stock Symbols and the dates match.
This same type of Manual Find can be performed in a script and you can include other criteria besides the existance of a related Price record.
A working knowledge of SQL can definitely mislead you when you first get started in FileMaker. Filemaker accomplishes most of the things you can do in SQL in a very different manner. Often this approach is simpler, but in many cases, the reverse can be true.
If you've done some Visual Basic Programming where you assigned the results of a query to a record set object, it may help to view each table occurrence as a record set object where you can control the result set assigned to it by performing finds, go to related records, show all records and several other options in the records menu. Just as each record set object can have its own sort order and current record, so does each table occurrence.
What you will find truly odd is that the only way you can access the found set, sort order, current record of a given table occurrence is to go to a layout that refers to it in Layout setup | show records from. This "layout context specific" set up in FileMaker makes some very simple operations even simpler, but in turn complicates more involved tasks where you need to manipulate multiple found sets of records.
Here's a tutorial on Table Occurrences that you may find useful... Tutorial: What are Table Occurrences?