multiple value

[Devin]

I'v got a delimina that I need a little help!

Right now I've a a field for called "plated_for" that is a pull down value list.

Some times each record could have more then one item choosen in the "plated_for".

When printing I would like for the field to show each item that was choosen seperated by a /.

example.

if "plated_for" has 525 and the 840 selected then it needs to show it as 840/525.

can this me done or is there a better way to go about this!

Thanks

Devin

[Søren Dyhr]

can this me done or is there a better way to go about this!

It's way too little to go on, I'm afraid! At first sight does it look like more than one fact per field, which is more common a violation, than a proper database structure - if you try to make statistics on the question raised in this forum : - I need not include you- though!

If a order should have more than one item, should it be broken out in a separate table... and perhaps show in a portal.

However if the solution is normalized, can a unstored calcfield make the presentation:

Substitute(ValueListItems(Get(FileName);"RelatedList");"¶";"/")

...based on the dynamic valuelist that gives related values over the relation.

--sd

[John Mark Osborne]

I agree that the solution should be normalized with another table but to solve Devin's problem more directly, the following formula will convert his data into the format he desires.

Substitute(plated_for; "¶"; "/")

[Devin]

You guys just went over my head with solution should be normalized!

right now I got what I need to work somewhat.

I've made a new calc field with:

GetAsText ( Substitute(Plated_For; "¶"; "/") )

it works great expect for when there are more then three itmes check in Plated_For.

[John Mark Osborne]

Normalizing a database means a lot of things but in your case, it means not entering two distinct pieces of data into a single field. Instead, you should enter your data into separate records. You can do this by using a new table and entering the data through a portal. This will allow you to create better reports and use other relational functions better (usually).

The formula I presented earlier will work no matter how many entries are in the field. Multiple selections from a drop-down list are stored as a return-separated list. You can see this if you place a second occurrence of your field on the layout without a drop-down list. And, since the Substitute function looks for all returns, it shouldn't matter how many choices you make.

[Devin]

There is alot atalk about making seperate records but you got to think about the user entering the data. Why would wan't them to enter the data 2-4 times and all that is different is one filed?

Sure it makes since from the tech side of things and that you might have more control for later, but you need to think about the users of the database.

I did get the formula to work! The problem was the filed was to small....duh!

Is there a way to have the text auto resize to fit the field size?

Thanks for your help. I'm allways looking for a better way and I do appreciate you help.

Devin

[John Mark Osborne]

Entering the data in a portal using a popup menu will actually be easier since you won't have to use the Shift key to make multiple selections. It will also be easier to read what selection have been made. So, this approach is easier for users and is relationally sound.

The only option you have for changing the size of a field is on print or in preview mode using sliding objects. This will shrink a field to fit the contents, not enlarge it.

[Devin]

Uhm that sounds very intersting!

So the portal is for the "plated_For"

but how is the Tabel setup for the portal.

The only thing I guess that is the same is the Job#

so should it be a self join Table based off Job#?

Devin

[John Mark Osborne]

You would need a brand new table. It would have two fields. One field would be for the job number (foreign key) and the other for the plate_for field.

[Devin]

That is sweet and Very easy.

I would have never thought of that. Thanks You.

once last question on this matter.

Now how do I grab those plated_for so that it will print with a "/" between them.

Devin

[John Mark Osborne]

Refer to SD's original post for a formula that will grab all the values as a return separtated list and then substitute the returns with slashes. You can also use GetNthRecord to collect the data (instead of ValueListItems) but it's more complicated. You can find out more by examining a file from my web site:

http://www.filemakerpros.com/SumTextDemo.zip

Look at the MegaSumText [JMO] script to see how it can be done. This method will save the need for a value list and allow you to keep the values in the portal in the original order instead of alphabetized.

[Devin]

That's very nice, and I can see where it I could use it in another database, but I don't want this to be a script it should be an autocalc.

I will look thru your script and see ifI can make a go at it.

Thanks

[Devin]

I don't see how this gets me what I need?

It gives me every thing in the vaule list with a / but I need it to only show be what was choosen.

Substitute(ValueListItems(Get(FileName);"RelatedList");"¶";"/")

Devin