Missing Items In Value List

[MichaelW]

Hi all. I am relatively new to Filemaker programming, but I have come across what I thought was a bug, which I have been tearing my hair out trying to squash, only to find it is more a "feature" than a bug. I have a field set as the key for lookups in a ms sql database which I have created a relationship with. I have it set as a drop down, and it is showing 2 fields (last name and first name). Unfortunately, it only shows 1 person per last name in the sorted list (example, there are 5 people with the last name "Bennett" but only 1 shows).  After driving myself nuts trying to find the error, I found the following in the filemaker troubleshooting section:

 

:

 

If the value list is defined to display information from two fields, items will not be duplicated for the field on which the value list is sorted. For example, if the value list displays information from the Company field and the Name field, and if the values are sorted by the Company field, only one person from each company will appear in the value list.

 

As I read it, I can't do what I need to do with a value list (display EVERY last name from the sql file) so what other options do I have? I have experimented with creating a portal which DOES show a list of ALL the last names and first names, but I don't know/understand enough to know what logic/functionality I need so if I click one of the people in the portal list it will do the same thing as if I clicked it in a dropdown value list, which is to then do the lookups and populate the rest of the fields in this database from the information in the record in the sql database. Any and all help would be greatly appreciated, and I appreciate any help any of you can offer. Thank you!

[carlosnorvik]

Maybe you can try a calculated field mixing the last name with first name, and make the Value  list from there.

Create a formula like:

Fullname = LastName&", "&FirstName

Which should display a list as follows:

Allen, Mary

Davis, Mark

Smith, John

 

Hope it helps

 

Carlos

[MichaelW]

Carlos-

I tried doing that already. It said I cannot do this since the calculated field I created (which was last name & " " & first name from the sql table) cannot be used as the value list since it cannot be indexed. –

[carlosnorvik]

You can create a text field based on a calculation.

Manage Database/Fields/Options/Auto Enter/Calculated Vale/Specify Formula/Uncheck Do not replace existing value of field (if any).

 

You will note that this won't modify existing fields, until you modify them, if they are few, that is no problem, but if you have a lot, it can be time consuming.

 

One easy solution, create a filed you can call "modifier" and include it in your formula (LastName&", "&FirstName&Modifier)

Insert any value you want in "modifier" stay there and do Records/Replace Field Contents.

Fullname will be populated, but you don't want that value from modifier.

Delete the "modifier" value in any record and do once again Records/Replace Field Contents, the modifier value will vanish and you will have your value list.

Now delete the "&modifier" from your formula. Any new records will show the Fullname.

 

Hope it helps this time.

 

Regards

Carlos

[MichaelW]

I tried a number of ideas offered by people, but unfortunately filemaker refused just about every one because it isn't a local table but one in a MSSQL database. I ended up just triggering an a script to import to a local table from the sql database with import/append/add. It only takes 2 seconds so I kick up a dialogue box and it seems this will at least work for now. Unfortunately, all of the suggestions I got (calculated text fields, virtual value list etc) worked great if the table/database was local but failed if it were a remote sql database.

[carlosnorvik]

I hope someday FM will also offer the possibility of working as a native SQL front end.

[Rick Whitelaw]

Then it would cease to be FileMaker, no? There are plenty of front ends for SQL.

[carlosnorvik]

None as good as FM, and the possibilities would be great, being able to run FM frot end in your office and some web forms at the same time.

 

Many years ago I did some tests with Lotus Approach, it was really cool and easy, I made a small front end to a MySql base a fried had, and it was incredibly easy to manipulate.You needed a MySql connector (can't recall which one) but the connection was very easy to make, I was even more novice than I am now with databases and it worked on the first test.

 

From my point of view, it would increase FM marketing possibilities and market share.

 

Just a tought!