Script for text field to populate miles based on two drop down selections

[Debra35899980vtpv]

I have a drop down field named "Dropdown 1.0" for the beginning location and a second drop down field

named "Dropdown 1.2" for the ending location. What script would I use in the text field named "MilesRow1" to calculate the miles between the two locations Dropdown 1.0 and Dropdown 1.2.  

There will be the same 20 sites to choose from in all the drop downs. I've attached the mileage sheet that shows the mileage between each of the 20 sites. Would I need to create a script like below for each MilesRow field or is there a better way. Is the below script even correct? 

I don't have any JavaScript knowldege so as much detail of the script would be very helpful.

var MilesRow1 = this.getField("Dropdown1.0").valueAsString;
if (Dropdown1.0=="MERC") and (Dropdown1.2=="Special Services (SSC)") event.value = "1.4";
else if (Dropwdown1.0=="MERC") and (Dropdown1.2=="Valley (VE) 110") event.value = "5.5";
else if 

I previously did one drop down field that combined the beginning and ending location but it created too many options in one drop down even though it was easier to create an Extract Value and custom calculation script.

Thank you for your valuable time and help

[try67]

Plenty of errors there... Try this code:

var MilesRow1 = this.getField("Dropdown1.0").valueAsString;
var MilesRow2 = this.getField("Dropdown1.2").valueAsString;
if (MilesRow1=="MERC" && MilesRow2=="Special Services (SSC)") event.value = "1.4";
else if (MilesRow1=="MERC" && MilesRow2=="Valley (VE) 110") event.value = "5.5";
// etc.
else event.value = "";

[Debra35899980vtpv]

Hi,

Can you help me understand your exmple script? Wouldn't MilesRow1 need to look at both Dropdown1.0 for beginning location and Dropdown2.0 for end location to populate the 1.4 miles? MilesRow2 would look at Dropdown3.0 and Dropdown4.0 because that's the next row for entering beginning and ending locations (I attached the actual form below)?

Also, even though one dropdown is for the beginning location and the other dropdown is for the ending location MERC and Mustang Elem equals 0.4 miles regardless of which location was the start or end. Does that make sense?

My mind keeps getting stuck on what I am more familiar with which is formulas like this: MERC + Mustang Elem = 1.4 and/or Mustang Elem + MERC = 1.4 as a database that the MilesRow fields reference when checking the selection from the dropdown. Sorry I might be over complicating things. 

[try67]

The file you attached doesn't contain any form fields. It just seems to be a table of values, so I'm not sure how you want me to use it.

[PDF Automation Station · Accepted solution]

You can do it with a script like you're attempting.  I would change the dropdown names to "Start" and "End" for simplicity and clarity and then run this script in the result field as a custom calculation:

event.value="";
var start = this.getField("Start").value;
var end = this.getField("End").value;
if(start==end){event.value=0}

if(start=="MERC Building)
{
if(end=="Mustang Elem 105"){event.value=.4}
if(end=="Valley 110"){event.value=5.5}
if(end=="Lakhoma 115"){event.value=2.8}
//continue with the rest of the list

}
//next starting location

if(start=="Mustang Elem")
{
//skip MERC Bulding
if(end=="Valley 110"){event.value=5.4}
if(end=="Lakhoma 115"){event.value=2.2}
//continue with the rest of the list

}
//next starting location, etc.

Once you complete the first block to establish the pattern you can copy and paste and then change the miles.  BTW, "and" in your script should be &&, and to call the value of the fields you have use this.getField (for example) this.getField("Dropdown1.0").value. 

[Abuse removed by moderator]

[PDF Automation Station]

Ignore //skip MERC Building

[Debra35899980vtpv]

This is exactly what I was looking for and it works perfect. Thank you so much everyone.

[Nesa Nurani]

Let's say you have fields named "From" and "To" and "Miles", as custom calculation script of "Miles" you can use this:

var milesTable = {
    "MERC Building": {
        "Mustang Elem 105": 0.4,
        "Valley 110": 5.5,
        "Lakehoma 115": 2.8,
        "Trails 120": 5.4,
        "Creek 125": 5.3,
        "MEC 130": 2.8,
        "Centennial 135": 2.2,
        "Horizon 140": 0.8,
        "Canyon Ridge 145": 4.1,
        "Prairie View 150": 4.4,
        "Riverwood 155": 3.2,
        "Meadow Brook 160": 6.0,
        "MMS 505": 0.6,
        "MNMS 510": 5.1,
        "MCMS 525": 3.4,
        "MHS 705": 0.2,
        "Special Svcs. Bldg.": 1.4,
        "Bronco Academy": 1.7,
        "Mustang Treatment Ctr.": 2.2
    },
    "Mustang Elem 105": {
        "MERC Building": 0.4,
        "Valley 110": 5.4,
        "Lakehoma 115": 2.6,
        "Trails 120": 5.2,
        "Creek 125": 5.1,
        "MEC 130": 2.6,
        "Centennial 135": 1.7,
        "Horizon 140": 0.9,
        "Canyon Ridge 145": 4.0,
        "Prairie View 150": 4.1,
        "Riverwood 155": 3.1,
        "Meadow Brook 160": 5.8,
        "MMS 505": 1.0,
        "MNMS 510": 5.0,
        "MCMS 525": 3.2,
        "MHS 705": 0.6,
        "Special Svcs. Bldg.": 1.2,
        "Bronco Academy": 1.5,
        "Mustang Treatment Ctr.": 1.7
    },
    "Valley": {
        "MERC Building": 5.5,
        "Mustang Elem 105": 5.4,
        "Lakehoma 115": 7.3,
        "Trails 120": 3.9,
        "Creek 125": 2.4,
        "MEC 130": 6.8,
        "Centennial 135": 6.4,
        "Horizon 140": 6.0,
        "Canyon Ridge 145": 1.5,
        "Prairie View 150": 2.9,
        "Riverwood 155": 3.7,
        "Meadow Brook 160": 4.4,
        "MMS 505": 6.1,
        "MNMS 510": 2.5,
        "MCMS 525": 3.9,
        "MHS 705": 5.7,
        "Special Svcs. Bldg.": 5.9,
        "Bronco Academy": 6.2,
        "Mustang Treatment Ctr.": 3.6
    },


};

(function() {
    var fromLocation = this.getField("From").valueAsString;
    var toLocation = this.getField("To").valueAsString;

    if (milesTable[fromLocation] && milesTable[fromLocation][toLocation] != null) {
        event.value = milesTable[fromLocation][toLocation];
    } else {
        event.value = "";
    }
})();

Just keep adding data for other miles in the script I added for ("MERC Building", "Mustang Elem 105", "Valley").

Here is an example file for you to test:  https://drive.google.com/file/d/18MHlyf2Yujek_g2OMdO2UtskXGwAE1RD/view?usp=sharing 

To make it easier to input data, export your file to excel, and then you can use online tools to format your data.