Filtering a reverse reference to display only related records with "open" status

references
#1

I have two tables in my app that are related. The ACCOUNTCARD table has one or more related ACTIVITIES. As a result, the ACCOUNTCARD table has a virtual column called “Related Activities” that is a List type, with the following formula:

REF_ROWS(“Activities”, “AccountCard”)

All works great as the Accounts detail view shows the “Related Activities” records as a table. But what I really want is to only show the “Related Activities” records where State=“Open”.

So… Is is there a way to modify the above REF_ROWS formula to select only records where State=“Open”, or do I need to add another virtual column to Filter or Select from the “Related Activities” list?

I cannot seem to get the syntax right for either option so some example syntax would really help.

2019-03-22_12-29-50

(Levent Kulacoglu) #2

@Mike
Create a slice for your Activities table with [State]=“Open” and slightly change your REF_ROWS expression to cover this slice i.e.

REF_ROWS("slice_Activities", "AccountCard")

2 Likes
#3

So… the simple way is to point the REF_ROWS at a slice. I guess this avoids a performance hit as well. Will do that…

Was kinda hoping I could use a Filter or Select to avoid adding yet another Slice, and let me use Show_If to allow me to display one of more filtered versions.

Thanks @Levant

(Levent Kulacoglu) #4

@Mike
If your intention is just to show the related records in the Detail View as read_only (users won’t be able to add new records), then you can set a VC with SELECT or FILTER expression as well rather then using REF_ROWS. Totally depends on your design and how you want your users interact with the that data.

#5

Ah… I see. I can return a list with Select or Filter, but that would be would not allow editing of the complete record. That’s actually what I found when experimenting.

Since the use case requires users to be able to drill down by selecting open activities and possibly editing, so I just modified the REF_ROWS and went that way. I still find setting up table relations properly a bit “uncomfortable” in appsheet. Thanks for the guidance!

(Levent Kulacoglu) #6

You’re welcome @Mike. Truly my pleasure if it helped.

#7

one last strange question. I am troubleshooting as we speak… but in case this makes any sense to you…
I have a Slice that returns just open activities called “Activities (Open)”. I modified the AccountCard REF column formula to be REF_ROWS(“Activities (Open)”, “AccountCard”). When I added that modified virtual column (“Related OPEN Activities”) to my View, rather than a table being displayed I get a Chart! Odd… not sure whats happening here :slight_smile:

2019-03-22_14-17-24

#8

Hmm… REF from a table renders a table list. REF from a slice is rendering a chart…
I have to figure out where the sub views are coming from :slight_smile:
2019-03-22_14-40-27

The UX view is “Account Cards_Detail”, which is where I added the REF formulas, but not clear to me how to control what view the REF columns are using to display the related lists.
REF_ROWS(“Activities”, “AccountCard”) is returning as a table
and if I click on “View(3)” it opens the “ALL Activities” view which is a Table view

REF_ROWS(“Activities (Open)”, “AccountCard”) is returning as a chart.
and if I click on “View(2)” it opens " Count by ACCOUNT" view which is a Chart view

Sorry if I am confused, but with these REF tables I am not sure how the views are specified…

Show different columns for inline reference views from same table
(Levent Kulacoglu) #9

@Mike
Locate the inline view for your slice from UX pane, create copy of that system generated view and then you can change the copied view’s type from Chart to Table.

#10

@LevantK
Interesting. For the Tables, there seems to by system generated views with _inline at the end of the name. For the slice, there are no system generated views, so no _inline to copy. Might be why it is using “wrong” view.

Wondering how come I don’t have system generated views for these slices, and how to get them back (and no, my system views are not hidden). Would hate to have to re-create the slice (as it impacts other views), but may have to. If you have a better approach… do tell (and again … thanks for the help)!

(Levent Kulacoglu) #11

@Mike
You can create your own inline views with creating REF type view for any required table.

#12

Ah… right. What I’m not understanding is how Appsheet is picking the view to use for the REF_ROWS list. If I have more than one table view for the slice, which one will it use… anyway, I will give it another try. Thanks again.

(Levent Kulacoglu) #13

@Mike
When picking the type of an inline view, AppSheet is relying on the data and the table structure. For example if you have an image type column, it generally assigns a Deck UX. Some kinda Machine Learning :slight_smile:

1 Like
#14

@LeventK - this still does not make sense to me. Maybe I am a little dense…
The Slice I am using in the REF formula has multiple table Views. For some reason when I add the REF column to the Account Cards_Detail view, it uses the Count by ACCOUNT view instead of one of the 7 Table views available to display the REF records list (for example “By Account”).

I am not clear how to specify which view the REF list should be displayed with, and you indicate appsheet “automatically” selects a logical view. Here are the views this slice has, and I want the display to be a table and not chart as the REF list did when referring to a main table instead of the slice. I must be missing something…


2019-03-22_14-17-24

(Levent Kulacoglu) #15

Hi @Mike
Provided you can share your app with co-author status for able3@able3ventures.com, I can have a look at the problem.

#16

Thanks @LeventK. It’s a set of company data so let me take a look in case sharing presents an issue. I do appreciate the offer…

#17

@LeventK - I was able to fix this in a very strange way using hints from another post. Basically had to add another slice just for this purpose (though I already had a slice I should have been able to use but that did not work).

I still don’t know how to define which view the REF_ROWS list will use as an inline view, but my strange workaround did accomplish what I needed. Seems like if you have many views for a slice, this may create issue for the REF_ROWS view definition?

Sometimes figuring out where to define what in appsheet can be very confusing - at least it seems illogical to me… More to learn I guess. Thanks for trying to help.