Redesigning Existing Structure to Use Join

[scneophyte]

Very excited to be at this point since linking records to forms was a significant chore for me. I am up against a challenge that, while I am still thinking about the solution for myself, thought I would throw to the community.

To maintain my privacy, I have a theoretical scenario below for a manufacturing plant:

PARENTS
Form 1: Nuts
Form 2: Bolts
Form 3: Screws

CHILD
Form 4: Expenses

Each expense on Form 4 links to a record on one of parent forms. Before the join field came into existence I had to have three Inverse Relationships showing on the Child form and then select the parent from from the appropriate field on the Child form.

Now, with the join field, I only need one field on the Child form!

Challenge: I want the join field on the Child form to be a picklist from records from the three parent forms

Workaround: I abandon the idea of having three parent forms and combine them into one formed named “Manufactured Items.” It is not my preference but could be done.

Thanks for reading!

[scneophyte]

Still working on this redesign. Since the original post, I have decided on combining the three forms but this still proposes a challenge.

Here is a better scenario: Book publishing. Each book is publish in print form, CD form, and digital download. I have one form for my books but want to track sales and expenses for each format.
Form: Books
Text Field (TF1): Author
Text Field (TF2): Physical Release code
Text Field (TF3): CD Release code
Calculation Field (CF1): Physical Release Join = TF1 + TF2
Calculation Field (CF2) : CD Release Join = TF1 + TF3
Link to Form Field (LtF1): Physical Book Sales = Join CF1 with TF4
Link to Form Field (LtF2): CD Book Sales = Join CF2 with TF4

Form: Expenses
Text Field (TF4): Release Join = Picklist

Current Options:

• manually add all possible entries from the CF1 and CF2 to a picklist
• create two picklists, one linked to CF1 and the other linked to CF2, then have two fields on the Expenses form, each linking its respective pick list

Requested Feature:

• ability to link a single pick list to more than one field on a form

If anyone has a better idea for how it can be done with the current feature set I am all ears!

[Brendan]

You can already link a single Pick List to multiple fields in a form. In fact, you can link the same Pick List to any Text or Number field in any form.

[scneophyte]

Thanks, Brendan. To be clear, I am trying to populate the choices of a Pick List from multiple fields. I’ve tried using shift, commands, etc to select multiple fields but to no avail. What am I missing?

(see screenshot)

Attachments:

You must be logged in to view attached files.

[Brendan]

Oh! Sorry for my misunderstanding. You can only select a single field on the Pick List itself to populate its values.

[Author]

Posts