Replace / Substitute character in text calculation

[NYC Newbie]

I have a book database with individual and unique bookIDs for each title.

This bookID field is auto-entered by calculation of first two letters of title, author, publisher and price.

bookID=Middle(title;1;2) &Middle(author;1;2)&Middle(publisher;1;2)&GetAsNumber(price)

This bookID will also be used as a SKU number which prohibits empty spaces. This presents a problem for titles such as "A Day in the Life of Ivan Ivanovich" which yields a bookID of "A SoPe5".

I would like to substitute the empty space with an underscore, e.g. "A_SoPe5". Is it possible to achieve this within the auto-entered calculation or will I need to run a script, perhaps a validation calculation, or some other solution?

Thanks.

[comment]

This bookID field is auto-entered by calculation of first two letters of title, author, publisher and price.

It seems like a very poor choice for something that's supposed to be unique.

[NYC Newbie]

Consultant,

Thanks for your reply, the bookID, as calculated, works for the user who wants an intuitively easy way to ID a product rather than look up a bunch of random numbers.

I am still looking for assistance on my original inquiry regarding replacing / substituting blank spaces in a text calculation.

[comment]

the bookID, as calculated, works for the user who wants an intuitively easy way to ID a product

It may be "intuitively easy" for the user but it should be a no-no for the developer, as this scheme is practically guaranteed to bomb.

Anyway, here's the answer to your question, courtesy of Dr. Kevorkian:

Substitute (

Left ( Title ; 2 ) & Left ( Author ; 2 ) & Left ( Publisher ; 2 ) & Price ;

" " ; "_" )

[NYC Newbie]

consultant,

Thank you so much for your kind reply.

A script step takes care of any repeats and doubles in the bookID field. Fortunately Filemaker offers ways work get around a client's prescriptions without creating a conflict between developer and client.

I have already implemented the substitute formula in the script referenced above. I will try your formula and see if it negates the need to add to the double checking script.

[Vaughan]

I recently worked for a film agency that had a similar code naming convention. These systems fail when items have names that would produce the same code. What happens then?

They had a "protocol" for generating a alternative code that was very complicated and produced codes that had little identifiable with the original item. In the end it turned out that people didn't use the protocol at all and made the codes up randomly just to get on with their lives.

A modern database has no need for codes, show people the item names and let them search on them.

[LaRetta]

A_SoPe5 is not going to be something your Users will easily remember to type to find "A Day in the Life of Ivan Ivanovich." I too have used such schemes (even started several when designing in other arenas). Honestly, is someone going to remember the price of a book is 5.99 instead of 5? Are they always going to know the Publisher? But they WILL remember "Day In Life".

As indicated by two top Developers, this scheme is prone to problems; been there, done it as well. It is easier to present those fields and let a User type in what they know to find what they need; no 'looking up random numbers' at all. You can even use type-ahead techniques and global searches such as *dog* to find all fitting books or get a list of books by one publisher.

IDs are supposed to be meaningless. The BookID should be auto-enter, FM-generated serial number - no need for scripts and possibilities of duplicates because of multi-user.

Oh and ... what happens if you change the price of a book? You will break your relationships. :crazy2: