Dropdown not sorting properly

[Pumpedplop]

I have a dropdown sorting issue, the issue is that I have to use a text datatype because the numbers sometimes contain a hyphen. This makes it not sort properly from smallest number to larger (everything that is behind the hyphen does not matter). Anyone know a solution?

[Pumpedplop]

I contacted caspio support and they were able to create the code below for my datapage.

This code should be installed in the footer. Hopefully this helps someone else with the same problem.

<script>
if (typeof getValuesArr=='undefined') {

const INPUT_SELECTOR = '[id^="Value3_1"]'

const getValuesArr = HTMLSelect => {
    let valuesArr = []
    HTMLSelect.querySelectorAll('option').forEach(option=>{
        valuesArr.push(option.value)
    })
    return valuesArr
}

const sortValuesArr = numbersAsStr => {

numbersAsStr.sort((a, b) => {
    if (parseInt(a) > parseInt(b))
        return 1
    if (parseInt(a) < parseInt(b))
        return -1
    return 0
})
return numbersAsStr

}

const generateOptionsTemplate = (sortedValuesArr) => {
let optionsTemplateStr = ''
for (let i=0; i<sortedValuesArr.length;i++) {
optionsTemplateStr += `<option custom-sorting="sorted" value="${sortedValuesArr[i]}">${sortedValuesArr[i]}</option>`
}
return optionsTemplateStr
}

const addObserver = (HTMLEl) => {

new MutationObserver( (mutations) => {
console.log('mutation fired')
    for (let i=0; i<mutations.length; i++) {
  
      if(mutations[i].target != document.querySelector(INPUT_SELECTOR)) continue
       if(mutations[i].addedNodes.length == 0) continue
      
       if (mutations[i].addedNodes[0].getAttribute('custom-sorting') == 'sorted') { 
        break}       


      if (mutations[i].addedNodes[0].tagName == 'OPTION') {

        window.setTimeout(()=>{
        document.querySelector(INPUT_SELECTOR).innerHTML=generateOptionsTemplate(sortValuesArr(getValuesArr(document.querySelector(INPUT_SELECTOR))))
        }, 250)
        
        break
       }

  
     
    }
    
}).observe(document.querySelector('body'), {childList: true, subtree: true})
}
//document.querySelector(INPUT_SELECTOR).parentElement.parentElement
const mainFun = () => {
const input = document.querySelector(INPUT_SELECTOR)
input.innerHTML = generateOptionsTemplate(sortValuesArr(getValuesArr(input)))

addObserver(input)


document.removeEventListener('DataPageReady', mainFun )
}
document.addEventListener('DataPageReady', mainFun )

}
</script>

 

[autonumber]

Hi @Pumpedplop - I believe there's no standard way to sort the values as it contains a hyphen and it is a text field. You'll need to manually sort it onto the table. However, this can be achieved using custom coding.

These links might help you - https://stackoverflow.com/questions/61460640/custom-sort-of-dropdown-options-using-jquery 

https://howto.caspio.com/datapages/ajax-loading/

[autonumber]

Hi  - you may also try pasting this code on the footer to sort your dropdown values:

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>

<script>

document.addEventListener('DataPageReady', function (event) {

function natcmp(a, b) {
    var ra = a.innerText.match(/\D+|\d+/g);
    var rb = b.innerText.match(/\D+|\d+/g);
    var r = 0;

    while(!r && ra.length && rb.length) {
        var x = ra.shift(), y = rb.shift(),
            nx = parseInt(x), ny = parseInt(y);
        if(isNaN(nx) && isNaN(ny))
           { r = x > y ? 1 : -1}
        else if(isNaN(nx) || isNaN(ny))
           { r = x < y ? 1 : (x > y ? -1 : 0); }
        else
            r = nx - ny;
    }
    return r || ra.length - rb.length;
}


$("#Value2_1").html(
Array.prototype.slice.call($("#Value2_1")[0].querySelectorAll("option"), 0).sort(natcmp)
);
});

</script>

You just need to replace the "#Value2_1" based on the ID of your dropdown. 

Hope this helps!

[Jarmizee]

Im waiting for Caspio to help us with the code on this, but Im trying your @Pumpedplop method and it not working for some reason. I have an initial problem where the numbers are sorting 1, 10, 11, 13 then 2, 3 4. Im seeing that the "Value3_1" needs to be changed. I may have this one wrong. How do you determine that this is the ID of the input selector. Im putting the HCRoomNumber which is the field in the table of this form. I think this is what I'm missing. Please advise. Thanks for the help!

[Tubby]

5 hours ago, Jarmizee said:

Im waiting for Caspio to help us with the code on this, but Im trying your @Pumpedplop method and it not working for some reason. I have an initial problem where the numbers are sorting 1, 10, 11, 13 then 2, 3 4. Im seeing that the "Value3_1" needs to be changed. I may have this one wrong. How do you determine that this is the ID of the input selector. Im putting the HCRoomNumber which is the field in the table of this form. I think this is what I'm missing. Please advise. Thanks for the help!

You probably need to inspect the element by right clicking on the dropdown and hitting inspect on the pop up that will show up. Then you should see the developer console pop up on your browser and you should find and ID field in the hightlighted text. Like this

[Jarmizee]

Thank you for your reply @Tubby I'm working in the inline editing with this issue. The ID seems to be dynamic, because it always changes vs the submission form seems to have a static ID. Please see the attached image I put a few screenshots of how the ID info changes on every load or refresh. Thank you again for any assistance.