Many to Many Form

How do I?
1

Hi. I created 3 tables: Clients, Accomplishments, and ClientsAccomplishments. The last one has 3 fields - it’s own key, and foreign keys to the Client table and the Accomplishment table.

I added a list page to the Client form for Accomplishments, but it shows ALL accomplishments, e.g. it doesn’t filter for just the accomplishments for a the selected client.

How do I do that?

2

And In case this is not clear - the behavior I want is so if a user is on the Client form, and they add an accomplishment to the list; it creates the appropriate record in the ClientAccomplishments table (and the Accomplishment table record as well if required).

They can also add and remove associations with existing accomplishment.

3

Hi DanH,

On the list page, instead of selecting the Accomplishments Form, selecting the ClientsAccomplishments Form should fix the issue. (as that’s the table that contains the foreign keys)
Make sure both foreign keys are included in the list by clicking on each field and toggling the ‘Include In List’ button.

  • Go to your ClientsAccomplishments Form and navigate to the Pages tab.
  • Select ‘General’ and click on the tab labeled ‘Fields.’
  • You’ll find both foreign keys listed here. Clicking on either of them will open their details.
  • Here, simply click on the button that says “Include In List”
    image
    image1844×1442 75.5 KB

Let me know if this resolves the issue or if you have any questions

4

Nice! Two follow ups.

  1. Any way to let them add a new item if the item them want is not on the list?
  2. Of the list of items to link are going to be very long, is there a better way to let them pick than a drop down?
5

Hi DanH,

  1. Yes, you can add a new page in the same form, this time for Accomplishments. In the display type, you can select “Grid”, enabling users to add items to Accomplishments in an Excel-like view
  2. I would again recommend switching from the “List” view to the “Grid” View, as it also allows Users to directly type into the drop-down which will filter it.

Let me know if this helps or if you have any other questions regarding this.

6

Hi Pranoy.

I did not fully follow.

When you said “same form” did you mean my Client form or my ClientAccomplishments form or my Accomplishments form?

And do I replace any existing Page or add as an additional page?

Thanks!
Dan

7

Hi Dan,

I am talking about the Client form here and you can add a new page for Accomplishments. Since you mentioned the behavior you want is so if a user is on the Client form, and they add an accomplishment to the list.

You don’t need to replace it instead add it as a new page. This will allow users to add new records in the Accomplishments table and then they will be able to select this entry from the ClientsAccomplishments page.