Using multiple 'positions' in a calc

[Emma in England]

As usual, hard to summarise in a heading!

My database processes text for a mail order catalogue. Various ranges of products need a dagger (†) to appear after their description to show they are exempt from a money back guarantee. I have a field for each product called 'Dagger' which is currently defined as follows:

If ( Position ( Line1OfDesc ; "Earring" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "fridg" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "freezer" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "washing machine" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "washer" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "condenser" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "dishwasher" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "cooker" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "gas hob" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "electric hob" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "digital camera" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "camcorder" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "memory card" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "memory stick" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "SD Card" ; 1 ; 1 )≠0 or Position ( Line1OfDesc ; "XD Card" ; 1 ; 1 )≠0; "†" ; "" )

- it is already somewhat out of hand, and more and more categories keep coming to light. What I would like is to have a separate table to which I can add phrases which can trigger a dagger(!) And then a calculation which says something like 'if any of the phrases in this table are in the description, put a dagger'.

I can't use a script for this, it has to happen on the fly as products are constantly being added by operators. Also, I have Filemaker 8.5, not advanced, so can't use custom functions. I'm sure there's something obvious which has escaped me, any suggestions welcome!

Edited September 25, 2007 by Guest

[Inky Phil]

Hi Emma

Since this monster could just grow and grow and also since the operators are adding the caregories on the fly could you not get them to check a box for exempt items as they do so.

Your 'dagger' field would then be

Case(notisempty(exempt);"dagger";"")

which would not need updating

HTH

Phil (also from Rainy England)

[Emma in England]

Unfortunately not. The items are created in a number of possible ways, but always via a text or data import, not manually. And everytime they recur in another publication, a new record is created, which is publication-specific (believe me, it's the only way - the prices and saves change, the description may have changed, etc etc).

Does this mean you can't see a way to me to do what I want? I guess it can just grow and grow, I don't like it but Filemaker seems to gobble it up happily!

[comment]

How are the items separated?

[Emma in England]

do you mean the source files which are imported?

They are tab separated from an Excel file. But items can also be created by a Filemaker script which goes through unstructured text supplied by the customer, and 'recognises' anything that might be a catalogue number (7 digits with or without an oblique after 3) and creates a new record for each one it finds. Further details are then supplied from the Excel file. Descriptive text is pulled in from Word docs downloaded from the customer's web site and put through 'Word to FM' application. It's all horribly complicated - the customer supplies a mish-mash of Excel, Word, and printed material. My solution was written to try and pull them all together and cut down on errors. It's working well, but I'd like to get these daggers in more elegantly!

[comment]

Sorry, that's not what I meant, but never mind. It does sound horribly complicated, and I believe you should solve this on a higher level. It is not clear what your records represent, but in general there should be a Products table somewhere. Exempt products should be marked as such in this table. Then, when a product is included in another table, you can tell if it is exempt or not by looking at the related record in the Products table - instead of searching for a needle in a haystack.

[Emma in England]

You are right - unfortunately we do not hold the complete database of products, and new products are added constantly, while old ones fall off. So a look up isn't an option, though I can see it would be a good way to go. We are not the database holders, this is simply a tool to aid catalogue production in an awkward situation!

[comment]

new products are added constantly, while old ones fall off.

That is all right. The question is HOW and esp. WHERE are the new products added. Why can't they be added to a Products table (and marked as exempt or not, along with other qualities?

In any case, if you need to maintain the current inflexibility, the best solution would be indeed a custom function. Lacking even that, I don't see a choice but to struggle along with your mile-long formula, which could be slightly simplified to:

If ( 

Position ( Line1OfDesc ; "Earring" ; 1 ; 1 ) + 

Position ( Line1OfDesc ; "fridg" ; 1 ; 1 ) + 

Position ( Line1OfDesc ; "freezer" ; 1 ; 1 ) + 

Position ( Line1OfDesc ; "washing machine" ; 1 ; 1 ) + 

Position ( Line1OfDesc ; "washer" ; 1 ; 1 ) + 

Position ( Line1OfDesc ; "condenser" ; 1 ; 1 ) + 

Position ( Line1OfDesc ; "dishwasher" ; 1 ; 1 ) + 

Position ( Line1OfDesc ; "cooker" ; 1 ; 1 ) + 

Position ( Line1OfDesc ; "gas hob" ; 1 ; 1 ) + 

Position ( Line1OfDesc ; "electric hob" ; 1 ; 1 ) + 

Position ( Line1OfDesc ; "digital camera" ; 1 ; 1 ) + 

Position ( Line1OfDesc ; "camcorder" ; 1 ; 1 ) + 

Position ( Line1OfDesc ; "memory card" ; 1 ; 1 ) + 

Position ( Line1OfDesc ; "memory stick" ; 1 ; 1 ) + 

Position ( Line1OfDesc ; "SD Card" ; 1 ; 1 ) + 

Position ( Line1OfDesc ; "XD Card" ; 1 ; 1 ) ; 

"†")

[Emma in England]

That's interesting - what are the pluses? That would certainly help and seems to be the only way. Don't worry - I'll look it up in my book tomorrow!

[comment]

The pluses are addition signs, nothing else. You could use "or" instead, but there's no need to convert each individual Position() result into a Boolean. You can add them up and have FMP evaluate it at the end. The bottom line is you want the dagger unless ALL the interim results are 0, so if their sum is not 0, it means at least one of the text strings is there.

[Emma in England]

That's lovely and much easier to work with. Thanks for explaining.

[comment]

Just remember this is an ugly hack. You are storing data in a calculation formula - that's not a good thing.

[Kingme]

Thought I'd ask for another "position" calc field I'm trying to do as well.

I've got some IFRAME code that I'd like to extract the link from and then use that url as the url for the webviewer. Here's some sample code:

Now, how can we extract simply the URL (the portion in red? I want the URL so I can simply use it in WebViewer as Filemaker doesn't seem to support showing IFRAME code in webviewer.

Any ideas?

[comment]

Here's a generic formula for extracting text between known prefix and suffix:

http://fmforums.com/forum/showpost.php?post/274556/

BTW, I don't see a problem with embedded iframe in web viewer.

[Kingme]

Hi comment,

could you explain? When I tell webviewer to go to that

It changes all the "<" to gibberish and doesn't sow anything.

[comment]

A web viewer is a browser - you have to point it to a valid URL. IOW, the iframe element needs to be embedded in a HTML document. Where is your source coming from?

[Kingme]

Yes, this is why I wanted to extract the URL from all that IFRAME code.

I'm creating a "web banner" database, that allows user to copy and paste "IFRAME amazon code" for a web banner.

I'd like to show them a "preview" of what the banner would look like and since web viewer, like you said is a web browser, I needed to extract that URL from the code they insert.

[comment]

I am not sure I follow this, but try setting your web viewer to:

"data:text/html," & YourTable::YourField

and put your iframe code in the referenced field.

[Kingme]

Thanks for that. It didn't work for what I'm doing but I can use it for other stuff. I believe I will need to use your previous method to extract the url from the iframe code and then point the webviewer at that url.

Basically, I want to show a preview of the banner that Amazon is hosting through the iframe code.

This solution is completely done in filemaker, meaning I'm not uploading this code to any html or website. It's a local solution to allow users to copy and paste iframe code into filemaker then "show" them a preview of what that iframe code would look like.

Anyways, thanks for the assistance. Mucho appreciated.

[comment]

It works for me with a test iframe here:

It could be blocked from the Amazon server's side - but then the URL probably won't work either.

[Kingme]

Just so I'm clear..

You are setting webviewer to "point" to the exact text of:

By the way, using the evaluation you linked to, to extract the src parameter is working great, so it isn't amazon blocking anything.

[comment]

No, my web viewer is defined as:

"data:text/html," & YourTable::YourField

and the field contains this text:

[Kingme]

Okay. I believe the final trick is to disallow "interaction with web viewer" - when I unchecked that box.. the "data/html," trick worked!

Very nice trick indeed! Both methods work!