Stack Exchange
Selecting correct match from list
I have an SQL Query which takes an input from a cell and queries the database to find matches.
For Example
SELECT Postcode
FROM Table
WHERE SUBSTRING(Postcode, 1, 2) = SUBSTRING('{{CollectionPostcode.text}}', 1, 2)
ORDER BY Postcode ASC;

If I use the Postcode LA4 4SN
The JSON output would be
[
  {
    "Postcode": "LA1-10"
  },
  {
    "Postcode": "LA11-23"
  }
]

I have a script which calls the query and logs the output as query.data
export default {
  async slicePostcode() {
    const enteredPostcode = CollectionPostcode.text;
    const postcodeArea = enteredPostcode.trim().split(" ")[0]; // Extract the first part of the postcode
    console.log(postcodeArea);

    await Query.run(); // Wait for the query to finish execution

    const dataString = Query.data; 

    // Parse the JSON string into an array of objects
    let data = [];
    try {
      data = JSON.parse(dataString);
    } catch (error) {
      console.error("Error parsing JSON:", error);
      return null; // or return an appropriate value to indicate an error
    }

    // Find the matching entry for the given postcode area
    const matchingEntry = data.find((entry) => {
    const [startPostcode, endPostcode] = entry.Postcode.split("-");
    return postcodeArea >= startPostcode && postcodeArea <= endPostcode;
  });

  return matchingEntry || null; // Return matchingEntry or null if no match is found
 },
};

The JAvascript should then select the relevent option from Query.data which in this case would be LA1-10
However I keep getting null
I have tried adapting the JS multiple times but keep getting the same result.
Can anyone help?

Are you specifically indicating that, in your debugging, matchingEntry is always null?  If so then it sounds like that .find() operation is where you should focus.  Can you replicate that operation and result here with known hard-coded data?

Which dbms are you using?

I think the issue has to do with the line:
return postcodeArea >= startPostcode && postcodeArea <= endPostcode;

The values are being compared as strings and according to string comparisons LA4 is not greater than LA1.
To get the desired behavior:
export default {
  async slicePostcode() {
    const enteredPostcode = CollectionPostcode.text;
    const postcodeArea = enteredPostcode.trim().split(" ")[0]; // Extract the first part of the postcode
    const postcodeAreaNumber = Number(postcodeArea.slice(2)); // Parse the number from postcodeArea
    console.log(postcodeArea);

    await KeedwellPostcodeQuery.run(); // Wait for the query to finish execution

    const dataString = Query.data;

    // Parse the JSON string into an array of objects
    let data = [];
    try {
      data = JSON.parse(dataString);
    } catch (error) {
      console.error("Error parsing JSON:", error);
      return null; // or return an appropriate value to indicate an error
    }

    // Find the matching entry for the given postcode area
    const matchingEntry = data.find((entry) => {
      const [startPostcode, endPostcode] = entry.Postcode.split("-");
      const startNumber = Number(startPostcode.slice(2));
      const endNumber = Number(endPostcode.slice(2));
      return postcodeAreaNumber >= startNumber && postcodeAreaNumber <= endNumber;
    });

    return matchingEntry || null; // Return matchingEntry or null if no match is found
  },
};


Sorry I left out some of the relevant code, thought I only had to edit that last portion.
Should work. Hope this helps!

I tried the above edit but postcodeAreaNumber is not defined.
I then changed to postcodeArea but get the error TypeError: Cannot read properties of undefined (reading 'Postcode')

ignore the above comment, I made a slight mistake
Still not working though
I tried the above edit but postcodeAreaNumber is not defined.
I then changed to postcodeArea
It still returns null

Managed to figure it out.
I changed the JS to
export default {
    async slicePostcode() {
        const enteredPostcode = CollectionPostcode.text;
        const postcodeArea = enteredPostcode.trim().split(" ")[0];

        await Query.run();
        const data = Query.data;

        // Find the matching entry for the given postcode area
        const matchingEntry = data.find((entry) => {
        const [startPostcode, endPostcode] = entry.Postcode.split("-");
        const startNumber = Number(startPostcode.slice(2));
        const endNumber = Number(endPostcode.slice(0));
        const numericPostcodeArea = Number(postcodeArea.slice(2));

        const isMatch = numericPostcodeArea >= startNumber && numericPostcodeArea <= endNumber;
        console.log(`Comparing ${postcodeArea} with ${startPostcode} - ${endPostcode}: ${isMatch}`);
        return isMatch;
      });

     // Extract and return the Postcode value from the matching entry
     const matchingPostcode = matchingEntry ? matchingEntry.Postcode : null;
     return matchingPostcode || null; // Return matchingPostcode or null if no match is found
    },
};

