Need four fields to all have same value list

[Tom T]

Hello,

I am developing an index of articles for a newspaper. We have allowed that each article can have four subjects, so I have created four subject fields. No subject is more important than any other. Of course most articles have only one or two subjects so that the third and fourth field do not have as many values in it. This causes a problem in searching.

If one chooses to limit a search by choosing three subjects, say, not all the available values are in the 3rd field.

I thought that if I defined each of the fields so that they all have the same values this would solve the problem. So I made it that the values in Subject2 in the pop-up show all the values in Subject2 plus all those in Subject1.

But to my surprise, I did not get just one long list but a concatenated one! Suppose Subject1 has A, B and C values and Subject2 has D, E and F as values.

What I want is a pop-up list that goes:

A

B

C

D

E

F

What I got was:

A D

B E

C F

How can I solve this?

Thanks,

Tom T

[BobWeaver]

Why not have a single subject field and enter all subjects in that field separating them with carriage returns. That should simplify the search operation and the creation of valuelists, plus there will be no limit to the number of subjects that an article can have.

[Tom T]

I had thought of this solution earlier. I would have to make a repeating field, with say, 4 fields. I decided not to try this solution, at least for now, because I think it will cause a bigger headache.

I am developing this database but in fact already thousands of entries have been made to a previous database from which I have imported the records. If I created one repeating field, wouldn't I have to retype the subjects that are now in four fields for each indexed article?

I thought I could get around this by making each field have the same values, but as you can see from the above, I haven't figured out how to do that either.

Tom

[BobWeaver]

I didn't suggest a repeating field. You definitely don't want a repeating field.

Why not a regular field with all the keywords in it? Makes searching a lot easier. One field can hold up to 64000 characters so it shouldn't be a problem. You can easily merge the existing data into this field using the replace command. It would be using the calculated result option with a formula of the form:

SubjectField1 & "

[Tom T]

Bob,

I tried your suggestion. Either it, or I, fell flat on the face.

The Replace Command syntax is:

Replace (text, start, size, replacement text)

I substituted:

Replace (SubjectField1 & "

[BobWeaver]

One of the confusing things about Filemaker is that there are 3 different replace functions. There is the Find/Replace function in the edit menu, the replace() text function in the list of calculation functions, and then there is the Replace command in the records menu. It was this last one that I was referring to. It lets you set the contents of a field in every record of the current found set all at once. It is very powerful (and hence dangerous too). When you select this command, you get a dialog box with 3 choices:

1. Replace the value in all records with the current contents of the field in the current record;

2. Replace with serial numbers;

3. Replace with a calculated result.

It is the last choice that you want. Then, if you follow the steps in my previous post, it should go according to plan.

[Tom T]

Bob,

Thanks for explaining that. I tried what you suggested. I got a different result that what I was expecting. I applied your formula to each of the four fields. What happened was that the values in the first field remained the same (what I want) but that in the other fields the value in the first field is now also the value in all the other fields.

Perhaps I haven't clearly explained what I want. I want the four values in each of the four subject fields to remain as they are for each record. No change here.

What I want is to combine the value lists of each of the now four separate value lists so that there is one value list to choose from. Now the four separate value lists have both duplicate subjects and the second, third and fourth lists do not have as many values as the first one and in many cases each list has some unigue subjects that the other lists do not have. This gets messy. One big list is better.

The question is how to combine the four lists? Then I can define each of the four fields to choose from this one value list.

Tom

[ESpringer]

I think what Bob was suggesting is not that you "apply [his] formula to each of the four fields" but that you make a fifth text field, call it Subjects_List (or whatever), with the understanding that it will henceforth do all the work of those other fields.

It will initially be blank, of course. But, for all of your existing records, you can populate it with the values from the existing four fields by following his steps.

I agree with others that having four separate fields is not good: it means that someone who tries to run a Find operation on "elections" (or whatever) by typing it into the first field would only come up with those records where "elections" happened to have been entered into the first field; but in some cases the first field will have been "schools" (or whatever), and the fact that "elections" shows up as the second subject of that article will not help it be found... No?

-ESpringer

[ESpringer]

Looking again at your post it seems I'm offering a solution to a problem you don't believe you have. I do think you should have a fifth field with the combined subjects... but you raised a different question: how to get a comprehensive value list.

Perhaps you want to keep your four fields in order to make sure that, for new records, (1) there are not more than four subjects and (2) that each subject is chosen (if possible) from the available ones, to avoid synonyms and prompt recognition...

So, you can not delete those four but make that fifth field into a calculation that always shows the combination of the other values, and (1) have searches done out of fifth field, and (2) use that fifth field as the basis for a value list. It should effectively show all the items that appear anywhere in those initial four fields. It should recognize that paragraph returns separate items, and it will eliminate duplicates.

I'll be curious how it turns out!

[BobWeaver]

Hi ESpringer, I think you hit the nail on the head. While my initial response was to discourage the use of separate fields for searching for the very reason that you explained, I didn't really address the issue of how the data would be entered after the creation of the 5th field.

If the four original fields were to be replaced with a single new field as I originally suggested, it could make it a bit messy to enter multiple items, except by using checkboxes. This would get unwieldy if there were more than just a few choices.

Defining the 5th field as a calculated field with a formula that concatenates the other four fields would give a source for the value list data, and a single search field. But the user could still enter data into the original fields.