Include Primary Key and Table Name as Reference Constraints

[squatch4014]

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?

[Steve]

This makes no sense to me.

[squatch4014]

@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.

[Steve]

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:

help.appsheet.com

Dropdown from Valid_If

How to create a dropdown that depends on a particular list of values.

help.appsheet.com

List from Values

Create a list from raw values.

help.appsheet.com

LIST()

New list

help.appsheet.com

SWITCH()

Chose-one conditional evaluation

[squatch4014]

@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”?

[Steve · 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:

help.appsheet.com

CONTEXT()

Information about the running app.

help.appsheet.com

FILTER()

Select rows of table or slice

help.appsheet.com

STARTSWITH()

Does text begin with fragment?

[squatch4014]

That’s exactly what I needed. Thanks @Steve

[squatch4014]

@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?

[Steve]

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

[squatch4014]

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