Include Primary Key and Table Name as Reference Constraints

squatch4014
Participant II

Hello…

I believe what I’m trying to accomplish is a composite key between a STATUS table that also references TABLENAME of the child table. Ultimately my goal is to have different statuses that have the same “Name” but are used for different tables. In my STATUS table I have many different values that require a specific “Context”. The Context property is a reference to the TABLENAME of the table that wants to use the status.

Here is an visual example of what I’m talking about.

STATUS
ID Context Value
1e2356 OPERATORS ACTIVE
27yux4 OPERATORS INACTIVE
tjh249 OPERATORS TERMINATED
3yhjxc MACHINES ACTIVE
hgjcx7 MACHINES INACTIVE
OPERATORS
ID Status FirstName
826v24 1e2356 Joe
et2647 1e2356 Mary
96elx1 tjh249 Tom
MACHINES
ID Status Name
25whb 3yhjxc Dav01
kcs3n3 hgjcx7 Dav02

As you can see, I have “ACTIVE” listed as available choices for the OPERATORS table and the MACHINES table. When I create or edit an OPERATOR, I only want to see statuses that are applicable for that Context.

Is this possible?

Solved Solved
0 9 447
1 ACCEPTED SOLUTION

All rows from the STATUS table that have a Context column value of OPERATOR:

FILTER(
  "STATUS",
  ("OPERATOR" = [Context])
)

All rows from the STATUS table that have a Context column value equal to the current view name:

FILTER(
  "STATUS",
  (CONTEXT("View") = [Context])
)

All rows from the STATUS table that have a Context column value occurs at the start of the current view’s name:

FILTER(
  "STATUS",
  STARTSWITH(CONTEXT("View"), [Context]))
)

See also:

View solution in original post

9 REPLIES 9

Steve
Participant V

This makes no sense to me.

@Steve - That’s direct and honest. I appreciate that. Allow me to try again…

I want a single table, STATUS, that is linked as a foreign key to several tables. In STATUS, I have a column of Code. The values of Code can be the “same” but mean different things based upon how status is used with the other tables. When I’m creating or editing a record at the detail level, I want to show a list of only STATUS.Codes that pertain to that table.

Here’s a better example (hopefully ;-))

Table: STATUS
Column: Code
Values:
ACTIVE - Pertains to MACHINES.Status
INACTIVE - Pertains to MACHINES.Status
ACTIVE - Pertains to OPERATORS.Status
INACTIVE - Pertains to OPERATORS.Status
ACTIVE - Pertains to OUTSIDE_SERVICES.Status
OPEN - Pertains to OUTSIDE_SERVICES.Status.

Did I do a better job explaining? I want to try to populate a dropdownlist with appropriate options that pertain to that table.

Whew! Thank you! I think I’ve got it: you want the Code column of the Status table to be tailored to the content of the Context column of the same row.

Valid If for Code column of Status table:

SWITCH(
  [Context],
  "MACHINES",
    {"ACTIVE", "INACTIVE"},
  "OPERATORS",
    {"ACTIVE", "INACTIVE"},
  "OUTSIDE_SERVICES",
    {"ACTIVE", "OPEN"},
  LIST()
)

See also:

squatch4014
Participant II

@Steve - Wow! That looks amazing.

It appears that with your provided example, huge thank you btw, the Context and Code values are hard coded. Is there a way to have it be dynamic?

For instance…
If I’m editing an OPERATOR and want to see all possible STATUSes where STATUS.Context = “the_form_thats_doing_the_editing” (in this case, OPERATOR) and have the function return Code values of “ACTIVE” and “INACTIVE”?

All rows from the STATUS table that have a Context column value of OPERATOR:

FILTER(
  "STATUS",
  ("OPERATOR" = [Context])
)

All rows from the STATUS table that have a Context column value equal to the current view name:

FILTER(
  "STATUS",
  (CONTEXT("View") = [Context])
)

All rows from the STATUS table that have a Context column value occurs at the start of the current view’s name:

FILTER(
  "STATUS",
  STARTSWITH(CONTEXT("View"), [Context]))
)

See also:

That’s exactly what I needed. Thanks @Steve

squatch4014
Participant II

@Steve -

I took your expression of

FILTER(
"STATUS",
STARTSWITH(CONTEXT("View"), [Context]))
)

and it worked great for my “WORKORDERS” form. However, if I’m editing a row from a SLICE of the table “WORKORDERS” I don’t get viable STATUSes. I can see that the Context I setup in STATUS won’t match the name of my Slice (“CNC”, “DAV”, “HYDR” in my case). The Slice(s) that I created all do pertain to DEPARTMENTS.

Do you think there is a way that I can union these two?

Is my overall table design flawed and I’m trying to push a square peg in a round hole?

Please post a screenshot of the complete slice row filter expression.

Nevermind, @Steve. I took another approach to solve this problem. I made the Context field more generic.

Top Labels in this Space