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.
Solved! Go to Solution.
@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.
@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")
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
@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.
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!
Youโre welcome @Mike. Truly my pleasure if it helped.
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
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
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โฆ
@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.
@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)!
@Mike
You can create your own inline views with creating REF type view for any required table.
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.
@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
@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โฆ
Hi @Mike
Provided you can share your app with co-author status for able3@able3ventures.com, I can have a look at the problem.
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โฆ
@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.
User | Count |
---|---|
41 | |
31 | |
29 | |
16 | |
14 |