List of REF_ROWS dropdown not appearing

[Matthew_Wolman]

I promise I’ve looked everywhere and there just isn’t someone who is answering this question within a relevant timeframe. I see answers for this question but they are so old they are using an older version of AppSheet and I can’t follow along.

I have a table that is a list of Claims. Medical claims. I also have another table called Work Orders. I need to put in a Work Order and select multiple claims when I do so. The Claims table has a reference to Work Order. One Work Order can have multiple Claims. This automatically generated a list of REF_ROWS in my Work Order table, referencing the Claims.

Naturally, in the form, I would expect there to be a dropdown of Claims I can select when I’m creating my Work Order. I’ve created the form and there are no dropdowns. I’ve even tried to explicitly add the columns but they still don’t appear even when I add them.

I see videos around using techniques with VALID_IF constraints and what not, but none of them work for me. I’m stumped. Please someone explain to me how I can get a dropdown list of the items from another column that reference the item in question.

[Matthew_Wolman]

I actually broke the application! I saw that I could mark the field required before saving the row. Someone has to select at least one claim to save the Work Order. Surely, surely, I would be forced into presenting a dropdown, because this column has to have a value to be saved! But nope, the app decided to not work all together, rather than provide me a dropdown.

[Marc_Dillon]

Matthew_Wolman:

Naturally, in the form, I would expect there to be a dropdown of Claims I can select when I’m creating my Work Order.

Matthew_Wolman:

Surely, surely, I would be forced into presenting a dropdown,

Not quite how it works…

Clarification question: do the Claims records already exist, and you’re wanting to connect them to a Work Order record? I’ll let you answer that first, since it’ll greatly affect how we move forward.

[Matthew_Wolman]

The Claims records do already exist

[Matthew_Wolman]

I’m probably going to move over to a feature request at this point. I’m surprised, but I think the AppSheet developers think they have this feature working when they really don’t

[Steve]

Nope, it’s you that entirely misunderstand AppSheet, or references between tables, at least.

[Matthew_Wolman]

I see… please forgive me Steve. I’ve been thinking about this and I realize, on a couple of levels, why I wouldn’t be offered a drop down. My instincts have always told me there is a way to do this, but am I wrong? If my misunderstanding has lead me down a path that doesn’t have an answer, I’d like to move in the right direction. Is this feature available and/or possible?

[Marc_Dillon]

In a one-to-many relationship set up, the key value of the “one” is stored in the records of the “many”. That means that the data change necessary to create the reference has to be in the “many” records, not the “one”. In this regards, you’re looking at it quite opposite from the beginning.

Often, in Appsheet, a typical one-to-many, or “parent and children”, relationship is created by first creating the parent record, then from within the parent record, the children are created. This child creation is typically done through the “magic” of a REF_ROWS() column, where a new child is auto-assigned the parent’s key value.

Since your child records already exist though, the scenario is quite a bit different. Instead of creating the child records, you need to somehow trigger the data change in each of them to link to the parent.

There will be several ways to accomplish this.

The easiest way would just be to create the parent Work Order, the navigate to each existing child Claim, edit them, and in their Ref type column for the parent, you should see a drop-down list of all Work Order records to choose from.

On top of this, you could design some ways to make the process easier for your users.

One way that I’ve used quite often is to provide an additional real column in your parent record, an EnumList with base type Ref pointing to your child Table (make sure to specify a valid_if expression for Enum/EnumList w/ base-type Ref!). This will provide a drop-down at the parent level where users can select existing child records.

Then from here, you’ll need to execute a “reference action” (Action type: “execute an action on a set of rows”), pointing to the Claim Table, and utilizing the new EnumList as the “which rows to execute on”. This has recently been made a lot easier to do thanks to the new INPUT() expression, where you can push the parent key value through to the ref’d Actions (search for a thread here in the tips-n-tricks section by Kirk for more info on that).

[Matthew_Wolman]

Alright, I was able to get it to work. Thank you very much. The take away for me here is that the EnumList column needed to be a real column. When I tried to make the EnumList from a virtual column, I kept getting errors and warnings. Furthermore, in order to accomplish this, I needed to create a separate list from the Related items list. Just out of curiosity, do I still need the automatically generated “Related” column? Hmmm… I’m thinking I do. My instincts tell me so but I don’t exactly know why. Probably something to do with getting back to the Work Order from the Claims view.

I come from a programming background, mainly Node.js. From a programmers perspective, this is the logic I was trying to accomplish:

When creating a new record for a table A, give me a list of all the items from another table, B, that has a column storing the key of record A. This is the one-to-many relationship. One A has many Bs. Before I create record A, I want to select all the records from B that are going to relate to it. So, in the creation process, I select all the B records, and when I click create, the algorithm grabs the newly generated key for A and pastes it into the column in table B for all the selected records."

By this logic, I should be able to select all the records that will relate to my record as I’m creating the record. Generating the new key and applying it to the column for all the selected records in the other table seems like valid logic. Am I wrong to think this? I feel like it could be accomplished with programming.

[Matthew_Wolman]

Disregard my question about the Related list. I didn’t fully understand what you were telling me until now.

Creating the dropdown list is one thing, but then you’re saying I can also create an action that takes the information from the drop down list and actually applies the key to the “many” records table?