Solved: Referances - Page 2

Jonathan_S · 01-31-2020 09:57 AM

Guys,

Im having a problem with referances.

I have a form with references, and a nested form inside this form. This nested form also has references. I want the references in the Nested form to be dependent drop downs based on a reference selected in the parent form.

Machine in the maintenance table is also referanced

See below,

The User selects a machine the maintenance is being performed on,
They Can than go to the next form and select what parts where used to repair the machine.
Depending on what part was selected in the maintenance form, I only want to show the parts from this machine in the drop down/reference.

Also when they select a category, I only want them to see the parts from the selected category for the machine (Selected in the parent form)

This is my maintenance Table/Form

The is my parts used Table

Parent Form

Nested form(PArts used)

Steve

Whew! This was a challenge! Try this:

SELECT(
  Machine Parts[Part Name],
  AND(
    OR(
      ISBLANK([_THISROW].[Part Category]),
      ([_THISROW].[Part Category] = [Part Category])
    ),
    (
      [Machine Name]
      = LOOKUP(
        [_THISROW].[Maintenance Form],
        "Maintenance Form",
        "ID",
        "Machine Name"
      )
    )
  )
)

SELECT(Machine Parts[Part Name], ...) what are the Part Name column values of the Machine Parts table from rows that match the given criteria (...; see (2)). This lists parts associated with the machine under maintenance, optionally limited to a specified part category.
AND(..., ...) are both criteria (..., ...; see (3) & (6)) TRUE?
OR(..., ...) are either (or both) criteria (..., ...; see (4) & (5)) TRUE?
ISBLANK([_THISROW].[Part Category]): is the Part Category column value of this Repair Parts Used form blank? If the user has not selected a part category, all parts are listed.
([_THISROW].[Part Category] = [Part Category]): is the Part Category column value of this Repair Parts Used form the same as the Part Category column value of the row of the Machine Parts table? If the user has selected a part category, only parts of that category are listed.
([Machine Name] = ...): does the Machine Name column value of the row of Machine Parts table match the given value (...; see (7)). This limits the list to only parts associated with the machine given in the maintenance form.
LOOKUP([_THISROW].[Maintenance Form], ..., ..., "Machine Name"): what is the value of the Machine Name column of the Maintenance Form table for the row with an ID column value equal to the Maintenance Form column of this Repair Parts Used form? This gets the machine listed on the maintenance form.

Optionally wrap the entire expression above with ORDERBY() to sort the list of parts by part name:

ORDERBY(
  ...,
  [Part Name]
)