Yellow Triangle

[The_Oaks_Admini]

I finally was able to get a Dashboard to work correctly using Parent - Child relationship.

I am used to working with Inner and Outer Joins so all this Key, Ref relationships are new to me.

I am probably not doing things right as I am showing a yellow triangle with an exclamation mark.

I understand why as I have the Yellow Triangle. I have a one to many relationship between Parent Table and Child Table, however, the Child table has records that are not in the Parent (hence the Yellow Triangle). This is behavior is correct. We have few exceptions that our Child is supposed to handle by design. In SQL Server I would handle this through and Left or Right Outer Join.

I attempted to get rid of this issue by first Identifying the Parentless Children on the Child Table by using a Virutal Column [Parentless Children Count]. I then created a Slice and filter out the lonely Children by only including Records on the Child Table that had 1 or more counts.

I am using this slice on my Form, however I am still getting the Yellow Triangle and my Form is not working!

This Form is supposed to add records to the Child Table and when I load up it doesn’t allow me to continue since the Form is erratic.

1. I am making the Ref Field on my Child Table the same Field as on my Parent (only difference is that the on Parent Table this Field is a UNIQUEKEY and on the Child Table this field is not key but a label only so that I can display correct values on the Form)
2. I have added to the Ref Field on Child Table a Formula to calculate UNIQUEKEY that matches the Parent UNIQUE, so when the Form saves this UNIQUEKEY will sync with the Parent Table’s UNIQUEKEY. This Formula and Form were working fine before I added relationships to sync Parent and Child on Dashboard, which now the Dashboard is in good working condition, but the Form has become erratic.
3. I ought to be able to continue to generate UNIQUEKEY so that when I add records to Child Table the correct UNIQUEKEY exists on both the Parent and Child. All the other fields on the Form are erratic and are not allowing in me to synchronize my dependent combo boxes nor is the Form showing all the Fields that are included by Design?

Help anyone?

[MultiTech]

The_Oaks_Administrat:

however, the Child table has records that are not in the Parent (hence the Yellow Triangle).

This is sort of correct, what it really indicates is that the data you have in the ref column is not found as a Key in the ref table.

So for instance, in my ref column I have the value “Mr. Smith” - the name of the person who I’m trying to ref too - but it would show a yellow triangle.

This is because when you create a ref column, it’s using the value marked as the key for the ref table.

So the value that I actually need to use is the KEY value, not the name.

If I had the key value for Mr. Smith in the ref column, then the ref would work.

---

I"ll be honest, when you say

The_Oaks_Administrat:

I finally was able to get a Dashboard to work correctly using Parent - Child relationship.

I"m not entirely sure what you mean. I’m not clear on how you’re using a parent-child relationship to get a dashboard to work, or how that would affect how a form works.

It sounds like you’ve got things setup correctly, it’s likely just a matter of one little setting is off or something.

Can you verify that you ref column has the appropriate ref table selected?

[The_Oaks_Admini]

Thanks for your help!

So I am following up on your answer to my question.

First, I infer that you mean that the Key in the ref table is the Parent table, correct? If this is the case, then by design the Parent table is not suppose to include all the UNIQUE Keys on the Parent’s table.
In this case, the situation is such that there are records on the Child Table that don’t exist in the Parent table.

That is why I am filtering out these Records out of the Child Table so that both the Parent’s table Key and the Child’s Table Ref Column have a 1 to many relationship. So, for every Key on the Parent’s table there must be at least 1 matching record with the same Key on the Child’s Table.

How can I prevent the Yellow Triangle from showing up? Since I need to have the Parent Child Relationship mention on the above. In SQL, you handle this scenario with either a right or left outer join.

Not sure how to handle this with APPSHEET other than filtering out all the Child Records in the Child Table which Ref Column doesn’t match the Key on the Parent Table.

Since I am using a the result set from this Slice on all my UX, then I am not sure why I get the Yellow Triangle on any of my Form Views?

Is there a work around on this Yellow Triangle issue?

The Parent and Child Relationship has been created through the same Field/Column on both Tables. Please view attached Screenshots. The Parent shows the Field as the Key and the Child show the Field as the Ref.

So on the Dashboard, once I select any record on the Parent Form, then all the records that are related to the Parent’s Key will show on the Child Form below.

So this is working…

On the other hand my Form to capture new Child Records is not working properly.

It seems that I can’t calculate the Key on the Child’s Ref Field? Is there a limitation on AppSheet where you can calculate Keys when using a Ref type for a Column?

[Phil]

In SQL terms, a Ref is just a “foreign key” field. It simply contains the key value of the record in the target table.

If the “foreign key” field contains a key value that is not present in the target table, you will see a yellow triangle.

[The_Oaks_Admini]

Thank you for your response.

Is there a way to tell AppSheet that this is by Design? Like you do with Outer Joins in Sql.

There is no way to separate these records from the Child Table as this is a real situation. The Parent table only deals with one aspect of this real situation. In Sql this is not an issue. Is this an issue in AppSheet?

I could probably work around this issue by creating a Virtual Column to use a Computed Key instead of Column Field. I will have to play with it to have this Computed Foreign Key on Child Table establish a 1 to many Relationship with the Primary Key on the Parent Table.

If I could then make this Computed Key my Ref Column then all my issues will go away. I will be able to have the Dashboard correctly, I will get rid of the Yellow Triangle, and I will have Form that will be able to Compute and Add records to the Child Table.

If you see anything wrong with my observations above please let me know.

I am new to AppSheet so once I get familiar with its terms and functions I should be fine.

[The_Oaks_Admini]

Never mind. I understand how to make these foreign keys now through calculated keys. thanks.

[Scott_Hall]

Would you be willing to share your solution here? Thanks.