So if you don't want to use a button (You could just call the button "New" and use it to generate all new portal records), how do you plan to implement this from the user's perspective? Do you want them to just start entering data in the "add" row and see this info automatically appear from the preceding portal row?
Try out this auto-enter expression and see if it works for you. (use it on each field where you want to copy from the preceding portal record.)
Let ( R = Get ( RecordNumber ) ; IF ( R > 1 ; GetNthRecord ( FieldToCopyFromGoesHere ; R - 1 ) ) )
Leave the "do not replace existing value..." option selected.
Generating portal records: There's a spare new one always below the last filled row so no need to specifically generate one so, yes, info to appear once they start entering data. That said, if you wanted them to specifically add a new row, how would that be done?
Auto-enter expression: If 1 field I'd like this to occur with is called 'Feed' and I use a Calculated Value specified as:
Let ( R = Get ( RecordNumber ) ; IF ( R > 1 ; GetNthRecord ( Feed ; R - 1 ) ) )
I've found each value inserted is '11' which happened to be the record I was on when I changed the field. I can overwrite.
There's a spare new one always below the last filled row so no need to specifically generate one
That's only true if "Allow creation of recods via this relationship" is enabled for the portal's Table Occurrence in the underlying relationship.
Often developers choose not to use that option as it can be annoying to keep scrolling the portal every time you need to add a new record. Instead, they put a button on the layout to add the new record and set up a sort order for the portal so that the new record appears in the top row of the portal.
Here's one method (other method's can also be used):
Set Variable [$ID ; Value: YourTable::PrimaryKey ]
Go To Layout ["portalTableOccurrence" (PortalTableOccurrence)]
Set Field [PortalTableOccurrence::ForeignKey ; $ID]
Go To Layout [original layout]
Be careful with this approach that your script does not trip any script triggers due to changing layouts that then interfere with the correct execution of the script.
A) Your alternative method of adding a Portal row/record sounds great but can I check how the script syntax works:
1. Set Variable [$ID ; Value: YourTable::PrimaryKey ]
2. Freeze WIndow
3. Go To Layout ["portalTableOccurrence" (PortalTableOccurrence)]
4. New Record/Request
5. Set Field [PortalTableOccurrence::ForeignKey ; $ID]
6. Go To Layout [original layout]
1. Is the 'YourTable::PrimaryKey' referring to the parent table/layout on which the portal sits?
2. Is 'Freeze WIndow' intended to stop any changes to parent records?
3. Where in '["portalTableOccurrence" (PortalTableOccurrence)]' do you put the name of the portal, eg. Prescription?
4. 'New Record/Request' pertains to the portal record?
5. Does 'Set Field [PortalTableOccurrence::ForeignKey ; $ID]' replace the new record with the previous record? And I don't get how the local variable '$ID' set from the primary key gets the correct foreign key. My ignorance; can you explain?
6. Is 'Go To Layout [original layout]' referring to the parent layout on which the portal sits?
b) I assume I just attach the script to a button within the portal?
1) You have a field or fields that serve as the match fields in the relationship on which your portal is based. Since your new portal record has match field(s) that must contain matching data in order to appear in the current record's portal, these values must be copied to variables so that the match field(s) in the new record can be set to them Most often, this is the primary key field (usually an auto-entered serial number ) in the table on which your layout is based, but it could be another field or fields in your layout's table. (If your portal is based on more than one pair of match fields, you'll need additional set variable steps with additional variables to copy all the needed data for use in step 5.
2) No. This step may not be needed and your script will still work if you leave it out. In some environments, you'll get a bit of "layout flash" when the script changes layouts. This step simply keeps the window from updating until the script finishes so that the user does not see the change in layouts.
3) You can specify any layout in this step that you have in your file so long as it refers directly to the same table as your portal. The layout name will appear in quotes. The table occurrence specified in Layout Setup | Show Records From for that layout will be shown in parenthesis. The names I used are just placeholders that will be replaced by actual names from your database. The name in parenthesis, however, must be identical to the table occurrence name shown in the set field step that follows the New Record step.
4) That's the point of Step 3. By changing to a layout based on the portal's table, this script step creates a new record in the portal's table. If you specify the wrong layout in step 3, this step creates a new record in the wrong table.
5) No. It copies the value that $ID received in step 1 into the match field of the new portal record so that this record correctly links to the current record on your original layout. See my comments in 1) above. Think of $ID as a "bucket". You load the bucket with the needed matching value in step 1 carry it over to the portal table based layout, create a new record and then dump this "bucket" into the match field of the new portal record so that it links to the correct record in your original layout's table.
6) Go to layout [original layout] is written exactly how it will appear in your script. This layout option takes you back to the the layout that was current at the time your script started execution. In this context, it will return you to the layout where you have your portal.
Notes: This script illustrates how all scripts are very "layout sensitive". The layout that is current at the time any given script step executes determines the "table occurrence context" in force at the time the step executes and this, in turn, often determines exactly what happens when the step is executed. If your script changes to a layout based on Table A, New Record creates a new record in Table A, Change to a layout for Table B instead and the same script step creates a new record in Table B. Likewise, the Table occurrence context determines what, if anything happens when Set FIeld executes. When that step refers to the same table occurrence as that specified for the layout, the field referenced is the field from the current record of that table. If, however, it refers to a different table occurrence, the relationship linking that table occurrence to the layout's table occurrence determine what record of that related table is being referenced.
To learn more about table occurrences, see: Tutorial: What are Table Occurrences?
Thank you for the full explanation! That's really helpful.
Query: Is there a method of pasting the above script into a new (empty) script? Or do you always have to import from another FM file or build from scratch?
Many users for many years have asked for a "text editor" functionality for FileMaker's script editor--which would enable such copy and paste actions, but FileMaker Inc. has never provided such a capability. Wonder if any of the plug ins out there support that?
I must have something wrong. Instead of copying the portal row, it deletes everything but the blank one.
Don't see how that can delete anything, but your set field step is not correctly configured.
When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.
Combined with your earlier field calculation, that works v. well !
Again, do developers tend to list Portal records in reverse order to ensure the latest (including any new record just added) are visible? ie. once you've gone beyond the set number of portal rows, a new record disappears; that might be confusing to users. OR is there another scripting method to ensure the portal shows the latest record?
Thanks again for the detailed explanations; v. helpful.
Specifying a sort order on the portal or via its underlying relationship that automatically sorts the records so that the most recent record is listed first is quite common. If the portal's table has an auto-entered serial number field, you can sort on that field in descending order. You can also sort a field with an auto-entered timestamp in descending order.
Combining the above with reverse ordering in the Portal works v. efficiently. Thanks.
1. I didn't initially realise that using the last record number of course means that it copies the last 'record' not the 'last record in that portal'. How do I do the latter.
2. Child of child portal: I haven't been able to get the technique to duplicate the record in this 2nd level portal reliant on the 1st level portal.
Patient (layout_parent) > Prescription (1st level portal) > Record (2nd level). Keeping in mind 1., how would I duplicate the last record in this portal.
1. What method do you use to access the "last record number"? and what value is this? Is it a serial number auto-entered in the field? The Last function should be able to return a value from the "last related record" which should correspond with the last portal row if your portal were unfiltered and unsorted.
2. I need more detail in how you have set this up on your layout and what is the "last record" in this context. The last record in the first portal or the second portal?
And does your second portal display all "grandchild" records for all records in the first portal or just one selected portal record from the first portal? (Both approaches are commonly used in this type of set up.)