Referances

[Jonathan_S]

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]

Jonathan_S:

I want the references in the Nested form to be dependent drop downs based on a reference selected in the parent form.

If by “dependent drop downs” you mean the dependent dropdown feature, they cannot be. The feature requires the dependent columns be adjacent to each other in the same table. But just because you can’t use the feature doesn’t mean you can’t do what you want: you’ll just have to do it manually.

Jonathan_S:

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.

For the Valid If expression for the Part Name column (I’m assuming):

[Maintenance Form].[Parts Used]

[Jonathan_S]

Ok, How would this be done manually? I still want to use references so if any item gets edited(Name) its everywere.

For the second part, A user selects the machine. Each machine is assigned parts. So i only want the parts to show in the nested table for the machine selected in the parent table.

To get only the parts to show up from the selected Category, Im trying a simplified formula where im only checking if the part is from the selected category, later I will add a check to make sure its from the currant machine. This is the formula Im using and it wont work,

SELECT(Parts[Part Name],Parts[Part Catagory].[Catagory] =[Part Catagory].[Catagory], FALSE)

[Steve]

You’re omitting a lot of information.

Please provide:

• A list of columns for all involved tables.
• The key column names of each table.
• The Ref column names of each table and the tables to which they refer.

[Jonathan_S]

Most of my problems are in the Maintenance form and the repair parts used tables.

So Currently I have the Following Tables

Machine Categories
Create Categories to place machines into
Part Categories
Create Categories to place parts into
Machines
Add machines Along with a reference to a Category from Machine Categories.
Parts
Add parts here along with a selected category referenced from Part Categories
Also Select Supplier Referenced from Suppliers Table
Suppliers
Add Suppliers here
Machine Parts
This is when you select a machine and a part to mesh them together. So you can see what machines use what parts etc.
Machine is Referenced from Machines Table and Parts are Referenced from the parts Table
Maintenance form
Document maintenance in this form. You must select a machine which is referenced from the machine table.
You can than Select parts you used to repair the machine from the nested Repair Parts Used table.
Repair Parts Used
This is a nested form inside the Maintenance form. Here you can select multiple parts that where used to repair a machine if applicable.
Part Category is referenced from the part Categories Table
Part Name is referenced from the parts Table.
In this form I want to check what machine was selected in the Maintenance form and show only parts that are referenced in the Machine Parts Table that have the same machine along with the same category as selected in this form.

My Tables Along with the Key Columns. All the Key Columns are ID

Hope this helps you help me

Thanks

[Steve]

Let’s start with this…

For the Repair Parts Used table Part Category column, set the Valid If property to:

ORDERBY(
  FILTER("Part Categories", TRUE),
  [Category]
)

This should provide a list of parts categories sorted by category name.

For the Repair Parts Used table Part Name column, set the Valid If property to:

IFS(
  ISNOTBLANK([Part Category]),
    ORDERBY(
      FILTER(
        "Parts",
        ([_THISROW].[Part Category] = [Category])
      ),
      [Part Name]
    )
)

This should provide a list of parts within the chosen parts category, sorted by part name.

See also:

help.appsheet.com

FILTER()

Select rows of table or slice

help.appsheet.com

IFS()

First-match conditional evaluation

help.appsheet.com

ISNOTBLANK()

Is value present?

help.appsheet.com

ORDERBY()

Sort row references

[Jonathan_S]

Steve:

IFS( ISNOTBLANK([Part Category]), ORDERBY( FILTER( “Parts”, ([_THISROW].[Part Category] = [Category]) ), [Part Name] ) )

I will use your first Filter suggestion allot more often. This is great.

So now The dependant drop down is working, but how would I make sure its only showing the parts that are connected to the selected machine in the maintenance form?

User selects machine in this form,

This is my Machine Parts Table.

[Steve · Accepted solution]

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"
      )
    )
  )
)

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

2. AND(..., ...) are both criteria (..., ...; see (3) & (6)) TRUE?

3. OR(..., ...) are either (or both) criteria (..., ...; see (4) & (5)) TRUE?

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

5. ([_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.

6. ([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.

7. 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]
)

See also:

help.appsheet.com

AND()

Are all true?

help.appsheet.com

ISBLANK()

Is value absent?

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

help.appsheet.com

OR()

Are any true?

help.appsheet.com

ORDERBY()

Sort row references

help.appsheet.com

SELECT()

Gather column values from row in table or slice

[MultiTech]

Just as an aside here:

placing any sort of SELECT() type of formula (lookup, select, filter, etc.) inside another SELECT() will cause some slow down - especially if you’ve got a lot of data.

Just something to keep in mind.

AppSheet handles this stuff pretty well in the beginning, but later on - if you keep using the app and don’t deal with the data - things will really start to slow down and some changes in how you’re storing variable information like that needs to be addressed.

Just thought I would throw it out there.

[Steve]

It’s a Valid If expression, so its impact is limited.

[lucasferreira]

Sorry, in which column was this 'valid_if' expression set up?

I have a similar problem with several references and I didn't understand if the expression must be set up in the Ref Column of the main table or in the column where the options are.

[Jonathan_S]

This is unbelievable.

Wow Steve. I hope other especially new creators here can learn from this and the true potential of appsheet.

[Jonathan_S]

I will let the future tell.

[Steve]

The option is to carry the Machine Name column value from the Maintenance Form row down to the Repair Parts Used row:

1. Add a Machine Name column to the Repair Parts Used spreadsheet and table.

2. Set the Machine Name column’s App formula expression to [Maintenance Form].[Machine Name].

Then the expression I provided earlier can be simplified to:

SELECT(
  Machine Parts[Part Name],
  AND(
    OR(
      ISBLANK([_THISROW].[Part Category]),
      ([_THISROW].[Part Category] = [Part Category])
    ),
    ([_THISROW].[Machine Name] = [Machine Name])
  )
)

Thanks, @MultiTech_Visions!

[Jonathan_S]

This is true :), Thanks. Will implement this. Always room for improvement

[MultiTech]