Convert text to numerical values

[Alan3]

My goal is to analyze survey results. The survey contains more than 100 questions where people respond to statements with: “Absolutely Disagree, Disagree, Neutral, Agree, Absolutely Agree”

I want to convert these text responses to numerical values from 1-5 and store them in Airtable. I’m currently using Make.com to receive survey responses from Tally then several Airtable modules to save the responses to the corresponding tables.

My goal is to set this up in as simple yet maintainable a way possible. I’d like to avoid having 100 unique formulas to convert each single response because what if I change the numerical values later down the line.

I started down the path of adding a formula field next to each response in Airtable but that would be 100 new fields in airtable, one for each formula conversion. Seems kinda bad doing it that way.

I’d like to try and do it with Make.com. Ideally I can convert the text to numbers in the scenario that gets responses from the Tally form but I’m having a hard time figuring out how.

Any ideas?

[samliew]

Welcome to the Make community!

Could you provide an example output from the survey module?

Please provide the output bundles of the modules by running the scenario (or get from the scenario History tab), then click the white speech bubble on the top-right of each module and select “Download input/output bundles”.

A.

Save each bundle contents in your text editor as a bundle.txt file, and upload it here into this discussion thread.

Uploading them here will look like this:

module-1-input-bundle.txt (12.3 KB)
module-1-output-bundle.txt (12.3 KB)

B.

If you are unable to upload files on this forum, alternatively you can paste the formatted bundles in this manner:

• Either add three backticks ``` before and after the code, like this:

  ```
  <sup>input/output bundle content goes here</sup>
  ```

• Or use the format code button in the editor:
  

Providing the input/output bundles will allow others to replicate what is going on in the scenario even if they do not use the external service.

This will allow others to better assist you. Thanks!

samliew – request private consultation

Join the unofficial Make Discord server to chat with other makers!

[Alan3]

Sure, here’s the sample output bundle
output_bundle.txt (1.5 KB)

[samliew]

Input

You can do a toArray on the collection of responses,

Then Iterate the array and use a switch function to convert to a score

Then aggregate the key and score

Output

samliew – request private consultation

Join the unofficial Make Discord server to chat with other makers!

Module Export

You can copy and paste this module export into your scenario. This will paste the modules shown in my screenshots above.

1. Copy the JSON code below by clicking the copy button when you mouseover the top-right of the code block
   

2. Enter your scenario editor. Press ESC to close any dialogs. Press CTRLV (paste keyboard shortcut for Windows) to paste directly in the canvas.

3. Click on each imported module and save it for validation. You may be prompted to remap some variables and connections.

View Module Export Code

JSON

{
    "subflows": [
        {
            "flow": [
                {
                    "id": 47,
                    "module": "json:ParseJSON",
                    "version": 1,
                    "parameters": {
                        "type": ""
                    },
                    "mapper": {
                        "json": "[\n  {\n    \"responseId\": \"0Dgx7P\",\n    \"submissionId\": \"0Dgx7P\",\n    \"respondentId\": \"yxEEA0\",\n    \"formId\": \"mZV6Oz\",\n    \"formName\": \"Ordinal to Numerical\",\n    \"createdAt\": \"2024-06-01T23:13:13.000Z\",\n    \"fields\": {\n      \"What's your take?\": {\n        \"d4bc6f52-6f2f-45ea-b798-e608085f9c80\": [\n          \"Absolutely Agree\"\n        ],\n        \"6f4812a3-76e6-494a-82c7-c3694738c9d4\": [\n          \"Neutral\"\n        ],\n        \"214acfde-2db3-40e1-83c3-78b90ea55b29\": [\n          \"Neutral\"\n        ],\n        \"2c0341cc-84db-46ed-997f-32e769744c04\": [\n          \"Agree\"\n        ],\n        \"6479389b-9035-4026-9ff9-3d13fd3e1fbd\": [\n          \"Absolutely Agree\"\n        ]\n      }\n    },\n    \"eventId\": \"fdf9ac97-47b8-4258-8c79-6dc92208d03c\",\n    \"fieldsById\": {\n      \"question_NqvqlO\": {\n        \"d4bc6f52-6f2f-45ea-b798-e608085f9c80\": [\n          \"Absolutely Agree\"\n        ],\n        \"6f4812a3-76e6-494a-82c7-c3694738c9d4\": [\n          \"Neutral\"\n        ],\n        \"214acfde-2db3-40e1-83c3-78b90ea55b29\": [\n          \"Neutral\"\n        ],\n        \"2c0341cc-84db-46ed-997f-32e769744c04\": [\n          \"Agree\"\n        ],\n        \"6479389b-9035-4026-9ff9-3d13fd3e1fbd\": [\n          \"Absolutely Agree\"\n        ]\n      }\n    }\n  }\n]"
                    },
                    "metadata": {
                        "designer": {
                            "x": -975,
                            "y": -1491
                        },
                        "restore": {
                            "parameters": {
                                "type": {
                                    "label": "Choose a data structure"
                                }
                            }
                        },
                        "parameters": [
                            {
                                "name": "type",
                                "type": "udt",
                                "label": "Data structure"
                            }
                        ],
                        "expect": [
                            {
                                "name": "json",
                                "type": "text",
                                "label": "JSON string",
                                "required": true
                            }
                        ]
                    }
                },
                {
                    "id": 51,
                    "module": "util:SetVariable2",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "name": "responses",
                        "scope": "roundtrip",
                        "value": "{{toArray(47.fieldsById.question_NqvqlO)}}"
                    },
                    "metadata": {
                        "designer": {
                            "x": -731,
                            "y": -1492
                        },
                        "restore": {
                            "expect": {
                                "scope": {
                                    "label": "One cycle"
                                }
                            }
                        },
                        "expect": [
                            {
                                "name": "name",
                                "type": "text",
                                "label": "Variable name",
                                "required": true
                            },
                            {
                                "name": "scope",
                                "type": "select",
                                "label": "Variable lifetime",
                                "required": true,
                                "validate": {
                                    "enum": [
                                        "roundtrip",
                                        "execution"
                                    ]
                                }
                            },
                            {
                                "name": "value",
                                "type": "any",
                                "label": "Variable value"
                            }
                        ],
                        "interface": [
                            {
                                "name": "responses",
                                "label": "responses",
                                "type": "any"
                            }
                        ]
                    }
                },
                {
                    "id": 48,
                    "module": "builtin:BasicFeeder",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "array": "{{51.responses}}"
                    },
                    "metadata": {
                        "designer": {
                            "x": -490,
                            "y": -1489
                        },
                        "restore": {
                            "expect": {
                                "array": {
                                    "mode": "edit"
                                }
                            }
                        },
                        "expect": [
                            {
                                "name": "array",
                                "type": "array",
                                "label": "Array",
                                "mode": "edit",
                                "spec": []
                            }
                        ]
                    }
                },
                {
                    "id": 49,
                    "module": "util:SetVariable2",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "name": "score",
                        "scope": "roundtrip",
                        "value": "{{switch(48.value[]; \"Absolutely Disagree\"; 1; \"Disagree\"; 2; \"Neutral\"; 3; \"Agree\"; 4; \"Absolutely Agree\"; 5)}}"
                    },
                    "metadata": {
                        "designer": {
                            "x": -245,
                            "y": -1490,
                            "messages": [
                                {
                                    "category": "reference",
                                    "severity": "warning",
                                    "message": "Referenced module 'Flow Control - Iterator' [48] is not accessible."
                                }
                            ]
                        },
                        "restore": {
                            "expect": {
                                "scope": {
                                    "label": "One cycle"
                                }
                            }
                        },
                        "expect": [
                            {
                                "name": "name",
                                "type": "text",
                                "label": "Variable name",
                                "required": true
                            },
                            {
                                "name": "scope",
                                "type": "select",
                                "label": "Variable lifetime",
                                "required": true,
                                "validate": {
                                    "enum": [
                                        "roundtrip",
                                        "execution"
                                    ]
                                }
                            },
                            {
                                "name": "value",
                                "type": "any",
                                "label": "Variable value"
                            }
                        ],
                        "interface": [
                            {
                                "name": "score",
                                "label": "score",
                                "type": "any"
                            }
                        ]
                    }
                },
                {
                    "id": 50,
                    "module": "builtin:BasicAggregator",
                    "version": 1,
                    "parameters": {
                        "feeder": 48
                    },
                    "mapper": {
                        "score": "{{49.score}}",
                        "key": "{{48.key}}"
                    },
                    "metadata": {
                        "designer": {
                            "x": -4,
                            "y": -1492,
                            "messages": [
                                {
                                    "category": "last",
                                    "severity": "warning",
                                    "message": "A transformer should not be the last module in the route."
                                }
                            ]
                        },
                        "restore": {
                            "extra": {
                                "feeder": {
                                    "label": "Iterator [48]"
                                },
                                "target": {
                                    "label": "Custom"
                                }
                            }
                        }
                    }
                }
            ]
        }
    ],
    "metadata": {
        "version": 1
    }
}