How to suppress duplicate entries in value lists?

[Comadeuro]

Hi,

I have a table "tour" with a "car_no", a "driver", a "co_driver" and some more fields. Because "driver" and "co_driver" mostly use the same car I want to ease the entry of a new "tour" by building a value list of previous drivers of that car.

I defined a self-relation of "tour" with "car_no" as connector and a value list of driver_no and driver_name with only related values. Everything works fine, but the list of a car grows with every new tour of that car.

How can I suppress duplicate entries, though the list shows only different previous drivers of a car.

How can I filter the list values to use only tours of a given time period?

Maybe this are simple questions. Please be patient, I'm new to FileMaker and couldn't find a clean and elegant answer up to now.

Thanks,

Comadeuro

[-Queue-]

By which field is your valuelist sorting? If it's a unique field, then you should only see one occurrence of each car_no for it.

[Comadeuro]

It's sorted by driver_name. Yes, I only see one car_no but all records of that car_no with multiple entries of the same driver_name. I want to choose one of the previous drivers, so every driver_name should occur only once.

Any idea?

Comadeuro

[-Queue-]

I think I'm missing something. Can you attach your file?

[Comadeuro]

Ok, here is a simple example, which demonstrates my problem.

Go to the layout "Ergebnis" and click into a "Nr." field. You'll see a valuelist with all drivers whichever are recorded in "teilnehmer" under this number. Try number "19", which has different drivers.

FormulaOne.fp7.zip

[-Queue-]

If you edit the valuelist, you'll notice it says the t_fahrer_name cannot be indexed, so it will not work. I would suggest making t_fahrer_name a lookup rather than an unstored calculation. Although, if t_fahrer is a unique id, then you don't need the additional field in the valuelist.

See if the modified file works for you.

FormulaOne.zip

[Comadeuro]

If you edit the valuelist, you'll notice it says the t_fahrer_name cannot be indexed, so it will not work. I would suggest making t_fahrer_name a lookup rather than an unstored calculation.

Ok, I'll have to understand the difference.

Although, if t_fahrer is a unique id, then you don't need the additional field in the valuelist.

Right. But human beings are not very good in remembering pure numbers. Reading names as text would be much easier for the users.

See if the modified file works for you.

Yes it does. It has given me the significant hint: when sorting the value list by the second field all and multiple entries show up. Sorting by the first field shows only distinct values. That's what I want.

Is this strange behavior (result sets depend on sorting) intended?

Thank you for your help,

Comadeuro

[-Queue-]

Because the calculation of the related field can't be indexed, the valuelist can't be populated correctly. So any behavior you witness is probably going to be strange. The difference between an unstored calculation of a related field and a lookup of that field is that if you change data relating to that field, a lookup will not change unless you perform a Relookup, whereas the calc will constantly update. So the lookup gives you a snapshot in time, very useful for creating invoices and such where you want the data to remain the same (say for clients or products), and a related calculation is good for when you want the data to constantly be updated. In this case, using a lookup should be fine, unless you want to keep track of when one of the related names changes, without performing a Relookup.

I think that using the pop-up list with both id and name in a portal, where the portal's relationship is different from that of the valuelist, may also be a problem; but I didn't scrutinize the file deeply enough to determine whether it should be an issue. I can't interpret the words enough to figure out what's supposed to be happening anyway.