Enum list as a ref type

Ami · 02-15-2020 07:27 PM

Hello
I have a parent table with employee ID and Names. The names are set as labels.
When i use a simple ref type, my user sees the Name and the ID is saved, thats great. However, if i want to allow multiple selections of names( Enum list) i cant use a ref type and i end up when the user selects a name and only the name is saved, not the ID. Any work around?

Aleksi

You need to choose EnumList with the base type of Ref.

gdutoit

@Aleksi
How do you delimit the list of values of the referenced table.
I tried a select in the suggested values but that did not work.

Aleksi

Do you mean with the Ref field?

gdutoit

enum list of type ref . . .
to a table with many thousands of rows
how do i delimit that to the relevant rows for the purpose on the enum list

leentsuki

Do you have an example how can you do this?

Aleksi

You can use a formula like YourTableName[YourKeyColumnName] in the Valid IF.

gdutoit

Assets has many [Related Parts] i.e. foreign key on Parts back to Assets
Assets has [Related Services] i.e foreign key on Services back to Assets
Service has [Related Parts] i.e foreign key on Parts back to Services

A part can only be used in one service.
Parts are added in bulk early in the process when Services do not exist yet.

So . . .
When a Service is added.
I need to present the user with a List of parts applicable that,

must obviously belong to the same Asset as the Service
it must not be used by another Service of the Asset already

The plan is to use an enum list in order for the user to select the relevant Parts and from that update the Part with the correct Service

SO . . . the enum list on Services is

of type Ref
the referenced table is Parts

As is it shows me all the parts in the table.
I need to delimit the list to

all Parts of the Asset of the Service
that is not already linked to a Service

Please help me formulate this.

Steve

It’s not clear what question you’re asking, or what the results should reflect. You want the assets associated with the asset being serviced? The parts associated with the asset being serviced? What information do we start with?

gdutoit

@Steve, @Aleksi
Please see my description under the image above.

Steve

This?

FILTER(
  "Parts",
  AND(
    ISBLANK([Service]),
    ([_THISROW].[Asset] = [Asset])
  )
)

Hua_Kwang_Pang

I have a column on [Contractor Name_1], and [Contractor Name_2], both of which is a ref to a Company Database [Name]. The filter is working and it display only the Contractor’s Name when I have the Valid_IF data validity.

FILTER(“Company Database”, ([Designation] = “Contractor”))

However, as a name is already selected for [Contractor Name_1], [Contractor Name_2] still display a enum of Contractor Name_1. Not sure exactly how to filter off the selected name when I have selected the Name for [Contractor Name_1].

Steve

Try:

(
  FILTER(
    "Company Database",
    ([Designation] = "Contractor")
  )
  - LIST([Contractor Name_1])
)

Hua_Kwang_Pang

Amazing it works, thank you very much for your help Steve

Hua_Kwang_Pang

I am trying to print a list of barcode with names on Barcode_Details.
For the Ref NameID, I have a Valid_If as follows:

(
FILTER(
“Company Database”,
AND(
ISNOTBLANK([NameID]),
(OR ( ([Designation] = “Manager”), ([Designation] = “Employee”) ) )
)
)
)

The above filter is fine as it select the names from the Company Database, but I would like to filter those I have selected in the Barcode_Details table.
The Barcode_Details form is as follows:

|OrderDetail Id| ID | NameID |
UniqueID Ref Ref

With the NameID selected, it still need to goto Barcode_Details to filter those duplicate names. May I check how to do it.

If the NameID is a Ref field, is it possible to Select All and Add at the Barcode_Details_Form?

Steve

I don’t understand the following:

Hua_Kwang_Pang

The Barcode_Details is a many-to-many relationship. The NameID is each individual names from Company Database which I will need to add at Barcode_Details. When I create an order of Print, I need to add the details of the names I need to print via the Barcode_Details. Was thinking how to filter the names of those I have added, such that it will only appear once in the table Barcode_Details table.

Steve

So no more than a single row in Barcode_Details may use a given NameID? Or, said another way: no NameID may occur more than once in Barcode_Details?

Hua_Kwang_Pang

Hi Steve, that’s correct. The NameID is from Company Database. It shows all the NameID during selection. Was thinking can the Barcode_Details NameID be filtered somehow to prevent multiple entries, as I only want to print one barcode per person only.

Steve

If I understand correctly, the following should work as the Valid If expression for the NameID column of the Barcode_Details table:

(
  FILTER(
    “Company Database”,
    AND(
      ISNOTBLANK([NameID]),
      IN([Designation], {"Employee", "Manager"}
    )
  )
  - SELECT(
    Barcode_Details[NameID],
    ([_THISROW] <> [OrderDetail Id])
  )
)

Hua_Kwang_Pang

Thank you very much Steve for your tremendous help, learnt something new today.
Yeah!