Prefill field logic - not all fields available

[CharlieMullin]

I'm using the highly useful Prefilled Forms extension by Kuovonne and it works as intended. However, there are some fields in my source table that apparently aren't qualified to be prefilled on the form and I can't figure out the principle. (The form itself is in the Assets Updates table.)

The Assets table contains the prefill form URL in a field called Update Asset. It works fine but does not include a field called Asset Type that I want prefilled on the form. This field is not included in the list of possible fields to use when filling out the wizard for Prefilled Forms.

Asset Type is a linked field to a third table called Types. In this screenshot you can see that I've reduntantly added two additional fields that contain the value from Asset Type, thinking there was something about Linked records that was a problem:

But I can't use any of these fields to prefill either. Why is that? At least one other linked field, Modules, works fine and is prefilled on the form.

Here's a partial view of the 11 fields made available to me in the Formula for a prefilled form wizard:

There are 30 fields defined in the Assets table, and some of them for sure I don't expect to be able to prefill. When I try to code it myself, it just returns an error that the formula is invalid.

Why can't I prefill Asset Type?

Thanks for any guidance.

-- Charlie

[kuovonne]

Could you double check which tables you are using? Often there are two tables involved: the table with the form and the table that takes the formula. If you try to use the formula in the wrong table, it will not work.

[CharlieMullin_2]

Oh, but seems to work fine except I can't see the additional fields to add. The form is in the table where new records will be created and the table that takes the formula is in the table that includes the field I want to reference in the form.

But maybe I do have it backwards, although I don't see how since the data I want to appear in the form does not exist yet in the table with the form.

I'll play some more and see if I can cause different behavior.

I'll follow up with results.

Thanks!

[CharlieMullin · Accepted solution]

OK, I figured out what I was doing wrong. I simply had to add more fields to the Asset Update table to serve as containers for the prefilled data I wanted to add from the Assets table. I had no trouble manually editing the output from the Prefilled Form extension, which helped me understand better how this all works.

Thanks for your input that sent me on the path to the solution.

[airballer86]

@kuovonne can you explain the difference between the table with the form and the table that "takes" the formula? I am creating a duplicate "row" form and the only thing I cannot duplicate are the mulitple linked fields in a linked field.

[kuovonne]

If you are trying to duplicate a record, the table for the formula and the table for the form are the same.

The two tables are often different if you want to create a new linked record. The table for the formula is usually the parent table, and the table for the form is usually the child table in a one-to-many relationship. 

When prefilling a linked record field, I find it best to refill using record ID instead of the value shown in the user interface. If you are trying to duplicate an existing linked record field value, add a roll-up field that rolls up the record IDs of the linked records, joined with a single comma.  (Do not use a lookup field!) You can get the record ID using a formula field in the linked table. Then prefill the linked record field in the form using the roll-up value.

[airballer86]

@kuovonne Just to confirm, I create the rollup in the main table; using the linked record ID's. From here do I use our extenstion on the rollup field and not the linked record field? 

[kuovonne]

@airballer86 Yes, prefill the linked record field with the result of the rollup field. The rollup field should have the formula ARRAYJOIN(values, ",") and the result should be a comma separated list of record IDs.

[airballer86]

@kuovonne That did the trick! It works perfectly! Thank you so much!