Remove "New" from Ref Column

[1minManager]

I have a sub table that has a Ref column to a parent table. The user can see and edit this column.

So normally when they create a subtable record the Ref column is populated automatically, which is great. But if they go into the Ref column there is the option to add a new parent record. This is causing mayhem as the users are constantly adding duplicated companies rather than choosing an existing one. Can I turn this “New” option off somehow?

[GreenFlux]

One way is using a Valid_If formula to check the input against your existing list.

To ALWAYS prevent adding new records to the subtable from the parent Ref, use a Valid_If formula in the Ref with your list of subtable keys.

Valid_If

IN([Ref],Subtable[Key])

Or, if you want to only affect this view, try:

IF(CONTEXT(“View”)=“VIEW_NAME”, IN([Ref],Subtable[Key]), TRUE)

I think this could also be done using the Table’s 'Are updates allowed’ formula, but I haven’t tried that.

[WillowMobileSys]

To me this seems more like a process issue on the users end rather than a software issue. Obviously there are use cases when the New function on the drop down list is beneficial.

Users typically want to take the path of least resistance. So in this case I would ask, “Why are users opting to go through the harder path of entering a company when they could just select it from the list?”

Answering that will give you some insight as to how to best approach a solution, if you even need to. For example…Maybe the list to too long, so is there a way to reduce it to give them fewer options to scroll through?

[1minManager]

Hi @WillowMobileSystems

So its got a fairly complicated data structure. Its used by a training company and has 4 tables
Companies
People (a sutable of Companies)
Courses (Which is also a subtable of Companies)
Bookings (Which links each People to one or more Courses)

The main issue is people will create a Company, create a Course, then create a booking. But instead of linking Stephen Smith to the booking, they’ll add a whole new Person called Steven Smith via the Booking[Person] Ref column. So now I have 2 records for the same person. Which messes things up because the users need to go into a Person and see all the Courses they’ve been on

So to simplify this a bit. I want them to be able to add a Person from a Company record, but not add them via the Booking form

Hope this helps

[WillowMobileSys]

Got it! I understand the dilemma.

@Steve just posted a great solution!

[Steve · Accepted solution]

1minManager:

Can I turn this “New” option off somehow?

New occurs because the referenced table or slice allows adds. Reference a read-only slice of the table instead.

[llheath]

Is there any way to have the ref just show you the one record from the ref table?
I have the < > icons so the user is able to browse through that file, and I only want them to see the one record

[Steve]

Set Slideshow mode to OFF.