Calcuation to list fieldnames and their values

[Kingme]

I'm looking for a calculation that will take the "fieldname" and it's "value" and put it together. For instance.

I have the following:

FIELD NAMES:

filmtitle

runtime

rating

THEIR VALUES:

Great New Wonderful

25 minutes

Rated R

I wish for them to appear in a SINGLE (text) calculation field like this:

CALCFIELD(result is text)=

`filmtitle` = "Great New Wonderful", `runtime` = "25 minutes, `rating` = "Rated R"

---------

You can see where I'm going with this... I've looked in the function "Fieldnames" and it does return the names of the fields, but I can't seem to figure out how to return the value along with the fieldname.

Edited September 28, 2006 by Guest

[Raybaudi]

Hi Kingme

if you have only FM 8.5 and not the advanced version, you may create a calculation with the names of fields hard coded into...

but try this file that use a custom function and that doesn't need to hardcode anything, but the calc field that must be named:"AllFields".

FieldNamesAndContents.zip

[Lee Smith]

The custom function presented by raybaudi gathers the data for any and all fields in the TO (add one to his sample and you will see what I mean). The custom function is only usable by you if you have either Developer version 7, or Advance version 8 or 8.5. Otherwise, you will need a calculation field like this:

Field (Calculation, Text Result) =

"'filmtitle' = " & " " & Quote ( filmtitle ) & " 'runtime' = " & Quote ( runtime ) & " 'rating' =" & Quote ( rating )

HTH

Lee

[Raybaudi]

Hi Lee

you are right for the problem of that custom...

So now it looks so:

/*

FieldNamesAndContents custom function

Format: FieldNamesAndContents ( start ; stop )

Parameters:

start: number - order-position of the first field in the layout that you want to get the value from

stop: number - order-position of the last field to get the value from

Note: the field holding this calculation MUST be named: "allFields"

*/

Let([

fieldNames = Substitute ( FieldNames ( Get ( FileName ) ; Get ( LayoutName ) ) & ¶ ; [ "allFields¶" ; "" ] );

name = "'" & GetValue (fieldNames; start) & "'";

content = GetField ( GetValue (fieldNames; start) )

];

Case (

start < ValueCount ( FieldNames ) and start < stop; name & " = "" & content & "", " & FieldNamesAndContents ( start + 1 ; stop ) ;

name & " = "" & content & """

)

)

BTW: your calc losts the commas !

"'filmtitle' = " & " " & Quote ( filmtitle ) & ", 'runtime' = " & Quote ( runtime ) & ", 'rating' =" & Quote ( rating )

[Lee Smith]

Hi Daniele,

I wasn't being critical of your first CF, and the way it worked may have been what Kingme wanted.

My calculation did not have the commas because I missed seeing them in the sample (old eyes do that),

Here is a revised calculation for Kingme:

"'filmtitle' = " & " " & Quote ( filmtitle ) & ", 'runtime' = " & Quote ( runtime ) & ", 'rating' =" & Quote ( rating )

I had trouble getting the new CF to work, it wants to break just before the start field is.

Lee

Edited September 28, 2006 by Guest

[Raybaudi]

I wasn't being critical of your first CF

Hi Lee

I didn't say that... I said that you were right !

My calculation did not have the commas because I missed seeing them in the sample (old eyes do that),

... and they are tiny !

I had trouble getting the new CF to work, it wants to break just before the start field is.

I do not understand what you say here, can you explain me better ?

The example doesn't work for you ? ( I changed the first attachment )

... and have you named the calculation field: "allFields" ?

[Kingme]

Hi Lee

Let([

fieldNames = Substitute ( FieldNames ( Get ( FileName ) ; Get ( LayoutName ) ) & ¶ ; [ "allFields¶" ; "" ] );

name = "'" & GetValue (fieldNames; start) & "'";

content = GetField ( GetValue (fieldNames; start) )

];

Case (

start < ValueCount ( FieldNames ) and start < stop; name & " = "" & content & "", " & FieldNamesAndContents ( start + 1 ; stop ) ;

name & " = "" & content & """

)

)

This is a brilliant calculation. I didn't want to hard-code each fieldnames into a calculation. I needed it to change based on the fields in a particular layout. (So I can just drop a field in a layout and it auto-changes the calculation, etc)

This seems like something that would work, I've tested it and it seems so. Thanks for the help on this. IT IS ABSOLUTELY appreciated.

I'll be in touch of anything I may change or add to the calculation.

[Lee Smith]

Hi Daniele,

I figured it out, when I pasted the formula you used in the post, I forgot to update the CF name from FieldNamesAndContents to FieldNamesAndContents2. Duh...

Lee

[Kingme]

I just realized that you made a custom function for this... It worked great on the file you attached... But alas I cannot use this file.

So there wasn't a calculation to do this without "advanced" version?

Or should I just use your attached file to "link" to my database?

[Lee Smith]

Without the Advance (Developer v7) editions, you can not Define a Custom Function in your files. Daniele mentioned this in his first post, and that is why I followed up with the old fashion method of a Concatenate calculation. in other words, you are going to have to create this calculation for each occurrence.

HTH

Lee

[Kingme]

Thanks Lee.

So annoying to use the old concatenate way. When I add a field, I got to change so much information in that calculation, and make sure I have all the text spelled correctly, as you now, one typo and blam! it's over.

Just thought that since I've moved from Filemaker 6 to 8.5, something would have better on this front.

Thanks!

[Lee Smith]

They reserved all of the good stuff for the Advance edition so they can justify the difference in price.

I use TextWrangler when I need to do repetitive modifications such as what you describe.

I'm curious though, why the need for so many occurrences of this calculation?

Maybe there is a better way to do what you want, why not fill us in what you need?

HTH

Lee

Edited September 29, 2006 by Guest

[Kingme]

Thanks again for the reply.

As you may be able to tell from the result of the calculation, I'm looking for a auto-magic way to create an "insert" query from mySQL.

For filemaker 6, I have over 35 fields that I have hand-coded and concatenated to fit the right format. (i.e. 'filmtitle' = "Great New Wonderful") etc.

Once I use the Excute SQL function in a script. It takes all the 35 fields, along with an opening statement (i.e. INSERT INTO tbl etc) and "uploads" the data to a mySQL database.

I've seen many different ways to use SQL/mySQL and filemaker together but I haven't seen anything that just is one calculation without alot of typing involved.

So with an upgrade to 8.5, I have decided to mirror my fields in Filemaker with the fields in the mySQL database. ("filmtitle" field in mySQL and "filmtitle" field in Filemaker)

I figured they must be a way to take the "fieldnames" of each of my Filemaker 8.5 fields and their "contents" to auto-magically create an insert/update query into mySQL. And since my fieldnames in both databases are exactly the same, I figured this may be possible.

I like the ideas you shown me and possibly can take the idea of dumping everything to a text file and then re-importing, like you've suggested. Although I think it would be much easier to do your first calculation since, on-the-fly, the calculation changes when I need to add or subtract a field simply by changing the fields in the layout.

But as you've pointed out, dang Filemaker doesn't play nice.

[comment]

This needs to be a recursive calculation (since the number of fields is variable). In the regular (i.e. not Advanced) version of FMO, you can still do recursive calculations by scripting the process. Since you need the result in a script anyway, this should not be a problem.

[BobWeaver]

You don't need a custom function or a script. The following calculation will return a list of all fields on the current layout, and their contents:

Evaluate(

"Let(F=""&

Substitute(FieldNames ( Get ( FileName ) ; Get ( LayoutName ) );¶;

"";F&"="&Quote(GetField(F)))"

&"&¶&"&

"Let(F=""

)&

"";F&"="&Quote(GetField(F)))"

)

However you have to be careful how you use it. If you define a calculated field or autoenter field with this calculation, and put it on a layout, then when it evaluates, it will try to evaluate it's own contents and get into an infinite loop. I've managed to lock up FM8.5 doing this (but not FM7). So, a slightly more complex calculation which skips calculating its own contents is as follows:

Let([

DummyTrigger=ModTimestamp;

Fields=Substitute(FieldNames ( Get ( FileName ) ; Get ( LayoutName ) )&¶;"FieldData¶";"")

];

Evaluate(

"Let(F=""&

Substitute(Left(Fields;Length(Fields)-1);¶;

"";F&"="&Quote(GetField(F)))"

&"&¶&"&

"Let(F=""

)&

"";F&"="&Quote(GetField(F)))"

)

)

In this case, I'm assuming that the calculated field name is "FieldData." Otherwise, change the name on the third line of the calculation accordingly. Also, if you want to ensure that the display updates whenever any data changes, the line: DummyTrigger=ModTimestamp, needs to be present. ModTimestamp needs to be a timestamp field set to autoenter the modification timestamp.

[BobWeaver]

Here's a sample file that shows the use of both the longer formula on the first layout, and the shorter one on the second layout.

FieldData.fp7.zip

[comment]

Very nice, Bob. May be a bit over-complex for this one, but it's a neat technique.

[Kingme]

Wow. Now that's a brilliant calculation. Absolutely will save designers hours and hours when doing insert/update queries to mySQL.

How do you suggest I go about getting "single quotes" around the fieldNames? (i.e. 'filmtitle' = "Great New")

[Kingme]

And I guess, how do we get the commas after each "fieldcontent"

like in ----- 'filmtitle' = "Great New" , 'runtime' = "23 min", ...

[Raybaudi]

Very nice, Bob

I can understand the need of the "FieldData" field ( somethig like my "AllFields" field ), and I can understand the need of the DummyTrigger ( I used Evaluate( ) to see any data changes )...

But I can't understand the logic that is into your calculation...

So, please, can you "comment" it ?

[BobWeaver]

Following is the formula to put single quotes around the field name and commas between values.

Evaluate(

"Let(F=""&

Substitute(FieldNames ( Get ( FileName ) ; Get ( LayoutName ) );¶;

"";"'"&F&"' = "&Quote(GetField(F)))"

&"&", "&"&

"Let(F=""

)&

"";"'"&F&"' = "&Quote(GetField(F)))"

)

I've also attached an new copy of the file.

Explanation:

This is just a variation of a formula that I used in my scriptless audit trail example. Refer to this Link in the Samples Forum for more info.

While it is a bit abstract, it really isn't all that complicated. What it does is use the Substitute() command to transform the field list into an expression similar to the ones given earlier in this thread. In the case of a field list containing "Name", "Address", & "City" it builds this expression:

Let(F="[color:red]Name[color:green]";"'"&F&"' = "&Quote(GetField(F)))&", "&

Let(F="[color:red]Address[color:green]";"'"&F&"' = "&Quote(GetField(F)))&", "&

Let(F="[color:red]City";"'"&F&"' = "&Quote(GetField(F)))

The parts in red are the original values from the field list. The part in green is what was subtituted for the line breaks in the field list, and the rest (in black) at the beginning and end is just concatnated on. The actual calculation formula looks messy because all of the quote characters need to be escaped with a backslash.

Now, If you evaluate this expression, you will get:

'Name' = "Homer Simpson", 'Address' = "123 Elm Street", 'City' = "Springfield"

...assuming that those are the actual contents of the fields.

In the new example file, I added a button that displays the unevaluated expression so you can see the intermediate result.

I think the only thing that deserves further explanation is why the Let() function was used. You will notice that each field name needs to be referenced twice, but only appears once in the original field list. So, the Let() function is used to assign each occurrence of a field name to the variable F so that it can be used twice: once just to display its own name, and once in the GetField() function to display its value.

FieldData_r1.fp7.zip

[Søren Dyhr]

Very nice, Bob. May be a bit over-complex for this one, but it's a neat technique.

In real life, are these interactions or syncronizations with syntactical different database tools - fairly more common than we wish it to be! I managed to see a pretty robust solution to my addressbook problem here!

The next issue I have to struggle with is which plugin it should be that issues the shell script command ...so thanks a lot Bob!

--sd

[Raybaudi]

Hi Bob

thank you very much for the explanations and for the calc :D

[Kingme]

Brilliant Bob, brilliant.

I'm no expert, but I've been using and designing "simple" databases for 4 years now, and it's great to see Filemaker 8.5 get some balls, if you will.

Thanks again for the calc. It's endlessly helpful, especially the explanation.