I want to confirm if referenced behavior has been changed

Hello. I have some reference columns that point to a ‘hidden’ key column in another table. It’s supposed to list rows that have a certain value in another column. However, sometime in the last few weeks the column stopped listing rows altogether, and it was only resolved when I ‘showed’ the key column it points to - which leads me to believe that the way referenced rows are shown have been changed. I just want to confirm if this is intended, because I have a lot of columns to go back to and change if so.

0 21 1,709
21 REPLIES 21

Are you able to take printscreen(s) form that situation so it would be easier to understand what your problem is, thanks.

Screenshots won’t help because a screenshot will only show blank spreadsheet cells. I’ll try to explain. Basically, we all know that we can reference tables using the REF column type, which fills out the field with the KEY for that reference table when filling out a CHILD form, yes? I hid those KEY columns because they were strings that the users could not understand and only cluttered the form. It worked before - but NOW, the table with the REF column “cannot see” the hidden column and shows up blank - and it’s affected all my apps. The sheets supposedly referencing a PARENT table are referencing nothing, and the data encoded by the users are disjointed. I am running several apps with a lot of references as this has a LARGE impact on me. The apps are useless right now. I am forced to unhide the ugly KEY columns.

On another note - are the devs still active in this forum? I have posted several questions already and this is the first reply I’ve gotten.

First… what post have you sent? I searched your posts but I was not able to find any not replied posts.

This.
Others I sent using other accounts.

Like?

The example you posted involves a spreadsheet formula. AppSheet’s handling of spreadsheet formulas are very infrequently addressed here in the community, and I doubt there are any “experts”. For AppSheet’s apparently-incorrect handling of spreadsheet formulas, you would do well to reach out to support@appsheet.com.

Are you saying this… you have child records in your spreadsheet where the parent ID is missing?

Yes. I have been a user since at least 2015 and hiding the key column of the parent table was never an issue until now.

Have you set the “Editable If” as OFF with the Ref column?

No, I hid them.

Edit: Just FYI even with Ref columns that are not hidden, rows from tables that have hidden key columns are not shown as choices.

If you hide that field how your users are able to choose the correct parent record?

They don’t have to. When they go to the data summary screen for the table, the children tables are listed at the end, with choices to add a new record for them, or to edit existing records. If they add a new record in a child table, the parent ID is automatically filled in by the ref column pointing to the parent, even when hidden. Likewise, hiding the key column for the parent table has never been an issue.

Those apps I made follow a strict encoding workflow, starting from parent to children. They have to view the parent summary before they can access any children. No child table is accessible from the get go, only parent tables.

What view type are you using with the inline view?

Table.

Bump

Hey Mobile_Tuberculosis,

I’m not 100% sure what your current workflow is, but i think i know where you’re coming from.

Hidding KEY and LABEL fields is a bit of a no-no generally speaking.

A KEY column in my opinion should always have a UNIQUEID() as an initial value, with the rare exception when you have a users table and an “email” could work just as well (which can make your life very easy later when/if it comes to creating security filters based on your user base).

Here’s how i hide my KEY columns without really hiding them.

In the show_if:

NOT(
OR(
CONTEXT(“ViewType”)=“Detail”,
CONTEXT(“ViewType”)=“Form”,
CONTEXT(“ViewType”)=“Dashboard”
)
)

Goes without saying that you’ll have to come up with a solution of the LABLE but that’s not a big deal… you can always use something like a CONCATENATE() in a VC and have that be your LABLE.

my two cents - hope this helps a bit

Thank you for the response and suggestion. I will try this.
I’m still confused by the change though, for as I said, my methods worked for years. Is there a summary of changes made to the AppSheet app in this forum? I want to see if this was intended.

Steve
Participant V

Not a problem.

What is the “it” that is “supposed to list rows that have a certain value in another column”?

Depends on the app.

Good idea. I consider that a best-practice.

What makes you think it “cannot see” the hidden column? Can you dereference the reference to get other column values from the referenced rows?

Please post screenshots from the app editor of the column lists of the affected tables, being sure to show the key and label columns of each.

What is the “it” that is “supposed to list rows that have a certain value in another column”?

The ref column.

What makes you think it “cannot see” the hidden column? Can you dereference the reference to get other column values from the referenced rows?

It didn’t show choices when the key column of the referenced table was hidden. It started showing them when I unhid the column.

Apologies, I’m not clear on what you meant by the second question there.

Like?

I apparently misspoke when I said other accounts, apologies.
The other question was a feature request and it’s from this account.

Noted about the community not answering spreadsheet formula questions.

Your tag says appsheet_team, are you a developer (or at least someone aware if this change is intended)? I’m asking because I want to know if I need to watch out when it gets reverted back, or if I should look for a permanent work-around.

The behavior you’re describing certainly sounds odd, but I also suspect we have some communication problems. I feel like I’m missing some information, but don’t know how to ask for it.

I am not a developer; I’m a sometime technical writer. I have no particular insight into the deeper development decisions or plans.

I’ll attempt to explain once more but I appreciate you trying to help me. No pressure to solve this one on your end. It seems like I have to work around it. I just hope that someone aware of such things see this.

What I used to do for reference/dereferenced tables is that I hide the key column of the table being referenced because 1) it’s automatically computed (I use a combination of date/time concatenated with a value from uniqueID) 2) the users are lay persons who do not need to edit a key column. Let’s say Table 1 is the parent (the one being referenced) and Table 2 is the child (the one with a ref column pointing to Table 1).

When a new form of Table 2 is made, the ref column is automatically filled with the key value of Table 1 since Table 2 is accessed only through Table 1’s summary view. This, as I understand, is default behavior. This happens even when the key column of Table 1 is hidden. Note that the ref column itself is hidden as well.

Last week, however, we noticed that the ref columns of new rows for Table 2 are blank. This is bad because we need to link the data of Table 2 to Table 1. We’ve had to discard some data because we cannot link some of them.

To see what’s happening, I unhid the ref column of Table 2, and when I looked at the dropdown, it only shows the “New” hyperlink, with no choices, when it should be showing key values (or labels) of rows from Table 1. When I unhid the key column of Table 1 and then tested the ref column of Table 2 again, it then showed the labels of rows from Table 1 like it normally would.

This is why I surmised that Table 2’s ref column “cannot see” the key column of Table 1, because it is blank when the key column is hidden, but works normally when the key column is shown.

Because the key column “has to be filled in”, the app ignores the Editable setting when I set it to “No”, so the users can not only see the key column, they can edit it.

Note that I have always had the “apply show_if universally” checked even before this event happened. This is why I was asking if something was changed, and if so, if it was intended.

Note also that as soon as I unhid the key column, the users started asking about it and whether I could hide it, to be safe.

Thank you for the thorough explanation!

There should be no reason that key columns cannot be hidden. As I mentioned earlier, I consider hiding key columns a best practice.

It sounds like you’re using an initial value expression to compute the key column value. To prevent the user from editing the column in the event the column is visible to the user, I recommend setting the key column’s Editable? property to the expression, FALSE, which prevents the user from modifying it but allows the app itself to.

The behavior you describe certainly sounds wrong, but I’m not aware of any other reports of this. I would encourage you to reach out to support@appsheet.com for direct assistance. There may be a bug in the internals of your app config that requires deeper investigation than I can provide.

Top Labels in this Space