Portal to text field?

Hi!

I've been tearing my hair out over this problem for longer than I like
to think about, so I figure it's time to ask people who are better at
Filemaker than I am.

Here's the thing: I have a portal with names related to a specific ID.
I'd like to somehow extraxt ALL the names related to the ID from the
portal and display them in a text field, preferably comma-separated.
Just to be clear:

Portal:
John Smith
Abe Jones
Paula Wright

Text field:
John Smith, Abe Jones, Paula Wright.

I don't care how workaround-y or scripty the solution is, I just have
to get it to work. Any help would be much appreciated by the entire
office :-)

I'm using FM Pro 7, if it makes any difference.

Thanks,
Johanna

Re: Portal to text field?

johannax@gmail.com wrote:
> Hi!
>
> I've been tearing my hair out over this problem for longer than I like
> to think about, so I figure it's time to ask people who are better at
> Filemaker than I am.
>
> Here's the thing: I have a portal with names related to a specific ID.
> I'd like to somehow extraxt ALL the names related to the ID from the
> portal and display them in a text field, preferably comma-separated.
> Just to be clear:
>
> Portal:
> John Smith
> Abe Jones
> Paula Wright
>
> Text field:
> John Smith, Abe Jones, Paula Wright.
>
> I don't care how workaround-y or scripty the solution is, I just have
> to get it to work. Any help would be much appreciated by the entire
> office :-)
>
> I'm using FM Pro 7, if it makes any difference.

It does make a difference.

In 8.5 and up, we have a List function which returns just what you want:
a return-separated list of all values in a related field.

In 7, you're limited to a scripted workaround, using a temporary holding
field (pseudo):

Set Field [ TempField ; "" ]
Go to related record [show only related records]
Loop
Set Field [ TempField ; TempField & YourFIeld & "¶" ] (if the character
didn't show, that's a FileMaker return, a/k/a "pilcrow")
Go to next record [exit after last]
End Loop
Go to layout [ original layout ]

Now use the contents of TempField as you wish.

Matt
--
Free FileMaker Technique Demos: http://www.VirtualVermont.com/FMP

My Custom Functions:
http://www.briandunning.com/filemaker-custom-functions/resul ts.php?keyword=wills

Re: Portal to text field?

Matt Wills wrote:
> johannax@gmail.com wrote:
>> Hi!
>>
>> I've been tearing my hair out over this problem for longer than I like
>> to think about, so I figure it's time to ask people who are better at
>> Filemaker than I am.
>>
>> Here's the thing: I have a portal with names related to a specific ID.
>> I'd like to somehow extraxt ALL the names related to the ID from the
>> portal and display them in a text field, preferably comma-separated.
>> Just to be clear:
>>
>> Portal:
>> John Smith
>> Abe Jones
>> Paula Wright
>>
>> Text field:
>> John Smith, Abe Jones, Paula Wright.
>>
>> I don't care how workaround-y or scripty the solution is, I just have
>> to get it to work. Any help would be much appreciated by the entire
>> office :-)
>>
>> I'm using FM Pro 7, if it makes any difference.
>
> It does make a difference.
>
> In 8.5 and up, we have a List function which returns just what you want:
> a return-separated list of all values in a related field.
>
> In 7, you're limited to a scripted workaround, using a temporary holding
> field (pseudo):
>
> Set Field [ TempField ; "" ]
> Go to related record [show only related records]
> Loop
> Set Field [ TempField ; TempField & YourFIeld & "¶" ] (if the character
> didn't show, that's a FileMaker return, a/k/a "pilcrow")
> Go to next record [exit after last]
> End Loop
> Go to layout [ original layout ]
>
> Now use the contents of TempField as you wish.
>
> Matt

Oops, sorry.

I notice after the fact that you wanted a comma-separated list. Change
the pilcrow in the Set Field to ", " (a comma and a space). After the
loop, add this step to strip off the last two characters, because you
don't need the ", " after the last value:

Set Field [ TempField ; Middle [ TempField ; 1 ; Length ( Tempfield ) -2 ]

Matt

--
Free FileMaker Technique Demos: http://www.VirtualVermont.com/FMP

My Custom Functions:
http://www.briandunning.com/filemaker-custom-functions/resul ts.php?keyword=wills

Re: Portal to text field?

In article <1188552113.502022.44680@r23g2000prd.googlegroups.com>,
johannax@gmail.com wrote:

> Hi!
>
> I've been tearing my hair out over this problem for longer than I like
> to think about, so I figure it's time to ask people who are better at
> Filemaker than I am.
>
> Here's the thing: I have a portal with names related to a specific ID.
> I'd like to somehow extraxt ALL the names related to the ID from the
> portal and display them in a text field, preferably comma-separated.
> Just to be clear:
>
> Portal:
> John Smith
> Abe Jones
> Paula Wright
>
> Text field:
> John Smith, Abe Jones, Paula Wright.
>
> I don't care how workaround-y or scripty the solution is, I just have
> to get it to work. Any help would be much appreciated by the entire
> office :-)
>
> I'm using FM Pro 7, if it makes any difference.

There is a way that should work in any version of FileMaker that has
Relationships abilities.

Since you've alrewady got the Relationship defined for the Portal, you
can easily make use of that to grab the text values.

Define a new Value List (File -> Define Value Lists) that obtains it's
values from the appropriate Name field via the Relationship.
eg.
vl_RelatedNames Use values from field
PortalRelationship::NameField

Now you can create a new Calculation field in the main Table that grabs
these values using the ValueListItems function. This function gives you
the values from the Value List each separated by a carriage return, so
you also need to replace that with a comma using the Replace function.
eg.
RelatedNames Text Result, Unstored
= Substitute(
ValueListItems ({DataBaseName}, vl_RelatedNames),
"*P",
", "
)

where *P is really the "backwards P" carriage return symbol that is on
one of the buttons in the Define Calculation window. {Database Name} is
the name of the database file which has the Value List.

Note: unfortunately the name and parameters of the ValueListItems
function may have been changed in newer versions of FileMaker - but you
should be able to find it in the Design Functions category when in the
Define Calculation window (use the pop-up menu above the list of
functions to narrow down the search).

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)