fmp17 Alpha Numeric Sort of Random Letters and Numbers

[poormh]

I use FMPro infrequently--and would be most appropriately described as a novice.  I've received help with the script below--but my portal isn't sorting by alphanumeric order--in the order I want it to. 

I have a text field in my portal called __pkPosnCtrl.  There is a max string of 20 characters in this field but location of letters or numbers within the field vary.  The script allows my records to sort by toggling back and forth between ascending and descending order. There is an optional script parameter of "__pkPosnCtrl" which appears on a button over the portal label.  Below is the script (I've added comments to help me understand and remember what the script is doing.)

Set Variable [$SP; Value: Get ( ScriptParameter)]

If [IsEmpty ($SP)]

Exit Script []

End If

# make sure a record is active

If [Get ( FoundCount ) = 0]

Exit Script []

End If

Set Variable [$SortField; Value: Get ( ScriptParameter ) ]

If [t01b_departments_POSITIONS::SortField = $SortField]

# If the $SortOrder is not equal to 1 go ahead and set it to 1, otherwise set it to 0

# Allows the enduser to toggle back and forth between ascending and descending order

Set Field [_t01b_departments_POSITIONS::SortField = $SortField]

Else

# Since the $SortField is not equal to the column you just clicked go ahead and set the sort field

# equal to $SortField (of that colum)

Set Field [_t01b_departments_POSITIONS::SortField; $SortField]

 

# equal to $SortField (of that column)

End If

Commit Records/Request[]

Refresh Window [Flush cached join results]

 

Additionally, I'm sorting the relationship between my parent table T01_DEPARTMENTS and my child table _t01b_departments_POSITIONS based on 4

calculations:

SortValueDynamicAscNumber, Unstored, from tblPOSITIONS, = Case (SortOrder=0; GetField (SortField))

SortValueDynamicAscText, Unstored, from tblPOSITIONS, = Case (SortOrder=0; GetField  (SortField))

SortValueDynamicDescNumber, Unstored from tblPOSITIONS, = Case (SortOrder=1; GetField (SortField))

SortValueDynamicDescText, Unstored, from tblPOSITIONS, = Case (SortOrder=1; GetField (SortField))

When I perform an ascending sort (descending is also an issue) the result I'm getting is: 

DevGrantWritPt48s1

DevMktgCoordFt66s1

DevCorRelEvMgrFt80s1

DevPreCirPGMgrFt80s1

DevVpAdvanceFt80s1

DevGiftProDbaFt80s2

DevAnnFunRCIIFt80s32 But what I want is:   DevAnnFunRCIIFt80s32 DevCorRelEvMgrFt80s1 DevGiftProDbaFt80s2 DevGrantWritPt48s1 DevMktgCoordFt66s1 DevPreCirPGMgrFt80s1 DevVpAdvanceFt80s1   Any ideas on how I can get the sort order I'm looking for?

[comment]

This is very difficult to follow.

In general, if you sort by a text field, then the sort is done alphabetically.  That would result in the order you show as the wanted order. If you're getting a different result, then you must be sorting by another field first. I get the impression that the first field in your relationship's sort order is a calculation field that converts the same values to numbers. That would explain the actual order you see - because the converted numbers are:

 481
 661
 801
 801
 801
 802
8032

So here only the three records with a common value of 801 would be sorted by the second field in the sort order.

I am not sure what exactly you're trying to accomplish here, and I have doubts if your basic approach is the correct one. Dynamic sort of portals is difficult to achieve. I suggest you explain what exactly your requirements are, so we can see if a better method can be found.

Edited November 10, 2018 by comment

[poormh]

I see your point.  I was making it way more complicated then it needs to be.  All set now.  I appreciate your response.  Thank you.