Jump to content
Sign in to follow this  
daniel z

Whats the best way to add a related record?

Recommended Posts

This is such a basic question but I am not sure of the answer to it. Whats the best way to add a related record in filemaker ?

eg if I have a contacts table and I wanted to add a related sale to that contact, I could

1) create a new sales record and type in the contact id

2) list related sales in a portal from the contacts layout and allow "creation of records via the relationship" - and type in the last row of the portal.

ok - but if I want to automate this process using a script, whats the best method ?

Why doesnt Filemaker have an "add related record" script step ?

Thank you

Daniel

Share this post


Link to post
Share on other sites

So it would... no really, would save a lot of meaningless scripts or otherwise relationships or otherwise layouts.

Share this post


Link to post
Share on other sites

Ps the reason i LOL was because its so obvious that i can't believe i have not heard anyone say it before or that i have not thought of it when putting together my own scripts.

Its the simple things in life.

Share this post


Link to post
Share on other sites

If you don't want to allow creation of records through the relationship, you can do it this way in FM8/8.5:

Set Variable [$ID ; Parent::ParentID ]

Go to Layout [ Child ]

Set Field [ Child::ParentID ; $ID ]

Then set whatever other fields you wish and either leave the user in a Child detail screen, or return to the original layout and set the user in the last portal row.

In FM5/6, there's a couple additional steps:

Set Field [ gTempID , Parent::ParentID ]

Perform Script [ external: Child: "Add Child Record (subscript)" ]

Then in the Child file, the script "Add Child Record (subscript)" would look like:

Enter Browse Mode[]

New Record/Request

Set Field [ ChildID , Parent::gTemp ]

Again, you then can enter a detail entry screen in the child file, or return to the parent file.

Share this post


Link to post
Share on other sites

Since you're on FM6 can't scriptparamters nor $variables do you much good, so you need two relation to same file, one with allow creation of related record and one which is the one you wish to display.

The job is done by setting a randum number as primary key for the "allow..." of the relation, and then overwrite the foreignkey with the primarykey for the visible portal.

This is two scriptstep both with a Set Field[ step, but the matter happens automaticly in fm6, since there is some kind of auto-commit going on, the second realtion breaks and the record you forced to create, have been given the other relations foreignkey and the record snaps to place in the portal as an empty line to write something in.

It should be said that this feature have vanished with fm7, so if you plan to upgrade be aware of this, due a severe change in commiting records, which means that either scriptparamteres or $variables is taking the primary key to a just record in the childtable!!!

Others might suggest you write to a global field in the child file/table, but as a programmers habit isn't it fully embraced, not to say they're in fact a bad habit!

I just toyed with what could be continue the approach under fm8, in a sort of object oriented constructor method, instead of passing paramters or writing $variables or even worse global fields.

(caution this file might only work with fm8.5!!!!!!!)

--sd

CreateRelated.zip

Share this post


Link to post
Share on other sites

Daniel have written a mail to me privately asking:

Whats the purpose of using a random number ? I also am having difficulty understanding the field definitions for ForeignKey Case(IsEmpty ( Value ) or IsEmpty ( Get ( ScriptName ) );ForeignKey;Value) Value Case( Exact ( Get ( ScriptName ) ; "Create Related Record" ) and ForeignKey=Value;Case(0;0);Value)

In short, "damage control"...

1) the random number is to sure that we know which record we have created, if more users have gotten the bright idea to write portal rows to the very same main record.

2) To pull a new related record via "allow creation..." are we forced to write to a field other than the foreignkey unfortunately. What might be confusing is that the expression has been DeMorgan'ed to reduce implications of unnessersary calculation

3) ...to ensure if the just pasted value have been transfered to the foreignkey, while still being under script control by the correct script, if it is will the field be blanked/emptied. This is to prevent further clearing of entries made by the user, since it would make no sense!

Now this might still not give you the undertanding of the method, since it's admitted an abstract way of dealing with data borrowing quite a deal from object oriented developments constructor methods.

There aren't any correct way to do it, it all depends on what you wish to achieve, the aim here is to make scripting as short as posible - Do you wish me to make you some templates showing a more procedural way of doing it?

--sd

Share this post


Link to post
Share on other sites

Ok, thats much clearer - thanks

A couple more questions though:

(1) whats the purpose of "Case(0;0)" in the field definition of "Value" ?

(2) The auto-enter calculations for ForeignKey and Value refer to each other ; I'm a bit confused about the order of how things execute. Which auto-enter calculation runs first ? (looks like Value is worked out 2nd - right ? If so, why ?)

Dan

Share this post


Link to post
Share on other sites

Søren often makes things more complex than they need to be. This technique was a lot simpler when he first showed it to me back in version 4 days.

If a script is used to create the related record, then I would stay away from auto-entered calculations, and do all that needs to be done in the script itself. Much easier to understand and to maintain, IMHO.

the random number is to sure that we know which record we have created, if more users have gotten the bright idea to write portal rows to the very same main record.

That is precisely why a random number should NOT be used - because though extremely small, there's still a chance for two users to draw the same random number. Use a value that's 100% guaranteed to be unique to the users (e.g. Get(AccountName) - provided it's uniqueness is enforced).

Most importantly, this technique has the disadvantage of requiring a dedicated relationship. Why litter the Relationships Graph, when the script can go to the child table and add a new record there (as shown in Ender's post)?

Share this post


Link to post
Share on other sites

(1) whats the purpose of "Case(0;0)" in the field definition of "Value" ?

It's, what Michael would call complexity, but if you later change the field type to say container or date, will the Case(0;0) make it absolutely certain that you replaces the fields value/content with a null value for that fieldtype.

To the second half of your question is it the field value that recieves the first value, which forces the primarykey to show up in the foreignkey as well, since this foreign key is based on the wrong relation, must it change to appeare in the portal.

MIchael is right with many of his points though, but if you make it a habit to use methods like this in thougout your solutions, are they just as easy to debug ...leaving the "inspector" window closed until a real purpose shows up, since there are no real nead to inspect variables nor scriptparamters. One thing is spot on, the use of the random value, the login name/ID is much better, and if you turn it a unstored calc'field instead are the script one line shorter!

--sd

Share this post


Link to post
Share on other sites

Can you give an example where "" will NOT leave a field empty? I remember vaguely some tip about using GetAsDate (""), but I cannot come up with a problem for this solution.

In any case, reversing the logic from:

Case ( test ; "" ; value )

to:

Case ( not test ; value )

would seem more sensible to me.

Share this post


Link to post
Share on other sites

You're as usual right, but I use it for another kind of reasoning ...by leaving these small personalized "signatures" will I be able to spot a copycat from a thinker, this time it didn't work Dan didn't take the bate :thumbup:

--sd

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.