I'm working on an app to register who's atten...

[Mikko_Palmu]

I’m working on an app to register who’s attending games for sports team. There’s a table A for all the events of the team, Table B for all the players and table C to hold players yes/no reply for certain event (EventID ref to Table A, PlayerID as USEREMAIL() ref to table B). Table C IsPartOf A, but is that necessary?

My idea is to show the Table A as deck view, showing the time and place of game and then there’s Action buttons in the action bar for adding yes/no row to Table C (or changing previous answer).

I tried creating Action1 to Table C that adds a new row to Table C, and then another Action as button to deck view of Table A that “executes an action on set of rows” on Table C, Referenced rows are the REF_ROWS and action is the Action1. That doesn’t work when REF_ROWS list is empty, and it doesn’t find the correct EventID.

Any help on getting the actions to work?

[Aleksi_Alkio]

I believe the easiest solution would be deep link with LINKTOFORM. With that formula you can copy your event’s ID for example and the user’s email as well. And if you add the answer Y/N into that deep link, everything is there.

[Mikko_Palmu]

@Aleksi_Alkio thanks, I’ll check that formula

[Mikko_Palmu]

@Aleksi_Alkio Thanks, I got it to work, not exactly like I wanted but still somewhat ok with an TableA Action to another view:

if( IN(USEREMAIL() , [Participant refs][Email]), LINKTOROW(ANY(SELECT([Participant refs][Key] , [Email]=USEREMAIL())), “TableC_Detail”) , LINKTOFORM(“TableC Form”, “Match ID”, [_THISROW] ))

So it either creates new row with form, or edits existing. There’s 1 extra click to use form and 2 extra clicks to edit detail view compared to just clicking Yes or No button on Deck view.

Another question:

I have player statistics from last 5 seasons per game in one table, columns are

[Season] [Match ID] [Player ID] [Goals] [Assists] [Points] etc…

How can I create table of player statistics per season? I have table of players and I can easily pull the goals etc into that, but it only gives the total amount, how do I apply the Season filter ?

[Aleksi_Alkio]

The easiest solution is to create an additional summary table with the QUERY sheet expression. With QUERY you can group your statistics for every season. And because you are reading your data from the statistics table, it will update the summary table every time when you sync the app.

[Mikko_Palmu]

@Aleksi_Alkio So it should be done on gSheet level? Or would Excel be better?

[Aleksi_Alkio]

Yes, you can use both if that’s possible with Excel. With gSheet it’s quite simple.

[Mikko_Palmu]

My data is in gSheet at the moment. I guess you mean this gheet function

QUERY(data, query, [headers])

support.google.com - QUERY - Docs editors Help which would in this case require a dynamic range as the data? I found some workarounds to get ranges dynamic in gSheet QUERY - Docs editors Help support.google.com

[Aleksi_Alkio]

The formula could be something like this… =QUERY(Tilastot!A2:Q;“select D,E,sum(H),sum(I),sum(K),sum(L),sum(N),sum§ where Q=1 group by D,E label sum(H)’’,sum(I)’’,sum(K)’’,sum(L)’’,sum(N)’’,sum§’’”;0)

[Mikko_Palmu]

@Aleksi_Alkio oh wow! thanks a lot!

[Aleksi_Alkio]

Ole hyvä vaan Translate