Autofill advice

[Mark Moran]

Hi,

I’m looking for some advice. I know just enough about scripting/programming to get myself in trouble.

I’m setting up a coin database. There will be a dropdown field for coin types (e.g., Lincoln Penny, Jefferson Nickel, etc.) and a separate field for denomination (e.g., 1 cent, 5 cent, 1 dime).

I’d like the denomination field to autofill based on the selected coin type. Some coin types will share the same denomination (e.g., both Lincoln Penny and Indian Head Penny = 1 cent).

What’s the best way to accomplish this—using a script, a calculated field, or something else?

Thanks!

[Daniel Leu]

I would create a form for your coin types with two (or more fields), one is the type and the second one is the denomination. This way, you have an easy way to enter your data. A pick list would use that table as input which then is used for your dropdown.

Then the denomination field can be set as the result of a field script. Following is an example. It should work but I haven’t verified it!

You need to set the field ids and the form name according to your database.

function getDenomination() {
    // define fields in the main form
    const coin_type_id = "fld-xxxxxx";

    // define fields of coin types form
    const coin_types__type_id = "fld-xxxxxx";
    const coin_types__denomination_id = "fld-xxxxxx";

    // get the coin type from the main form
    const coinType = getFieldValue(coin_type_id);

    // get the types from the coin types form
    const coinTypeForm = document.getFormNamed("Coin Types");
    const coinTypeRecs = coinTypeForm.fetchRecords();

    // get the matching coin type record
    const matchingCoinType = coinTypeRecs.find(coinTypeRec => coinTypeRec.getFieldValue(coin_types__type_id) === coinType);

    // get the denomination from the matching coin type record
    const denomination = matchingCoinType.getFieldValue(coin_types__denomination_id);

    // return the denomination
    return denomination; // set return type as text
}

getDenomination();

• This reply was modified 6 days, 15 hours ago by Daniel Leu.

Cheers, Daniel
---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks

[Mark Moran]

Thanks for the advice!

[Mark Moran]

Wondering if I can or should someone move this to the Scripting forum??

I tried what you suggested. Both forms are in the same document and I’ve double checked the fld’s. But I get this error???

Get Demonination: ReferenceError: Can’t find variable: getFieldValue, line:(null)

=====

// define fields in the main form
const coin_type_id = “fld-1231556e0f234de9b2d85f50225b8135”;

// define fields of coin types form
const coin_types__type_id = “fld-415d31330587455cab106835b1d075fe”;
const coin_types__denomination_id = “fld-528c65271e894ebda93268d20f2329ad”;

// get the coin type from the main form
const coinType = getFieldValue(coin_type_id);

[Daniel Leu]

Sorry, it should be record.getFieldValue(coin_type_id).

Cheers, Daniel
---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks

[Mark Moran]

Ah, makes sense. Thanks. It works but it doesn’t update the field. I’ve tried updating a text field demonination or the script field itself but it remains blank. I added the bolded lines below and it still isn’t working:

const coin_type_id = “fld-1231556e0f234de9b2d85f50225b8135”;
const coin_type_demon = “fld-b7559ec0da8b4b42a9d270e1686e4746”;

…

// get the denomination from the matching coin type record
const denomination = matchingCoinType.getFieldValue(coin_types__denomination_id);

record.setFieldValue(coin_type_demon, denomination);

// return the denomination
return denomination; // set return type as tex

• This reply was modified 5 days, 11 hours ago by Mark Moran.
• This reply was modified 5 days, 11 hours ago by Mark Moran.

[Daniel Leu]

My script wasn’t intended to save the value to a field, but the return value would be the denomination. To save the field value, you need to use record.saveFieldValue() as you do, but you need to add document.saveAllChanges(); as well.

Cheers, Daniel
---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks

[Mark Moran]

Thanks that did the trick! Enjoy your coffee I just bought you.

[Daniel Leu]

Thank you for the coffee! I appreciate it!

One more thing: My intention for the script was, that it’s used as a field script and not a form script. This way, whenever you set the coin type, the denomination field is automatically set.

Cheers, Daniel
---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks

[Mark Moran]

Just came back here to say it’s not updating. It is setup as a field script (see attachment). According to the docs anytime I change the Coin Type it should run the script, no? It’s not, I have to manually click the “play arrow” on the script to get the field to update.

• This reply was modified 5 days, 9 hours ago by Mark Moran.

Attachments:

You must be logged in to view attached files.

[Daniel Leu]

1) change the double-quotes to single-quotes.
2) since it’s a field script, you should not need the record.setFieldValue() since the return value is what is used.

Cheers, Daniel
---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks

[Author]

Posts