Collating Related records and combining data in a seperate field

[MutantST]

Hi All,

Hitting a wall at the moment with performing a find and with those found records, selecting some of the information and then for want of a better word "pasting" that info into another field.

My example is:

Photo shoot booking has been made. It has numerous related records of the subjects of who will be involved plus their contact details. I am trying to gather the subject name and contact info from this related table and have it available to paste into a secondary program like, word, ical or textedit.

Are there any methods that you can suggest that may work?

 

Edited April 12, 2016 by MutantST
crap spelling

[comment]

Copying data from related records is one thing. "Collating" it into a field is another. You could place a COPY button under a portal to Subjects, and have the script go to the related records, using a layout that shows only the fields you are interested in, and do Copy All Records/Requests[].

--
P.S. The part of the graph that you show is hardly readable, but it doesn't look right. Presumably, a subject can participate in many bookings - so it cannot have a BookingID foreign key. Instead, you should have a join table between Bookings and Subjects.

[MutantST]

Thanks for your comment. Unfortunate that the graph looks too small for you, although it looks fine on my screen. Luckily you saw enough information and have been able to comment. 

Thank you again, as the copy all records/requests works well and I am getting the required info from the tables. I am finding the formatting of the info problematic though as it brings back and pastes all the information in a Tab delimited format. Is there any chance you know how I can retrieve this info without the tabs/spaces included?

Records returned as below . . 

Chelsea Devlin    21    021021021        [email protected]    

Simon E    42    021145554        [email protected]    Admin support

 

[comment]

What would you want to have as the separator between fields, instead of the tab?

[MutantST]

I would prefer a comma

 

[comment]

16 minutes ago, MutantST said:

I would prefer a comma

And if one of the fields contains a comma?

Anyway, this is not going to be as simple, because Filemaker does not provide direct access to the clipboard. To do this natively, you would have to Copy All Records, then paste the result into a global text field, set that field to Substitute ( gTextfield ; Char ( 9 ) ; "," ) and copy the result.

Alternatively, you could use a plugin that allows manipulating the clipboard directly, or - if you work on OS X exclusively - do the same thing using AppleScript.

[MutantST]

Mmmm, seems like I cannot use the script or function - Substitute ( gTextfield ; Char ( 9 ) ; "," ) in my script.

[comment]

23 minutes ago, comment said:

set that field to Substitute ( gTextfield ; Char ( 9 ) ; "," )

I meant use the Set Field[] script step.

[Lee Smith]

I copied the text you posted into TextWrangler and the gaps in the text are spaces?

 

[MutantST]

This is what I am using at the moment and to no avail.

Set Variable [ $Bookingid; Value:BookingTable::BookingID ]
Go to Layout [ “Subjects” (Subjects) ]
Perform Find [ Specified Find Requests: Find Records; Criteria: BookingTable::BookingID: “$Bookingid” ]
[ Restore ]
Copy All Records/Requests
Unsort Records
Go to Layout [ original layout ]
Paste [ BookingTable::SubjectnamesGlobal ] [ Select ]
Set Field [ BookingTable::SubjectnamesGlobal; Substitute ( BookingTable::SubjectnamesGlobal ; Char ( 9 ) ; "," ) ] Copy [ BookingTable::SubjectnamesGlobal ]
[ Select ]
Paste [ BookingTable::Subjectnames ] [ No style ]

[comment]

See if the attached works for you.

Note that the global field must be on the layout for copy/paste to work (it does not have to be as noticeable, though).

CopyRelatedSet.fp7

Edited April 14, 2016 by comment

[MutantST]

Hi All,

Thanks for your help on this. I was running into problems with this script as I did not have the Subjectnameglobal on my layout. Now it is there, I am getting the results I require. By the way, does Char ( 9 ) mean? is that the number for a space character?

 

[comment]

Char ( 9 ) is the tab character.

[MutantST]

Thank you again my Anonymous helper