How to Do Reference Multiple References to the same table

[Jonathan_S]

Hello Guys,

Have a slight Dilemma,

I have Table A & Table B

Im trying to Referance Company Name and Employee Name from Table A to Company Name and Employee Name in Table B.

Problem is Cant figure out how to set the Column it is referencing. I can only get them to reference the same Column,

How would I go about getting this implemented?

[Steve]

Screenshots of what you’re trying?

[Jonathan_S]

Also, I tried to change the label, etc, but cant do multiple

Red to Red,
Green to Green

[Steve]

Ah. A Ref values references the entire row; you can’t “reference” a single column with a Ref value. You can, however, dereference a Ref value to get the column value from the referenced row. You’ve not shared enough information about you data to offer concrete suggestions on how to implement this for theses two tables.

See also:

help.appsheet.com

Dereference Expressions

Use a dereference expression to retrieve a field value from a referenced record.

[Jonathan_S]

hmm, see, I love the way how one can currantly add a new Name from the drop down,

So what I want, it first select company, and than all the names in the table that have the same company as selected to appear.

But want the NEW in the drop down to stay as is…

[Steve]

Which table is the form using?

[Jonathan_S]

Signinout

[Steve]

Okay, so the user is completing a form for the Sign inout. They pick or add a value for Company Name. You then want the user to be able to choose (or add?) a Visitor Name value from a list of only names associated with the chosen company. Yes?

How is the Contractor Policies table involved?

[Jonathan_S]

So the first time anyone comes to do work from a company

If the company is not in the system yet they add the company,
Than they review contractor policy and sign it.

If there company is already in the system, they just need to enter a new Visitor Name, each contractor from company needs to sign contractor policy, but this is only ever done once,

Going forward they can just select there name from the drop down.

[Steve]

What table(s) does (do) the Company Name columns reference?

Same question for the Visitor Name columns.

[Jonathan_S]

They Both reference the Contractor Policies Table.
Wait I’m a tool, 1 second

[Steve]

You probably shouldn’t be using Ref for any of those four columns. Instead, use Text or Name type, and use Suggested Values to produce a list of existing values to choose from or add a new value.

[Jonathan_S]

So that was that, Now Company name is referanced from the companys table and the Visitor name comes from the Contractor Table,

See Folmula,
Only thing is apparently my referance is broken?

Im trying to Use Referances wherever I can because any name edits, or company name edits changes everything which i Love,and i love the relational lists etc. for Easy Data overview

[Steve]

I don’t think you’ll be able to use Ref values in all four columns here.

[Jonathan_S]

its only 2

[Steve]

Well, three:

Regardless, a single row can only have one (textual) label.

[Jonathan_S]

Right,

So its kinda Working now, just my reference is broken.

3 Tables.

A. Signinout
B. Contractor Policy Table
C. Company Table.

Table A
2 references.
Company References the Company Table
Visitor Name references the Contractor Policy Table

Table B
1 Reference
Company Name References the Companies Table

Table C
No References.

I’m getting a Broken Reference on Table A for Visitor Name.
This Formula
Select(Contractor Policies[Visitor Name], [_THISROW].[Company Name] = [Company Name])

Without This Formula, everything works perfectly. It just shows all the names, but I only want the names related to the selected company to show up when a user signs in or out.

Any Reason Why it works fine without formula but shows broken referance with formula? Is it my formula? Not sure what I should Change.

[Jonathan_S]

Got it working

[Jonathan_S]

Sorry, Misread,

So Yes,

If there company does not exist yet, add it,
If there name does not exist yet (Based on Selected Company), add it
If it does select it.

[Jonathan_S · Accepted solution]

Fixed.

Select(Contractor Policies[Visitor Name], [_THISROW].[Company Name] = [Company Name])
Changed to This

Select(Contractor Policies[ID], [_THISROW].[Company 
Name] = [Company Name])