Jump to content

Using multiple 'positions' in a calc


This topic is 5723 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by Guest
Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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 ) ; 

"†")

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 1 year later...

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5723 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.