Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Removing Excess Commas From Calculation Field


EdY
 Share

Recommended Posts

I'm using FileMaker Pro 6 on Mac OS 10.5.8. For photos in our image library we produce a series of keywords for searching on our website. We have 65 Filemaker keyword fields and each field gets one keyword. Keywords are then exported into the metadata template associated with each image. The keywords must be comma separated. To accomplish that I have created a calculation field that brings all the keywords together and inserts the commas between them. Here's my problem: If I have fewer than 65 keywords I get a string of commas at the end of the keywords in the calculation field representing the empty keyword fields. Here's what it looks like:

ag, agriculture, agricultural, crop, crops, food, foods, fresh, freshness, fruit, fruits, produce, variation, variations, varied, variety, various, vegetable, vegetables, section, supermarket, supermarkets, market, markets, grocery, groceries, store, stores, display, displays, displayed, strawberry, strawberries, berry, berries, red, ripe, ripen, ripening, ripened, ripeness, California, CA, , , , , , , , , , , , , , , , , , , , ,

Is there a way when defining the calculation field to eliminate the commas after the last keyword?

 

Here's the calculation formula I'm using:

Keyword 01& ", " &Keyword 02& ", " &Keyword 03& ", " &Keyword 04& ", " &Keyword 05& ", " &Keyword 06& ", " &Keyword 07& ", " &Keyword 08& ", " &Keyword 09& ", " &Keyword 10& ", " &Keyword 11& ", " &Keyword 12& ", " &Keyword 13& ", " &Keyword 14& ", " &Keyword 15& ", " &Keyword 16& ", " &Keyword 17& ", " &Keyword 18& ", " &Keyword 19& ", " &Keyword 20& ", " &Keyword 21& ", " &Keyword 22& ", " &Keyword 23& ", " &Keyword 24& ", " &Keyword 25& ", " &Keyword 26& ", " &Keyword 27& ", " &Keyword 28& ", " &Keyword 29& ", " &Keyword 30& ", " &Keyword 31& ", " &Keyword 32& ", " &Keyword 33& ", " &Keyword 34& ", " &Keyword 35& ", " &Keyword 36& ", " &Keyword 37& ", " &Keyword 38& ", " &Keyword 39& ", " &Keyword 40& ", " &Keyword 41& ", " &Keyword 42& ", " &Keyword 43& ", " &Keyword 44& ", " &Keyword 45& ", " &Keyword 46& ", " &Keyword 47& ", " &Keyword 48& ", " &Keyword 49& ", " &Keyword 50& ", " &Keyword 51& ", " &Keyword 52& ", " &Keyword 53& ", " &Keyword 54& ", " &Keyword 55& ", " &Keyword 56& ", " &Keyword 57& ", " &Keyword 58& ", " &Keyword 59& ", " &Keyword 60& ", " &Keyword 61& ", " &Keyword 62& ", " &Keyword 63& ", " &Keyword 64& " " &Keyword 65

 

Thanks to anyone who can help.

Link to comment
Share on other sites

How about

If (keyword 01 ≠ "" ; keyword 01 & ", ") &

If (keyword 02 ≠ "" ; keyword 02 & ", ")

etc

¶ ≠

Or could you use

Substitute ( List ( keyword 01 ;keyword 02 ; ...etc ) ; "¶" ; ", " )

Link to comment
Share on other sites

How about

If (keyword 01 ≠ "" ; keyword 01 & ", ") &

If (keyword 02 ≠ "" ; keyword 02 & ", ")

 

think about what happens when there is a keyword 64 but neither a keyword 65 nor a keyword 66. (66 is last).

 

Or could you use

Substitute ( List ( keyword 01 ;keyword 02 ; ...etc ) ; "¶" ; ", " )

 

Hmm! That looks elegant!

Link to comment
Share on other sites

Hmm! That looks elegant!

 

I rather liked it to be honest!

Only two instructions...

 

The first example would end with a comma, but not that big a problem I would have thought?

 

But as usual I bow to the wisdom of...

 

AHunter3. (Someone we all rely on, but never probably gets the thanks he deserves)

Link to comment
Share on other sites

Oops. The elegant one won't work because there was no List () function in FileMaker 5.5. Didn't come along until version 8.5.

 

You could set up a value list of related values only and then use ValueListItems (Status(CurrentFileName), "Value List Name Here") and treat that the same way as List () in this situation, though.

Link to comment
Share on other sites

Thanks AHunter3. Your solution worked perfectly except for having a comma after the 65th keyword. I solved that by ending with: Keyword 64 & Left(", ", Length(Keyword 64) * Length(Keyword 65)) & Keyword 65

 

I tried using your formula to separate the keywords with semi-colons (;), but got the following error message: "Either an operator was omitted, this function cannot be found, or "(" was not expected here." Do you have a solution for this? Thanks for your help.

Link to comment
Share on other sites

Here's the formula I was trying to use (it's exactly the same as the formula that worked with commas, but the commas have been replaced with semi-colons): Keyword 01 & Left("; "; Length(Keyword 01) * Length(Keyword 02)) & Keyword 02 & Left("; "; Length(Keyword 02) * Length(Keyword 03)) & Keyword 03 & Left("; "; Length(Keyword 03) * Length(Keyword 04)) & etc., etc.

 

Thanks for your help.

Link to comment
Share on other sites

Aha.

 

Length(Keyword 03)) & Keyword 03 & Left("; "; Length(Keyword 03) * Length(Keyword 04))

 

You're in FileMaker 5.5. SOME of those semicolons (previously commas) are literal characters (used as separators) but SOME of them are FILEMAKER commas (dividers between clauses of the formula).

 

In FileMaker 5.5, semicolons cannot be used to divide clauses:

 

Left ("something" , Length (SomeField) ) works.

 

Left ("something" ; Length (SomeField) ) would work in FileMaker 7 and beyond but will not be accepted in FileMaker 6 or earlier.

 

When the "something" is a literal comma or semicolon it can be confusing, I know:

 

Left (";" , Length (Keyword XX) )

Link to comment
Share on other sites

Thanks AHunter3 for your last calculation formula. It was close enough to what was needed that I was able, with my limited knowledge, to figure it out. I had to remove one "(" and add a space, and then it worked. Here's the formula that finally worked: Keyword 01 & Left("; " , Length(Keyword 01) * Length(Keyword 02)) & Keyword 02 & Left("; " , Length(Keyword 02) * Length(Keyword 03)) & Keyword 03, etc. Thanks again, I couldn't have done it without your help.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use