Get related record of repeating field

[gephry]

I have CALENDAR and ADDRESSBOOK tables related by CALENDAR::Tech[2] <==> ADDRESSBOOK::Name. Calendar::Tech[2] is a repeating field with two repetitions.

I have a button that takes you to the ADDRESSBOOK related record of the Tech person....I can get it to do it for Tech[1] but can't think of how to do it for Tech[2].

Only idea I have is a run-around by having the script swap the Techs around, getting the related record from repetition 1, and swapping them back. But there's got to be an easier way! Thoughts?

Thanks!

[Søren Dyhr]

Could you tell me why you shied from using a portals?? Remember each row can be put where ever you wish by cutting it up ...you must at least reveal the secret of why repeaters are used??

--sd

[gephry]

Haha, well.

The Calendar is an ever-changing college events calendar for my tech-support office. The Tech field contains the tech person(s) I have assigned to the event. There is the possibility of two people per event; it's a drop-down menu from the people deemed as "Tech" people in my AddressBook table. I need the Tech field searchable so therefore is repeating. So I select the person and violá--event scheduled.

I couldn't do that through a portal....could I?

[Søren Dyhr]

I couldn't do that through a portal....could I?

Did you try??

--sd

[comment]

I have CALENDAR and ADDRESSBOOK tables related by CALENDAR::Tech[2] <==> ADDRESSBOOK::Name.

I don't think that's possible: a relationship using a repeating field uses the entire field. You cannot specify individual repetition when defining the relationship.

I have a button that takes you to the ADDRESSBOOK related record of the Tech person....I can get it to do it for Tech[1] but can't think of how to do it for Tech[2].

I believe that's not accurate either: Go to Related Record should take you to BOTH related techs.

In any case, if you want to refer to each related tech separately, you should use a join table for assigning a person to an event.

[gephry]

Hm. Nope.

[21 minutes later.]

Yes. But I couldn't get it to work properly. I tried a few different things----I don't see how I could use a portal to insert the Tech into the Calendar...because that's not what I'm doing. I did figure out this way, but it seems roundabout:

I figured I could create a portal to show the related AddressBook record via the relationship (2 portal rows). But, if I have something in Tech[2] and not in Tech[1], the related record for Tech[2] shows up in the first portal row.

So the solution for that would be to have two buttons--one for each repetition. Each button sends an "optional script parameter" which is the value of the corresponding repetition. The script then checks to see if the name matches portal row 1; if it does it performs Go to PortalRowNumber[1]; Goto Related Record..... If not = PortalRow1, it checks the match for portal row 2 and if that is a match, Go to PortalRowNumber[2]; Goto Related Record..... But that's a weird weird way to do it. One of the manyy negatives of that is that it puts a random blank portal on my layout.

So that works but it just seems very roundabout. Like trying to go from London to Paris by flying to Bangkok first. I'm sure there are other ways to do it and I'll feel stupid once I figure them out. I know I could think about it more and figure out another way to do it....but I've got to run. I'm going to play with it later.

(By the way, I really like your way of teaching, Søren. "Did you try it?" You've been a big help to me over the past days, too. Thanks.)

[gephry]

Comment wrote:

I believe that's not accurate either: Go to Related Record should take you to BOTH related techs.

A repeating field operates the same way in a relationship that a field with a hard return (¶) operates.

RELATION1:

If you have a single field containing:

"Bob¶

Bill¶

Bo"

The portal will show all related records for all three pieces of data.

RELATION2:

The repeating field will store (or at least SHOW) data differently as if it were three separate fields.....

Repeating[1] = Bob

Repeating[2] = Bill

Repeating[3] = Bo

...but it will show exactly the same results as a field with hard returns (¶) between the data. The repeating field just makes it easier to insert multiple "selections" of data.

Thing is, if you use "Goto Related Record" for either RELATION1 or RELATION2, it will always go to the first related record in the related data set, that being "Bob"

Comment wrote:

In any case, if you want to refer to each related tech separately, you should use a join table for assigning a person to an event.

Yea, you're probably right. I am just getting to translate this from FM6 so should change how this part operates.

[comment]

I don't see a contradiction here. Except this:

it will always go to the first related record in the related data set, that being "Bob"

It could be Bob, or it could be Bill, or it could be Bo - in any case, it does NOT depend on which matching value is in which repetition, but on the sort order of the relationship. And all three related records will be in the found set.

Edited January 5, 2007 by Guest

[Søren Dyhr]

I don't see how I could use a portal to insert the Tech into the Calendar

The usual way is just to adding the ID of the the tech in a multiline key. checkout the two templates we made in this thread:

http://www.fmforums.com/forum/showtopic.php?tid/183051/post/234872/hl//

One of the manyy negatives of that is that it puts a random blank portal on my layout

Not true, if you turn off allow creation of related record in the relations def as well as cutting the two line up in two, then is it nearly the "visibility..."

http://www.filemakerpros.com/Visible7.zip

--sd

[gephry]

Whoops, sorry I've been away for a few days.

Thanks for your help guys. I understand the examples, but since I'm only using FM8 and don't have the List() function, I don't feel as if that alternative to LIST is the best solution for me so I ended up using the "Visible7.fp7" example. Within the portal is the TechName field and the relationship allows creation/deletion of portal records. So that fixes my field repetition problem. It allows me to go directly to whichever Tech person I want, so that's good.

Thanks again!