Extracting multiple values in a column to send via JSON

Ask for Help
1

GLIDE1
GLIDE1560×673 49 KB

Glide2
Glide21181×132 9.47 KB

Hi All,

I have a drop down list of calculations that all have unique code. When the user selects them I get the selections in “A Calculations” column. The user can select multiple calculations and the “A Calculations” column can have multiple values.

I want to send the multiple values as a JSON via a webhook to integrate with MS Power Automate. I have it working with a single column value. But I don’t know how to separate multiple user selections and send these values? Any advice?

Note - the user could select 20+ different values and I need to send each value via JSON.

See images to explain.

Thanks all,

Ricch

2

In what exact format do you want it? Is it something like this?

[
    {
        "choice": "choice1"
    },
    {
        "choice": "choice2"
    }
]
3

Hi ThinhDinh,

I have managed to use the AI JSON tool where I have a choice listed as “CALCNo” and the value as a string “C-No” but I am not sure the

{“CALC1”:“C-0006”,“CALC2”:“C-0007”,“CALC3”:“C-0008”}

However, I still would like to know how to extract individual values in a multiple value column as I need to “lookup” each value in another table.

Thanks,

Rich

4

GLIDE5
GLIDE51067×321 40.3 KB

5

Let’s circle back to your MS Power Automate scenario thing. What exact format does that require you to have in your JSON?

6

Hi ThinhDinh,

As the Power Automate is under construction I can make it work with any JSON format.

The JSON format just needs to clearly show the name of the file selected and I want to look up each multiple value in my “A Calculations Column”, and return its file format saved in another table. This will then need to be added to the JSON. For example:

[
    {
        "choice 1": "c-0004"
        "choice 1 format": ".xls"

    },
    {
        "choice 2": "c-0007"
        "choice 2 format": ".docx"
    }
]

Etc. But the “choice 1 format” is not input by the user. But is returned from my other table.

If each of the user selected values were in their own column, I think I could get it to work. But as all selections are stored in one column with multiple values I cannot extract and look up individual values.

Thanks,

Rich

7

Here’s my setup.

1/Add a JSON Object column in the table where you store the file types.

image
image1318×390 57.4 KB

2/Go to the table where you store the users’ choices and set up these columns.

image

image

(J is replaced by the output of the first column)

image

(You already have this column).

image
image668×922 77.7 KB

function formatChoices(choicesString, lookupJsonString) {
  // Parse inputs
  const choices = choicesString.split(',');
  const lookupData = JSON.parse(lookupJsonString);

  // Create a map for quick lookup
  const lookupMap = new Map(lookupData.map(item => [item.Name.toLowerCase(), item['File Type']]));

  // Process choices and create output
  const output = choices.map((choice, index) => {
    const lowerChoice = choice.trim().toLowerCase();
    const fileType = lookupMap.get(lowerChoice) || 'Unknown';
    return {
      [`choice ${index + 1}`]: lowerChoice,
      [`choice ${index + 1} format`]: fileType
    };
  });

  // Convert output to string
  return JSON.stringify(output, null, 4);
}

return formatChoices(p1,p2)

image

2 Likes