Is it possible to combine a single field from all records?

[sdutton]

I'd like to create a calculation that would combine a single field from all my records.  For example, I have a calculation for a person's entire name (called EntireName), bringing the first and last name together.  I would like to create another calculation field that will combine EntireName for every record . . . the final field result would look like this:

 

Peter Jones

Mary Smith

John Timber

 

(Each record's entry needs to be followed by a paragraph return.)

 

Thanks,

 

Steve

[bcooney]

List ( ) with a self-join. But, where are you going with this?

[doughemi]

Unfortunately the List() function didn't appear until 8.5 .  You can create a value list using values from EntireName, and then create a calculation field NamesList=ValueListItems("YourFileName", "TheValueList").

[sdutton]

List ( ) with a self-join. But, where are you going with this?

This calculation looks to be the most straight forward.  Sorry, could you explain the "self-join" aspect?  How does the calculation look if the field that I'm attempting to combine from all the records is EntireName?

 

List ( field {; field…} )   What will this look like in the calculation box?

 

Thanks,

 

Steve

[bcooney]

Sorry, as doughemi points out, your version of FileMaker doesn't have List ( ) yet. You are using FM v8, right? If not, please update your profile.

[sdutton]

Sorry, I have v12

[Lee Smith]

Then Please update your profile to reflect it. Here is a quick link where you can show the current version 12, your OS and Platform. Update Profile

[bcooney]

OK, so read about the List ( ) function. A self-join is a relationship that uses the same table for parent and child.

 

However, I would still like to explore why you need this "list" of names in one field. My instinct tells me that you might be headed in the wrong direction.

[sdutton]

I've updated my profile.

 

I used the example of EntireName to keep it simple.  I actually have created a calculated field that has all the properly formatted data for a vCard for each record.  I created a script that can export that vCard field to a .vcf file keeping all the paragraph returns intact.  But the Export Field Contents only works on an active record.  So, I want to create a calculated field that will combine all the vCard fields from all my records into one field, which I will then be able to run my Export Field Contents script to create a single vCard will all my records.

[bcooney]

Why couldn't you use Export Records?

[sdutton]

Export Records strips the paragraph return out of the fields upon export.  So my result would look something like this:

 

Peter Jones 123 Main St. Chicago IL 60111

 

as opposed to 

 

Peter Jones

123 Main St.

Chicago

IL

60111

 

The Export Field Contents in a script that I created works perfectly, but as I said, it only works on the active record.  I have created a single calculation for each record that brings all the vCard elements for that record into a proper format.  Something like this:

 

 

BEGIN:VCARD

VERSION:3.0

N:Timber;Archibald & Karen;;;

FN:Archibald & Karen Timber

TITLE:Vice President / Janitor

item1.EMAIL;type=INTERNET;type=pref:[email protected]

item1.X-ABLabel:email (1)

item2.EMAIL;type=INTERNET:[email protected]

item2.X-ABLabel:email (2)

item3.EMAIL;type=INTERNET:[email protected]

item3.X-ABLabel:email (3)

item4.EMAIL;type=INTERNET:[email protected]

item4.X-ABLabel:email (4)

item5.EMAIL;type=INTERNET:[email protected]

item5.X-ABLabel:email (5)

item6.EMAIL;type=INTERNET:[email protected]

item6.X-ABLabel:email (6)

TEL;type=HOME;type=VOICE;type=pref:+1-987-654-3482

item7.TEL:+1-987-654-4567

item7.X-ABLabel:work (1)

item8.TEL:+1-987-654-3210

item8.X-ABLabel:work (2)

item9.TEL:+1-987-654-3365

item9.X-ABLabel:work (3)

item10.TEL:+1-987-654-7354

item10.X-ABLabel:work (4)

item11.TEL:+1-987-654-3963

item11.X-ABLabel:mobile (1)

item12.TEL:+1-987-654-3000

item12.X-ABLabel:mobile (2)

item13.TEL:+1-987-654-8363

item13.X-ABLabel:mobile (3)

item14.TEL:+1-987-654-9753

item14.X-ABLabel:mobile (4)

TEL;type=WORK;type=FAX:+1-987-654-9373

ADR;type=HOME;type=pref:;;123 Main St.nSuite 34;Wheaton;IL;60187;USA

ADR;type=WORK:;;456 Main St.nSuite 23;Wheaton;IL;60187;USA

NOTE:Allergic to oranges

item15.URL;type=pref:webcams

item15.X-ABLabel:_$!<HomePage>!$_

item16.X-ABRELATEDNAMES;type=pref:Rob • Melissa • Maria • Kyle • Joshua

item16.X-ABLabel:children

END:VCARD

 

Now I would like to create a single calculated field that would combine all the vCard fields from all my records.  (The field that I'm choosing to combine in a calculation is irrelevant.  That's why at the start of my question, I just posited the field "EntireName".)

 

I hope that makes sense.

 

Steve

[bcooney]

OK, so have you looked at List ( )?

[sdutton]

I've read the section on the List function in FileMaker Help.  From your earlier response concerning "self join", I'm assuming I need to duplicate my contact table, make the proper link, and then in the parentheses, place that new table and the field that has the vCard . . . sorry, I'm not very knowledgeable when it comes to tables and relationships.

 

My original table is called "Contacts".  I duplicated it and called the new one "Contacts 2"  The calculated field that I created for the vCard layout is called "z_vcard27Compiled"

 

I entered this for the List calculation:

 

List ( Contacts 2::z_vcard27Compiled )

 

but all I get is one record's vCard.  It's not listing all my record's vCards.  Not sure if I'm not creating the right table, linking the right item in the table, having the right operator between the tables . . . what am I doing wrong?

 

Thanks,

 

Steve

[Rick Whitelaw]

What relationship exists between your "original" table and the new table occurrence? Also, what is the operator in the relationship?

[Ocean West]

If you have v12 look at the execute SQL function.

[Opotoc]

Without any understanding of relations you can:

just loop through your selection, get each vcard into a variable and export the contents of that variable using a global field:

 

Go to first record

Loop

Let ( $vcardlist = List (  $vcardlist ; vcardCalculationField )

Go to next record [exit after last]

End loop

SetField[ some_global_text_field ] = $vcardlist

ExportFieldContents [ some_global_text_field ]...

 

Best,

Alexander

[doughemi]

I've read the section on the List function in FileMaker Help.  From your earlier response concerning "self join", I'm assuming I need to duplicate my contact table, make the proper link, and then in the parentheses, place that new table and the field that has the vCard . . . sorry, I'm not very knowledgeable when it comes to tables and relationships.

 

My original table is called "Contacts".  I duplicated it and called the new one "Contacts 2"  The calculated field that I created for the vCard layout is called "z_vcard27Compiled"

 

I entered this for the List calculation:

 

List ( Contacts 2::z_vcard27Compiled )

You're 90% there.  The relationship between the two should be Contacts::<any field> X Contacts 2::<any field> using the X or Cartesian operator.  This relates all the records in Contacts 2 to any record in Contacts, which is exactly what you need.

[sdutton]

Thank you (to all) . . . I had an = instead of the x.  It works great!