Database help

[kiki1986]

I'm trying to make something like database.

I use code in document level script
var fruits = {Apple:{ Color: "Green",
Size: "Medium",
Weight: "50" },};
function SetFieldValues(fruits)
I set fruit function in dropdown field where I select fruits ( in this case apple).
Now I want to add text field with numbers to use same function, how to set my code so it depends on two fields(dropdown = fruits and text field = numbers)
something like this:

var fruits = {Apple, 1:{ Color: "Green",
Size: "Medium",
Weight: "50" },
2:{ Color: "Red",
Size: "Large",
Weight: "80" },};

How do i make it work?

[Thom Parker]

So you want to select a data set based on the values of two dropdowns?  First, the data needs to be organized so that it can be selected using multiple criteria. The syntax in your example is not legal. There are several ways to do this. But an easy one is to move the numer to be a next level object selection parameter. This technique requires the fruit name to be selected first.

var fruits = {Apple:{ 1:{Color: "Green",Size: "Medium",Weight: "50" },
                      2:{ Color: "Red",Size: "Large",Weight: "80" }
              };

The next piece is to rewrite the select function to use both parameters, but it can't be used the same way that is described in the article. since it depends on two parameters, the simplest solution is to use a calculation event, i.e, the calculation script on one of the text fields that is receiving data.

function SetFieldValues(){
    var oFruitObj = fruits[this.getField("FruitSelect").value];
    if(oFruitObj)
    {
          var oFruitType = oFruitObj[this.getField.getFile("TypeSelect").value;
           if(oFruitType)
           {   .... Set Field Values ...}
    }
} 

This fuction is designed to work in a calculation event, don't try to use it in a validation or keystroke script. 

[kiki1986]

Hi Thom, and thank you for your help.I tried to recreate what you said but I'm lost, can you please check this sample file and let me know what i did wrong

https://drive.google.com/uc?export=download&id=1rFmGJxasVNIgGdyeOiykkpbyOUmlIwv1 

[Thom Parker]

There are a several issues.

First, the function definition aught to be in a document script. I designed the original script you copied this from to work on a form with multiple dropdowns, all calling the same script. So the idea is to define the function once, then use is in multiple locations. Please move it to the same document level script where the fruits object is defined. 

Now call this function from the calculation script. 

Next, the function contains some nonsense that needs to be cleaned up. 

I put "... Set Field Values..." as a placeholder for the code that actually sets the field values. Also, you can't have two functions with the same name. So, move all the code in the "SetFieldValues" you already have in the document level script into this placeholder. Then delete that function. Also, since the function is called in the calculation script for the Color field, you need to change the first line to

event.value = fruits[cFruits].Color;

After you do this, and test the sample, it's likely it will not work and you'll need to do some debug. Before you post agian. Look in the console window and see what error is being reported. Then post this error message along with a description of  exactly what is happening. 

Good Luck!!

[kiki1986]

I battled this whole day but just can't make it to work, this is way over my level of coding, Il give up on it and try something else.

Thank you Thom for your time.

[Thom Parker · Accepted solution]

It's good to spend time on something difficult, you're actually leaning a lot even though it's very frustrating.  But you need a success to more forward.  Here is the simplest script that will work on your test form. Put it in the "Calculation Script" for the Color field. Remove all other scripts except for the "fruits" variable definition in the "fruits" document script. Part of you problem is having an unnecessary script in the keystroke event. Always test scripts in a clean environment. So delete everything except for exactly what is needed. 

var oFruitObj = fruits[this.getField("Dropdown1").value];
if(oFruitObj)
{
	var oFruitType = oFruitObj[this.getField("Text1").value];
	if(oFruitType)
	{  
		  event.value = oFruitType.Color;
		  this.getField("Size").value = oFruitType.Size;
		  this.getField("Weight").value = oFruitType.Weight;
	}
	else
		this.resetForm(["Color","Size","Weight"]);
}
else
	this.resetForm(["Color","Size","Weight"]);

[kiki1986]

Thank you Thom, I really appreciate your help.