calculating text date to numbers & text

[JayJay]

I have one text field called SERVICES NEEDED with checkboxes indicating what services the user still needs to buy for her wedding:

Accessories & Jewelry

Alterations & Preservation

Bridal Attire

Caterer

etc..

I was manually converting that text to numbers for submitting the associated codes to customers. Each topic has a number associated with it:

Accessories & Jewelry is 2

Alterations & Preservation is 4

Bridal Attire is 11

Caterer is 20

So I was manually typing in 2,4,11,20

I want to create a calculation field to do that automatically. So the new Calculation field would look at SERVICES NEDDED FIELD and create numbers from it with a comma in between each number. I'm able to get it to work with one word but not multiple words. Here's what I tried...

If(Services Needed ="Accessories & ewelry";"3, ";"") &

If(Services Needed ="Alterations & Preservation";"4, ";"") &

If(Services Needed ="Bridal Attire";"6, ";"") &

If(Services Needed ="Caterer";"9, ";"")

NOt working...Any ideas? It seems simple enough.

[LaRetta]

The simple way is to use a table and it would hold the number and description. Otherwise, when you change anything, you will need to open that calculation and change it. A table will be much easier.

And the reverse would be easier as well, you should code the number and then just display the description when needed. Your customers (depending upon your setup) could easily get the items as a numbered list separated by commas or however you wish.

With a bit more information about your structure, we could walk you through it. Would you be willing to post an empty, zipped clone? Otherwise, just the tables and key fields should be enough. :wink2:

[LaRetta]

Even with what you've given me, if you create a table for the topics as described, then you can create a relationship from:

MainTable::ServicesNeeded = TopicsTable::Description

Your calculation to turn this into a 'number list' would then be:

Substitute ( List ( TopicsTable::Number ) ; ¶ ; ", " )

[comment]

I am puzzled why a customer would prefer seeing "2,4,11,20" instead of meaningful terms.

[LaRetta]

Good question!! When I read it, "for submitting the associated codes to customers", I flashed on the possibility that the data will be going to another system for translation (uploaded system) and they needed to send it as numbers. But I shouldn't have assumed at all!

[JayJay]

Our customers import the data into their databases or they simply open the data in Excel spreadsheets. They like to search on a number for their field versus a text name.

[JayJay]

Attached is a very stripped down clone of the database. The original is on a server published to the web. I only kept 2 layouts in this clone. A user (bride) see the called Online REg WEDDING PLANS on the web. The MAGAZINE layout is just for internal purposes.

So she checks off the SERVICES that she still needs. On the MAGAZINE LYOUT, you see the words inserted into the field. Below that SERVICES NEEDED field, are my generic scripts of each category number that my staff inserts if they're doing data entry and it's not automatic.

THanks for offering to look at it!

BrideDBTest.zip

[LaRetta]

Hi Jay,

First, you have 20 value lists which are all simple Custom Value List "yes". Are you aware that you can use the SAME value list and simply attach it to different fields? A value list just determines what can go into a field - ANY field. So you can do away with 19 of them and re-point those fields to the same value list.

But it indicates a deeper problem ... You do not have a normalized structure, meaning you have a flat file and you are not taking advantage of relationships. Duplicate fields, such as OnLineRegistrationOct08 and OnlineRegistrationJan09 etc all overly-complicate your solution. And I see no BrideID. Every table should have an auto-enter serial.

Your Users should be simply selecting the Service Requested, eliminating the need of all those scripts to set the number. And by using a ServicesAvailable table, you can change the wording in the description and add records as your needs change.

Attached is how to simply accomplish what you've requested. The yellow field is the checkbox field (servicesRequested) and it shows that it inserts the number for you (no script needed). Then the calculation shows how you can produce the one-liner with commas. Your value list for the checkbox should now be based upon this ServicesAvailable table (open the value list to explore how it is set up). There are a few limitations with this method and, along with re-structuring your solution, I would also recommend considering these additional changes:

1) If you think you will ever want to produce a summary report which is sub-totalled by service requested 'item' then, instead of using a multiline checkbox field, you should consider inserting each selected service into a related table as well. Because Mary Jones' selection of Dance Studio can't appear in the Dance Studio sub-summary and also under the Favorite Gifts summary (because one record can only appear in ONE place).

2) The multiline checkbox can run out of room and, if you added more items, you risk that the checkbox will not display them, ie, it won't expland to show new items. If your items grows, then you may wish to switch to portal (services requested as records) anyway.

For now, let's start with ONE relationship ... and it will be the one I suggested above. Let's replace those scripted buttons and make things easier for you (see attached). And I hope I gave you additional things to consider; particularly the serious design flaw with duplicate fields (just different month/years) within same file. They should be records.

Bridal.zip