Firstly, I can't seem to display all companies within a sector using a portal as the sector information only appears in the Master list and not the Company Snapshot table.
To do this, you'd need an additional table occurrence in your relationship graph so you can link master company list to a second occurrence of itself by the industry type field. With that relationship, you can set up a portal so that if you are on a specific record in your master company list, the portal will display all other companies of the same industry type. Is that what you want here?
If so, use:
MasterCompanyList::IndustryType = CompanySameIndustry::Industry type
Where CompanySameIndustry is a 2nd table occurrence of MasterCompanyList. To created it, find MasterCompanyList in Manage | Database | Relationships, click it and then click the button with two green plus signs. Double click the new table occurrence box this produces in order to rename it from the default MasterCompanyList 2 name it has been given.
Secondly, when I have this list, how can I specify that I only want the most recent (ie. from a date perspective) record from the Company Snapshot table and not all records relating to that company.
Do you only want to see the most recent snap shot record or do you also want the ability to scroll down to see past snapshot records? In either case, you can double click the reltionship line between your two table occurrence boxes that represent these two tables and specify a sort order that sorts the most recent snapshot record to be first. If you have a date field, you can simply select this field for the date field and specify a descending sort order. If you just want the most recent record and only the most recent record, you don't need any portal, you can place fields directly from the other table (table occurrence actually) on your MasterCompanyList layout. If you want to be able to scroll down and see past snapshot records, use a portal specifying a single portal row and also a scroll bar. (You can enlarge a portal row and place fields in the portal so that this is like a mini form view instead of the default table like view.)
If "Table Occurrence" is a new term. Read this thread for more on what it means and how it can be used with FileMaker databases: Tutorial: What are Table Occurrences?
Thank you very much for your input. I have not got it working the way I wanted it to although I had to modify your solution to my second point. Once I had created a second occurrence of the MasterCompanyList as you described I could see all stocks listed within the portal which had the same industry sector.
However, in order to see the relevant data for each stock from the CompanySnapshotTable I set up another occurrence of the CompanySnapshotTable and linked the CompanySameIndustry to the second occurrence of CompanySnapshotTable as follows:
CompanySameIndustry::SEDOL = CompanySnapshotTable 2::SEDOL
Once I had done this, it worked perfectly.
Many thanks for your help and the link to the table occurrence thread.
Another thought on your basic design. Since the SEDOL number is generated from outside FileMaker, you might consider not using it as the link to your other records. Granted, you've indicated that it is unique, but this still leaves you vulnerable to the following scenario:
You create a new masterlist record, but mistype the SEDOL number and then create a number of records in the related tables. If you then discover and correct your error with the SEDOL number, all your related records seem to disappear as they are still attempting to link to a master list record with the incorrect SEDOL number. You'd have to find each set of related records with the incorrect number, fix them first, then fix the data entry error in the master list record.
A better approach is to define an auto-entered serial number field in the Master List table and use it to link to your related records. You can keep your SEDOL number field in the Master List table so you can use it for searches and such, but now you will no longer "lose" your related records if you discover the need to change the SEDOL number in order to correct an error.
Thank you for your suggestion. If I were to do this and create an auto-entered number in the Master List Table, I would also need to get that serial number into the other two tables ie the CompanySnapshot table and the ReviewTable. The CompanySnapshot table is imported monthly from Excel and has the SEDOL field present. In order to get the auto-entered serial number into that table, would I use a lookup field to the MasterCompanyList table.
A looked up value setting on the field that uses a different relationship based on the SEDOL number instead of the serial number would work, but since the data is imported and the SEDOL number forms the link to your Master List Table, there's not much reason to use the auto-entered serial number to link the two tables as it would be indirectly based on the SEDOL number anyway.
Thank you for your help. I have everything working but would like to make some alterations to the layouts to make it easier to use. In particular, I would like to be able use a dropdown list to select the both industry and the date for the portal to display. I have set up a another table which just holds the screening criteria ie date, industry and linked this table as shown.
However, I seem to have two problems, firstly, even though the portal selects the correct stocks, it does not display the correct stock name but seems to default to the first stock name in the last for all the stock in the portal. Secondly the list will change when I change the industry but if I only change the date it does not reflect the change.
What am I doing wrong?
Take a look at your portal's relationship. Unless you redefine the relationship, there's no part of that relationship that uses your new date field to control which records are visible. Nor have you set up a portal filter (Requires FileMaker 11) to filter your records by date.
If you are using FileMaker 11, enter layout mode and double click the portal. In the portal set up dialog, click "Filter portal records" and enter an expression that uses your date field to filter the records visible:
Table::YourDateField < PortalTable::DateField
Then create a one line script: Refresh Window [Flush cached join results]
And use an On ObjectSave script trigger on your date field to perform this script so that your layout updates when you select a different date.
In older releases of FileMaker, you'd need a relationship like this:
MasterCompanyList::IndustryType = CompanySameIndustry::Industry type AND
MasterCompanyList::DateField < CompanySameIndustry::DateField
Sometimes this requires creating yet another table occurrence if you need the original table occurrence for other purposes, but you also don't need the script to refresh the window each time you select a different date.
I now have it all working the way that I want it to. Thank you very much for your help.
It has been the first time that I have used Filemaker Pro and it has been a useful exercise. My next project is to design and build a trade database!!