How to produce an ID from a name VL in FMP7

[SlimJim]

Recently in a different post

http://www.fmforums.com/threads/showflat...true#Post167239

I described a difficulty I had with a value list and S

[-Queue-]

This should work, but you need to validate the ClientName as unique, or you may end up with the wrong ClientID.

There is no need for ClientID to be a number, either. Only fields that will be used for mathematical purposes need to be a number. But it is up to your preference.

[SlimJim]

I agree. This only works if both relationships are one-to-one.

[Ender]

This only works if both relationships are one-to-one.

Well, many-to-one anyway.

[SlimJim]

As I said in my first post I have only implemented this the once; I have not spent a lot of time thinking about conditions. One might say that it always works, since you are choosing an existing name and so there will be at least one related record (I assume?) and that will have an ID number (I assume?).

The question boils down to whether or not you are looking for predictable results or just simply a result. I would favour the first of these so I think you are looking for a single ID. Whether you achieve that result will depend on the structure of your database. I think tthe only case you are trying to avoid is one name many IDs - but I'm not even sure about that because I think FMP7 will always make the same choice. One name many related records single Id is OK.

For example a contact database where each individual has both a company ID and an individual ID. There are numerous ways of getting a good result here.

Name = company name, ID = company ID; many related records, single ID - good result

Name = individual name; ID = individual ID; one related record, single ID - good result.

Name = individual name, ID = company ID; many names produce the same single ID - good result (this is not really any different to the previous one except in the choice of ID).

Name = Company Name, ID = individual ID; many related records multiple ID's one will be picked (the first one?) is this good or not?

[Ender]

I think tthe only case you are trying to avoid is one name many IDs - but I'm not even sure about that because I think FMP7 will always make the same choice. One name many related records single Id is OK.

If the same name is used by multiple Clients, then when the Name value list picks the name, the ID that pops in will be the first related Client with that name. This will not produce an error, but your users will have no way to choose the correct Client. This is why Queue suggested making the name unique.

If it's likely that more than one Client will have the same name, then you should look for another solution.

In any case, "many-to-one" is still the type of relationship between Invoice and Client. Many Invoices to each Client.

[SlimJim]

If it's likely that more than one Client will have the same name, then you should look for another solution.

In this situation you would need some way of distinguishing which Client you wanted and choosing a non-unique name from a value list would not be the method I would choose to use and its hard to imagine that anyone would. I do not have clients with the same name but I often have more than one contact at a given organsation - and frequently cannot remember the contact names (Inland Revenue for example has numerous different branches that I need to contact) so I follow the "Outlook model" of having a unique and non-empty FileAs field which gives me the information I need to distinguish which Client or Contact I am trying to use in a given situation.

The point I was trying to make here is that the situation of one name- many IDs it is one you would probably want to avoid.

[-Queue-]

For non-unique names, you could use a technique as demonstrated in the attachment.

NonUniqueNames.zip

[SlimJim]

OK. That is neat. I found the auto-enter calculations interesting. I think it still suffers the disadvantage of exposing an ID number in the VL although it looks much better on the right than on the left.

I haven't seen many examples of the Let function before so I shall be reading that again with interest.

[comment]

it still suffers the disadvantage of exposing an ID number

I see a bigger flaw in not being able to decide which John Smith to select. IMHO, it would be more practical to require a unique Name & Company combination, and base the value list on that.

[-Queue-]

I do not see a way around not exposing the ID when dealing with non-unique descriptions. But at least the auto-enter removes the ID once selected. Note that it is also validated so that you cannot modify the name by circumventing the drop-down list, and get away with it.

The Let is simply used to eliminate repetition, in this case, so that we can say

GetAsNumber® = R

instead of

GetAsNumber(RightWords( ClientName; 1 )) = RightWords( ClientName; 1 )

which makes it a bit clearer also.

I should note, as I have before, that this technique requires that the ID field in Jobs be created BEFORE the name field. If it is not, the auto-enter calculations will not function correctly.

[-Queue-]

True, but I was referring to duplicate client names here, which would not necessarily include a contact name.

[comment]

Whatever. The point is that I, as the user, need some meaningful information in order to select the correct entry. Serial ID has the advantage of always being unique - because it is meaningless. And because it is meaningless, it is not suitable for use in this situation. The proposed solution solves a developer's problem, instead of the user's problem.

[-Queue-]

I think it depends on whether you hide the id from the user when creating or viewing the 'client' list. If you do not, then it can be meaningful to users. I do understand your point, though.

[LaRetta]

We have 68 John Smith's in our Contacts table (Customers). There is simply no way to make them unique. And IDs are worthless irritating clutter to Users; particularly in large tables where IDs can run 6- or 7-characters (but even when not).

I usually allow a Shortcut Code which is also unique. Staff controls it. It provides them quick lookups. They code any 6-characters they wish but it is validated. They like State-LastFourPhone. If that is taken, they will assign State-FirstFourLastName, etc. With clairvoyance, only 4-5 characters is all it takes to find someone. This does NOT replace IDs but only assists Users in identification and isolation. And they can change them to their heart's content, as Customers move from State to State or change phones. If no immediate match, other measures are taken based upon Find Similar. Shortcut Code looks up the ID. Learned from Ray Cologon.

When using names in Value Lists, I always pair the name with Shortcut Code. Or name with City/State or Phone for an identifier. Of course you may have duplicates. And that is, after all, why we use IDs behind the scenes. But I think IDs should remain behind the scenes. Shortcut Code and Name works the best (I believe) because it is unique and meaningful data to the Users.

Conditional Value Lists help here also. Select name John Smith. If more than one, invisible portal appears with pop-up providing the identifier (Street, etc) to fine-tune the selection. If only one, the portal remains hidden and the User is tabbed by it.

So what am I saying? Well you know me ... it could be most anything.

My point is that there is no definitive answer. As with every solution, it should be taylored to the individual business need. And in my book, the User is king. Because easy, fun, friendly design makes happy, productive, accurate employees which require little (or no) staff training - which creates happy (thus wealthy) business owners. And that's our purpose after all ...

LaRetta

[SlimJim]

We're all singing from the same songsheet. If you have numerous same name values then you need some extra information to enable the user to choose the right one. How you do it is your (or preferably the user's) choice.