I have a form, that contains a reference tabl...

[Rosemary_Black]

I have a form, that contains a reference table related to a part number.

This table contains quantity’s of sub-parts.

I want the sum column of this table to calculate the extended quantity from the number shown in the form.

So, from my terrible diagram, here the sum column should show 15 and 15.

It it possible to do this kind of reference within a reference?

[Rosemary_Black]

@tony I’m going to explain these records a bit, and where I have gotten so far.

Table A - Logs that Have a component Part number, This part number is a reference to Table B.

Table B - This has a list of level 2 part numbers included in every component.

Table A Form, has an inline table that shows the level 2 parts and quantities using the following expression in a virtual column in Table A

=SELECT(Includes[_ComputedKey],[_THISROW].[Whole Component Code] = [Component Code])

I want a qty amount chosen on this form, to reflect in the sum column of this inline table.

Besides getting it to do the math, I can’t seem to get it to pull in the quantity from the form at all.

Ideas?

Should I stop trying?

[tony1]

@Rosemary_Black You can use a de-reference like this: [Reference Column].[Column from referenced table]

Here’s an example of dereferences in action: https://www.appsheet.com/samples/A-basic-demo-of-table-references?appGuidString=6ffad040-b04a-4325-a...

[Rosemary_Black]

@tony I am having a really hard time getting this to work.

I tried it first with the original setup of a double de-reference, and changed the structure to only use a normal de-reference.

I just can’t get it to work correctly.

I feel like I have tried everything.

[tony1]

@Rosemary_Black Unfortunately, I don’t think it’s possible to do in AppSheet currently. Google Sheets and Excel allow you to express more with their formulas.

It’s possible that someone in the community (@Aleksi_Alkio?) could come up with a workaround, but I can’t think of one.

[tony1]

@Rosemary_Black Right now double dereference (e.g. something like [Ref column].[Another ref column].[Some other column]) cannot be used. You’d need to create a virtual column to store [Ref column].[Another ref column] and then use [Virtual column].[Some other column] to access the second level.

[Rosemary_Black]

@tony Can the virtual columns be List type instead of Ref type?

I think my issue is not only the double dereference, but that 2 of the columns aren’t key columns.

Thank you for your help!

[tony1]

@Rosemary_Black Columns can be list type, but… I suspect that is not going to be helpful for you.

If table A has a reference to table B, then you can use a formula like [Ref to B].[Quantity] to access B’s quantity value. I’d start from that.

[tony1]

@Rosemary_Black I’m still confused. Each log (TableA) has a reference to a part number (TableB). Is there a difference between “component part number” and “level 2 part number”?

[Rosemary_Black]

@tony Yes, So each component is made up of a bunch of other parts.

So, The form (table A) references one component code, which pulls up the table of the parts within that code.

So, if the form calls for 2 of the component code, then all the quantities of the individual parts in the table needs to be x2.

[tony1]

@Rosemary_Black Okay, so it sounds like your relationships are:

Logs ==ref==> Components SubParts ==ref==> Components

The first relationship is saying that each Log has a Component. This means that each Component has many Logs.

The second relationship is the same: each SubPart has a Component, and each Component has many SubParts.

If I understand correctly, you’re saying that you want each SubPart’s quantity to be updated based on some input in the Logs table.

This can’t work, though. To see why, imagine that you have log1 and log2. Both of those logs reference component15, which has subpart8 and subpart3.

What should the quantity of subpart8 and subpart3 be? They are related to multiple logs (log1 and log2), so they have no notion of a single quantity.

So, with your data, there’s not really a good way to do this.

[Rosemary_Black]

@tony The thing is, I am only trying to see this data virtually, not actually log it anywhere.

Just in the form, so the total parts quantity can be seen.

If I were making this table in a spreadsheet for example, I would so something like

=‘referenced Part Qty’*(index(‘logs’,match(‘This Logs Key’,‘Log Key Column’,‘Return Component Qty’),1))

I have to imagine there is a way to make this work in the app, if I could make it work in a spreadsheet right?