Relationship Sort Order

[Keith Shelton]

Mac FM4

1 Quotes File with Repeating Fields including Product Code and Product Details.

Product Code has Pop Up List to show Product Code and Product Details which of course come from Charges File.

Quotes and Charges Files related via Product Code and set to sort in Product Code order.

Problem: The Pop Up List always sorts in Product Details order instead of Product Code order. If the Value List is set to display Product Code only, the sort order is correct.

What am I doing wrong?

Thanks for any help.

[Pupiweb]

Hi Keith

Nothing wrong, FM4 doesn't have the possibility of sorting on the "second" field. The feature was added with FM 5

[Keith Shelton]

Thanks for quick reply.

I may be misunderstanding you but I want it to sort on the first field which it does until I select a second field. It then sorts on the second field.

Also tried making Code second choice but it still sorted based on Details and not Code.

Keith

[Kurt Knippel]

You are correct it does seem to be doing that. I would suggest the following: either upgrade to Filemaker 6 (you missed 3 major releases of Filemaker including the ability to sort on either the first or the second field specifically) or use a calculated field with combines the values of your two other fields and then use only that single field in the value list.

[Keith Shelton]

Thank you, that works.

However, don't know if I have set up correctly but relationship is now based on new calc field and therefore Quotes Code Field fills with both Code and Details which is a little messy.

Is there anyway I can make it more elegant? It would be nice to be able to use pop up list or type code in manually.

Thanks again.

[CobaltSky]

If you edit the formula for your calc field to 'delimit' the values from the two fields with a carriage return character - ie:

Field1 & "

[Keith Shelton]

Ray

I tried that but pop up list then has Codes and Details generally separated in alpha list, e.g. Code starting C is detached from its Details starting with W.

Keith

[Pupiweb]

Hi Keith

If you want to make it effective and elegant you might use a sorted (eventually filtered) portal popping up in a Dialog.fp3 file ...

Details:

Create a dialog-like layout in a Dialog.fp3 file

Send the user to this layout from your file, putting him/her in an endless loop (Abort Off/loop/pause/loop) ... btw DO REMEMBER to format the buttons in this layout to Halt current script else you won't get out of the loop ...

Create some global fileds in this file and use them as the "left side" of a relationship to the Products file ... sort the relationship on the Product name and put a portal in the layout

Final result:

the user clicks on the field to choose a product

the dialog.fp3 file comes into play acting as a pop-up window floating over the original file, letting the user make his/her choice from a properly sorted portal

Additional features:

IF the products list is long (>100 items) you might/should consider to filter the portal: to do so relate a global field in the dialog.fp3 file to a calc field in the products file returning the first letters of the product (or its category or some other userful-for-filtering parameter) ... the user will write the first few letters of the product and the portal will only show matching products, thus shortening the list and allowing an easy choice

[CobaltSky]

A few other other suggestions, then:

1. Enter the combined string from the pop-up, but create two calc fields (to sit over the top on your layouts) which parse the entry field into the two separate elements (you might want to put a character other than a CR inot your calc which produces the value list, so that you've got something easy to parse on...)

2. Enter only the code from the value list, and pull up the details in an adjacent field using a lookup or a relationship.

3. A combination approach, where you parse out the code from a joint valuelist pop-up, and then lookup the details.

Hope these thoughts may prove useful...