Primary keys are fields where the value in the field uniquely identifies each record. 99.999% of the time in FileMaker, this is a field with an auto-entered serial number. Thus, a list of these values taken from your current found set will uniquely identify all the records in your found set and have nothing to do with the specific find criteria used to produce your found set.
Right, I guess I never thought of adding a serial number to each table without a specific reason. (or I never knew this as a reason) You probably add a serial number field to every table every time. I may adopt this mode. OK, so I need 3 layouts; 1 that has only the PK, one that has the gPrimaryKeys and the original layout the has the portal on it. All 3 layouts on the same table, right?
thanks for your help Phil-DJ
I do add a PK field, defined as a auto-etnered serial number to the core tables of my solution from the start. Many developers add them to each every table from the very beginning and this is not a bad way to go.
I tend to add them on an "as needed" basis to the other tables that make up my solution. I can get away with that because adding a serial number field to a table in filemaker is a pretty simple thing to do unlike some other database systems.
Add the field to your table and specify the serial number options.
Add the field to a layout based on this table
Select Show All Records
Put the cursor in this field and use Replace Field Contents to add serial numbers, selecting the "update serial number in Entry Options" check box.
Sorry, somehow I missed the rest of your question:
OK, so I need 3 layouts; 1 that has only the PK, one that has the gPrimaryKeys and the original layout the has the portal on it. All 3 layouts on the same table, right?
That will work, though the layout with the global field, gPrimaryKeys, can actually be based on any table in your system and it will still work due to the field being global.
That works great except...
The data that I need to show in this found set portal does not exist in the local table. A lot of the data shown on these records is displayed from other tables based on a different relationship but some is local. Does all the data need to be local in order for it to work with this PK found set technique? If so, it kinda defeats the whole benefit of relational data. I guess I could do a bunch of lookups to get the data on this table. Is there a better way? Maybe you have experienced what I am describing. -DJ
I can't go beyond generalities unless you spell out the details on what you are trying to show in your portal.
A portal can include fields from occurrences other than the one specified in portal setup | Show Records From. What data they display will be determined by the relationships you have defined in your database. If you add a field from an unrelated occurrence, you'll see an error indication when you return to browse mode.
I'll describe two tables;
My process creates a new record in SJT when the lead is elevated to a sale. Instead of using a look up to copy the customer info (name, address, ect), I display that data on the SJT layout from LT based on a relationship primary key called LeadNumber. Now if I do a find on the SJT layout, I would like to display that found set in a portal on the same SJT layout that displays the customer info along with other data the existing only in the SJT. Your prescribed technique works to show the found set in the portal but the data that does not exist native to SJT does display correctly. It shows the same iteration of whatever record I'm on in every portal row. Now the data that IS native to SJT, displays correctly (different values on each portal row). This leads me to believe that the data must exist in the SJT in order for this technique to work. Am I missing something here? -DJ
Begin off topic comment:
My process creates a new record in SJT when the lead is elevated to a sale.
Have you considered changing the status of the record from "lead" to "sold" or some such rather than moving the data to a different table? That can be a much simpler way to manage this in many cases.
End off topic comment
You appear to have this relationship:
LT::LeadNumber = SJT::LeadNumber
and presumably a relationship like this for your portal:
SJT::gLDNumbList = SJTfoundset::LeadNumber
Where SJTfoundset is an additional occurrence of the SJT table. If you add an occurence of LT, LTfoundset, you can set up this relationship:
SJTfoundset::LeadNumber = LTfoundset::LeadNumber
Now you can add fields from LTfoundset to your portal and they will correctly display the matching date from the LT table.
Can you give a more complete example of what you are trying to set up here and why you want the portal?
I keep coming back to the fact that you can display your found set in a list view layout and not have to add any extra scripting or relationships to show your found set and the related data....
Oh, I figured it out,
Using a calc text field, I can put together the data there and use that on the portal. Since it is native to the table, it works fine in found set portal technique you showed me. Thanks for your help Phil. Your'e the best. -DJ
I know I'm a little late, but for anyone reading this, I will note that the proposed script leaves all the primaryKeys in the clipboard. That is a bad practice (changing what the user had on the copy/paste clipboard)
What I did to fix this is to create a "tempClipboard" global field, in a unrelated 'systemTable'. In the script I added a 'paste' to this tempClipboard at the very begining, and a copy from the same field at the end. This way you preserve the clipboard the same after the script is done, and the user doesn't see a weird text with our primaryKeys that he has no idea why is the system pasting that.
the script will end up something like this:
//paste current clipboard
Go to Layout [//go to layout where I have 'tempClipboard' global field ]
Paste [ Select; 'tempClipboard']
Go to Layout [//go to layout based on same table occurrence, but only place the primary key field in this layout]
Copy All Records //copies primary keys to clip board
Go To Layout [//Go to a layout where you've placed the global field: gPrimaryKeys]
Paste [select ; YourTable::gPrimaryKeys]
//copy tempClipboard back to clipboard
Go to Layout [//go to layout where I have 'tempClipboard' global field (same as before) ]
Copy [Select; 'tempClipboard']
//return to original layout
Go To Layout [original Layout]
This thread has been very helpful. Now I need a portal to display a found set from a different, related, table. I'd appreciate any suggestions if a portal isn't the best solution.
I’m working on a medical record solution. Patients have Evaluations that consist of Elements. One Evaluation record is related to many Element records of different types. For example one Evaluation record may relate to an Element record of type “ChiefComplaint” and a second Element record of type “HistoryPresentIllness” and so on.
Over time, the Element table will have many records since Patients have multiple Evaluations that consist of multiple Elements. For a given Evaluation, I need to rapidly sort and display a found set of Element records.
The first find criteria will always be the Evaluation record to which the Element record is related. A second find within that found set may include criteria like Element type, who gathered the data contained in the Element record, and when they gathered the data. Multiple sort criteria are needed, similar to the find criteria. These find and sort criteria will change based on user input and scripts.
Using Phil’s general script above, all of this appears to work as I step through the script with the debugger…
Go to Layout [“ElementPrimaryKeys” (Element)]
Copy All Records/Requests
Go to Layout [“gPrimaryKeys” (SystemTable)]
Paste [Select; SystemTable::gPrimaryKeys]
However, when this step executes…
Go to Layout [“Edit History” (Evaluation)]
The portal contains all of the Element records even after doing a find on Element type that limits the found set of Element records to one type (e.g. “ChiefComplaint” or “HistoryPresentIllness”).
I suspect my problem is here…
Now, if you use this relationship for your portal:
YourTable::gPrimaryKeys = YourTable2::PrimaryKeyField
Your portal will display the same records as are currently present in your found set.
My portal is set to display related records from the Element table on a layout based on the Evaluation table. The Edit Relationship dialog window for that relationship is set to…
Table: Evaluation Table: Element
_pkEvaluation = _fkEvaluation
Do I need to establish a relationship with SystemTable somewhere?
I’ll take care of the clipboard issue noted above once I get the main issues resolved.