Question 210677 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – 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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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
    },
};

  2. 0

    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!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search