Auto Fill Fields on New Record

[Andy M.]

(my apologoes for the noob question...)

I have a database of related tables. For simplicity of this example I'll look at two of the tables:

1 - Customers

2 - Markets

I have a layout with customer data that has a portal to the list of markets.

I also have a "Add New Market" button that runs a script that:

- goes to layout AddNewMarket

- adds a New Record.

I would like it if the script would also automatically fill in the Customer field in the new record (with the customer that we are currently viewing).

I have tried the various script functions (ad hoc) with no success. Is there a way to do this ?

Thanks in advance.

[John Mark Osborne]

Pass a script parameter from the button to the script so you can place the customer id on the New Markets record. When you define a button to Perform Script, you'll see the Optional Script Parameter. In the calculation dialog for the parameter, enter the field you use to connect Customers and Market (probably a Customer ID serial number). In your script, use the Get(ScriptParameter) function to grab the value passed from the button in a Set Field step:

Set Field [MARKETS::ForeignCustomerID; Get(ScriptParameter)]

[Andy M.]

That works well, thank you. I always thought there had to be a parameter passing function somewhere.

Now, can I pass 2 parameters ? (or am I asking for too much ?)

:

[John Mark Osborne]

Yes, you can pass two parameters. All you have to do is concatenate them into one parameter and then parse them out. For example, if you want to pass Customer_ID and Company_ID in a single parameter, use the following formula:

MYTABLE::Customer_ID & "¶" & MYTABLE::Company_ID

The "¶" character can be entered using a button in the calculation dialog and represents a return character. I used this character because it makes parsing out the value easier.

To parse the values, use the following formulas in two separate Set Field steps:

Left(LeftValues(Get(ScriptParameter); 1); Length(LeftValues(Get(ScriptParameter); 1)) - 1)

Left(RightValues(Get(ScriptParameter); 1); Length(LeftValues(Get(ScriptParameter); 1)) - 1)

The Left function around each of the formulas removes the extra return that is captured by the LeftValues and RightValues functions.

[Andy M.]

Wow.

That works great, thank you. I never would have been able to get that on my own.

For those of you that may also use John's example above, note that in the second line there is a LeftValues that should be a RightValues.

Thanks again John.

[comment]

Starting with version 8, you can use script variables to pass as many DISTINCT parameters as you want, e.g.:

Set Variable [ $customerID ; Value: Customers::CustomerID ]

Set Variable [ $companyID ; Value: Customers::CompanyID ]

Go to Layout [ Markets ]

New Record []

Set Field [ Markets::CustomerID ; $customerID ]

Set Field [ Markets::CompanyID ; $companyID ]

...

More importantly, once you have set the CustomerID in a Market record, all other information about the Customer is available from the related record in Customers - so in the simple situation you describe, you really shouldn't need to pass more than one parameter.