How can I refer to an other field of the same record?

[MacSig]

hello guys,

I'm new on FM scripting and I don't understand how, having a field value, I can refer to an other field of the same record.

Let us say I have a table called friends with 2 fields: ID and name

Now I pass as a parameter "name" and within my script I need to use the ID. Do I have to perform a find in order to get the record and after saving the ID of that record in a variable or there is a easiest way?

Thanks and have a nice weekend

THANKS

[bcooney]

What is your goal and purpose of the script? It's really not clear where you're heading.

[MacSig]

Hi Barbara, thanks for your reply.

I have a many to many relationship between locations and friends

and I would like to add a new friend from the location layout so I have added a global field where I display all the friends (I use their name).

Through a button I trigger a script in order to add a new record on the join table. But as I said I have the friend's name instead his ID.

So my question is: Do I have to perform a find using the name in order to retrieve the record or there is a easiest way?

Hope this time it is clear enough.

[bcooney]

Much better, thanks. On the Location form, to add a friend to the join table, you could simply allow creation of related records in the relationship from Location to Join. Then, put the join table's Friend ID on the portal row. Format it to be a popup menu, using a value list of Friend ID and Friend Name (use a calc of First and Last to avoid duplicates), only showing the second value.

So, the user would simply select a friend in the portal row.

[MacSig]

I thank you for your reply.

I don't want to create new friends on the portal (I use it to show them) because when the friend number becomes high I have to scroll down until a new row is available. I prefer having a field where I can select the friend and creating the relationship through a script.

My script now looks like

Set Variable [ $locationID; Value:GetValue (Get (ScriptParameter) ; 1) ]

Go to Layout [ “friends” (locations) ]

Enter Find Mode [ ]

Set Field [ friends::name; GetValue (Get (ScriptParameter) ; 2) ]

Perform Find [ ]

Set Variable [ $friendID; Value:friends::fID ]

#--------------

Go to Layout [ “meetings” (meetings) ]

New Record/Request

Set Field [ meetings::fID; $friendID ]

Set Field [ meetings::lID; $locationID ]

Commit Records/Requests

[ Perform with dialog (NOT COMPATIBLE) ]

Go to Layout [ original layout ]

and seems it works.

[bcooney]

Have a look at this article by Scott Love. It's the best summary of interface design for popup window selectors.

[MacSig]

thanks for the link. unfortunately it is a subscriber-only article.

[bcooney]

bummer, i thought it wasn't. time for a demo, when i can.

Basically, your "Link Friend" button would immediately capture the Location ID (you're on the Location record) into $locID. You don't need a button parameter.

Then, pop a new window of a friend list (based on friend TO) and pause. On each row of the list have a Select button that is set to Resume script.

Next step, set $friendID to the _kP_FriendID. Switch to a join table layout and set both foreign keys using your variables.

Finally, close window.

[MacSig]

thanks for your explanation.

It would be really great If you could let me have a demo because I'm not sure I understand how it works.

Thanks again and have a nice weekend.

[comment]

I have added a global field where I display all the friends (I use their name).

I believe that is the source of your problem. To display all the friends, use a portal to the friends table (i.e. another occurrence of the friend table), with the underlying relationship defined to use the "x" relational operator.

This way, you will have access to the selected friend's ID and can pass it to your script.

[comment]

BTW, here you can find a demo that's very similar to what you describe:

http://fmforums.com/forum/showpost.php?post/300477/

[MacSig]

Thanks for your reply buddy.

I haven't looked into you file (I have just opened it) but looks like a good idea

if I need some help to complete understand you option I will let you know.

Have a good weekend

[MacSig]

I have already a question for you

How did you create AllAttributes and AttributesInCategory ?

I mean I can see them on the relationships tab but not in the table one.

THANK YOU AGAIN. I APPRECIATE YOUR HELP

[comment]

A table can have multiple OCCURRENCES on the relationship graph.

[MacSig]

thank you for your reply.

I'm now to FM development so even simply stuff are new for me.

[MacSig]

Hi Comment,

I have a question for you about the script you use to add a record on the join table.

After setting the field you first go to the origin layout and after that you commit the record. is there a reason why you do that?

I mean, to me looks better to commit before changing layout.

THANKS

[comment]

It doesn't really matter - record will be committed anyway, by virtue of changing layout. It's just a habit to conclude every operation by Commit Records.

[MacSig]

Thanks for the explanation.

Actually I have an other question (the last one).

Let us say beyond categories I have super-categories (so a super-category has many categories that can belong only to a super-category).

How can I filter the attributes for super-category? Since super-categories and attributes are not directly related.

I would like to have 3 portals: 1 where I display all the attributes, 1 where I filter the attributes by category and 1 where I filter the attributes by super-category (showing all the attributes that are related to 1 of the categories related to the super-category).

If it is easier I can merge the last 2 portal adding an intermediate step: first I filter the categories for super-category and after showing on the portal only the attributes related to the selected category.

I'm playing around those 2 options for awhile but I can't make either works.

THANKS AGAIN AND HAVE AGOOD NIGHT!

[comment]

I am not sure I follow this. It seems you would first to select a super category. Once you've done that (i.e. the selected super-category's ID is in a global field), you can have a relationship to Categories based on matching the global to the parent super-category ID in the Categories table. Hang another TO of Attributes off the Categories, and point your portal to it.

[MacSig]

Basically, I would like to select a super-category on a menu and display on a portal all the attributes related to categories related to the selected super-category.

Is this possible? How can I create AttributesInSuperCategory?

If not my idea is to execute 2 filtering.

Thanks again

[comment]

Yes, it is possible exactly as I said. If it's not clear, add a table of Super-categories to the demo, and I'll show you how to link it.

[MacSig]

Thanks,

unfortunately it is not so clear.

I have updated the file:

- I have created a SuperCategories table

- I have created a relationship between SuperCategories and Categories (added SuperCategoryID in Categories)

- I have created some supercategories

- I have linked the existing categories to a supercategory.

I hope that is all you need to show me how to link Attributes and Supercategories.

Thank you for your time. I appreciate it.

PortalToPortal.fp7.zip

[comment]

Like this?

PortalToPortalSuperCat.fp7.zip

[MacSig]

That is exactly what I need.

Thank you so much for the demo. I do appreciate it.

Have a nice day.