FM7 create new record in related table

[kmerritt]

I can't seem to create new related recorded in a related table.

I have a main table of employees which relates to another table of events. I have a button where the user can click to create a new event for that employee. In FM 6 I used to run and external script in the related file to create this new record and populated the related fields using globals.

I have to believe there is an easier way with FM7. However, now I can't figure out how to just create the related record since I am using tables and not a separate file anymore.

Thanks for the help.

-Kevin

[Ender]

The same procedure could be used, just go to a layout that refers to the table you want to create a record in.

If the record is related, you could instead use a portal (with the portal's relationship set to allow creation of related records,) then go to the last portal row and add a record in the first open row. This could be scripted in a hidden layout or this could be the process users go through to enter new Events.

[kmerritt]

A couple questions:

1. If you have more than one portal on a layout, how can you use "go to portal row" acurately?

2.If I want to use a script step to create the new record, then I need to go to a layout associated with that table right? I can't just tell the script to go to the portal and then use New Record? I have tried to use

go to portal row (last)

Create New record

but it didn't work for me, (it created a record in the main db) but I don't know if I was just missing something.

Thanks, Kevin

[-Queue-]

1. Try Go to Field [relFieldinPortal] before Go to Portal Row.

2. A new record doesn't exist until you start filling in some fields. So go to the last row and set a related field.

[kmerritt]

1.Try Go to Field [relFieldinPortal] before Go to Portal Row.

2. A new record doesn't exist until you start filling in some fields. So go to the last row and set a related field.

When I try to do the above here is what happens:

The user click new record and the script goes to a layout with 2 portals on it.

*the portal I am targetting only shows one row*

I use GO TO FIELD (choose rel field in first portal) and the cursor shows up in that field in the correct portal.

I use GO TO PORTAL ROW (last) and the last portal row appears.

I use NEW RECORD/REQUEST and a new record is created in the Main DB and not in the the related table.

Of course then if I enter anything in the portal fields, it automatically creates a related record, but related to the newly created main record.

What am I doing wrong?

I have a work-around solution, which is to create a layout based on the table that i want to create the related record in. The the NEW RECORD step will work fine and I can abandon the portal stuff. However, I then have to set the related fields with globals, etc... That is what I have done in the past, but I keep thinking there is a better way I could be doing this.

Thanks for the advice.

-Kevin

[-Queue-]

Maybe I wasn't specific enough. I didn't say to use New Record/Request. I said a new related record won't exist until you start entering some data for it. So Go to Portal Row [Last] and Set or Insert some data in a related field, just like you would manually. You wouldn't manually create a new record and then enter data in the portal, right? You would simply go to the last record and start entering info.

[kmerritt]

When I "Go To PORTAL ROW (last)", it doesn't take me to an empty portal record. Instead it takes me to the last related record. Therefore when I enter info using set field I am replacing the info in another related record rather than creating a new one.

Is this because my portal only shows one row?

-Kevin

[-Queue-]

Have you set 'Allow creation of related records' for the related Table Occurrence, as Ender specified?

[kmerritt]

Have you set 'Allow creation of related records' for the related Table Occurrence, as Ender specified?

Yes, I checked the box in the relationship to allow records to be create in the related file.

I can create the related record manually if I turn on the scroll bar in the portal so that I can go to the empty portal row, but i can seem to make it work with a script.

-Kevin

[kmerritt]

I figured out some of the problem. When I turn on the scroll bar in the portal, I am able to use the GO TO PORTAL ROW to get to an empty portal row and thus enter some data to create the new record. However, if I turn off the scroll bar feature, then GO TO PORTAL ROW (last) takes me to the last portal record and not the empty one.

Thus, for what I am trying to do, I am not sure there is a solution without creating another hidden layout to first create the record and then go to this layout to modify it.

Any thought on other solutions?

-Kevin

[dbruggmann]

Hi Kevin

Have a look at the attached sample file. Tell us, if it does what you want and where it differs.

Test.fp7.zip

[smorr]

Kmerritt: we meet again

One approach I often use is a specific relationship to create a related record

What it involves is using a unique key to create the record, set the data, and then set key to blank

Steps:

In the related table add a field "CreateKey"

In the Maintable add a calc field "UniqueKey"= some unique identifier. In FM 7 I use =get(systemNICAddress) (It is important for shared DBs that this unique per user)

Then create a Relationship/TO "CreateInRelated" where UniqueKey=CreateKey and the create in Related Table is on.

Then you can script the creation as such:

# makesure that there are no related records with unique key

[loop]

   [exit loop if: not isvalid(CreateInRelated::CreateKey)]

   [set field: "CreateInRelated::CreateKey" ;"" //Set any existing create relationship off (safety)]  

[end loop] 

#

# Now Create

[set field: "CreateInRelated::_datum"; "whatever" // This will create the record and set whatever data]

[set field: "CreateInRelated::_moredatum"; "whatever" // Record is already created -- just sets more data]

[setfield:  "MainTable::gCurrentRelatedRecord" ; "CreateInRelated::_SerialID" // record id of record just created]

# Now release creation relationship

[set field: "CreateInRelated::CreateKey"; "" ]

#If you, for whatever reason want to go to that relationship in a portal just continue with the following

[Go field: "RelatedPortal::_Datum]

[Go Portal Row: First]

[Loop]

     [exit loop if: "MainTable::gCurrentRelatedRecord = "RelatedPortal::_SerialID"]

     [Go Portal Row: Next (exit after last)]

[end Loop]

Generally speaking I set up this structure/Script for all my relationships as it is very quick and doesn't mess with jumping layouts (it can be used almost anywhere -- though you will want the CreateInRecord TO relationship to be in your relational paths -- or at least jump to a layout where it is in the relational path -- It is also handy for creating records in tables in external database files.

Hope this helps

[bruceR]

Your use of isValid is not valid and Filemaker reps have repeatedly advised that it should not be used this way.

[exit loop if: not isvalid(CreateInRelated::CreateKey)]

Should be:

[exit loop if: not isEmpty(CreateInRelated::CreateKey)]

[-Queue-]

I believe that should be IsEmpty, not "not IsEmpty".

[bruceR]

There are some missing "commit record" statements in the example script cited above by smorr.

Unlike previous versions, FM7 does not create the record or actually enter data into the record using set field if you do not commit the record afterwards. As written, if this script is used in FM7, it won't do anything; or at least won't be reliable.

[n1k0]

Just now I ran into the same problem, and happened upon this thread, which spawned an idea and a workaround. It's a hack, but then again, so am I.

It hinges on having the related key-referenced field IN the portal as a sliver thin editable field (font can be set to background color for extra invisibility).

My script looks like this:

Go to Field [select/perform; ### RELATED FIELD FROM PORTAL ###]

Go to Portal Row [Last]

Set Field [### KEY FIELD FROM MAIN DATABASE ###]

Go to Portal Row [select; Last]

Go to Portal Row [select; Previous]

Go to Related Record [etc. etc.]

The last two "Go to Portal Row"s are only necessary if your "Go to Related Record" opens in a new window, as one must somehow 'deselect' the field in the first window before it can be edited in the new window.

Hope I was useful and/or clear!

n1k0