I have a table with my users in it. It is pulling from Firebase Auth. I'd like to figure out how to modify what I see in the columns for this example and others down the track.
The columns that automatically populated were Email, UID, metadata, tokensvalid, provider data.
Within the meta data there is some useful information I'd like to sort/filter by.
For example. the metadata contains
"lastSignIn" and "CreationTime" in a array. I'd like to be able to sort by creation time so I can see who the newest account is.
If there is another way to do this or anything else I'm all ears and I'm sorry if this has been addressed before. I looked around but am unsure what to search for to find similar issues.
"metadata": {
"lastSignInTime": "Mon, 07 Nov 2022 22:43:32 GMT",
"creationTime": "Thu, 03 Nov 2022 23:59:14 GMT"
},
Ideally, I'd like a column which just says 'Last sign in time' with just that part of the information and another column that says 'Creation time' with just that part.
That way I can quickly see who was the last person to create an account and, for example, quickly filter to see who was active/last signed in today etc etc.
Learning to do this too will help me create a table later on showing me things like '5 active users who signed in today' and things like '9 new users this week' or what ever.
@arkonis That doesn't seem to be formatted correctly...
Can you send the entire payload - if you have to, simply replace sensitive info with test data if need be....
I only ask because maybe I am not seeing the overall structure of the object.... using what you sent would require me to change it and not allow me to get to the correct solution.
@ScottR no worries, though I'm not really certain what you mean by the entire payload or where to find it. Do you mean like a full user bit? I'll use my own entry and obfuscate anything sensitive.
@ScottR but basically I want to display something I can filter by or sort by later on from the last signed in and creation time.
For example, have a column that says 'last signed in' and have 07 Nov 2022 23:00:14 or something like that.
I'm not sure if there will be issues with sorting later when it's formatted as "Mon, 07 Nov 2022 23:00:14 GMT" rather than a epoch time or something but afterwards I'll want to make a table to show me:
How many new users per unit of time, e.g. '7 new users this week'
Show me anon users who haven't signed in within the last week.
I'm having some success creating a transformer with the following in it but I haven't managed to jig it to display just that information but I feel I'm close.
As a result I've made it a date and this is my new column. Hope this works for others.
Now if you see the screenshots below, they are all the same date. I realised after posting that the 'selectedrow' means the selected row will show in each row so I'm looking at how to tweak it so that it's either a transformer that is put into the value of the column or replace 'selectedrow' with something else.
Thank you so much Bradly, you're a saviour. I don't know if it didn't come up in the ^space list or if I just missed it but I feel I went through the list of available items and didn't see it. Thank you!!!
Maybe you can help me similarly with my next issue. I did take a look again and I'm not sure if I'm just not practiced enough to know the search terms yet because I'm certain others are looking for similar things. Anyway, continuing on with the table:
The next column, I'd like to show the number of documents that user has under their account.
I have a seperate table which shows the selected user's document list and a count of how many documents they have (Screenshot 1 below)
To do this, I used a query which uses the following firebase query
users/{{ userTable.selectedRow.data.uid }}/blocks
Then to get the count I used from the table ('DocTable')
{{ DocTable.displayedData.length-1 }}
I also got the selected user's doc length into the first user table by creating a transformer ('docLen') with the following:
return {{ document.data.length-1 }}
When I put this in the first table's custom column however, it's showing me the currently selected user's doc length only rather than the currentRow. So a similar issue as before but I played around with placing the currentRow in a few places and it's unhappy with where ever I'm placing it.
I had a play around with using currentRow in a each place and also just not using a transformer and just putting it into the value of the column itself but I seem not to understand the nuances between the previous fix and this one.
Will start a new topic for this issue too whether or not a solution is found here for others with similar issues.
Its all about context, what programmers call scope - what the current code you are running knows at that time, or point in the code. A VERY important concept in programming anything, and the more the dev environment tries to simplify things for you (as with a Low Code platform like Retool) the more opaque it can become.
When you are putting code into the Value field of a table column, the scope is the current cell. You can then get the value in that cell (self), other values in that row (curentRow) or column (currentColumn) or other publicly available Component properties.
A transformer only has a very generic scope - the retool App itself. It has no idea of row and column within the table, only what is can see which is selectedRow. That of course is not very useful for populating an entire column of data.
And since you cannot get the count of the subtable within the scope of a table cell, you are kinda stuck.
So how do you get the count into the cell's scope? By linking the two tables together, probably using a JSON SQL Query. This is probably not exactly right, but should get you started:
Select userTable.*, dt.docLen
from userTable as ut
join DocTable as dt on ut.uid=dt.uid
Then use that as the source for your table and you now have a column with the doc count.
Yes to this! I almost wish I knew how to do this fully in code as I think it would be far more transparent and a lot of other documentation resources would be easier to follow. Also understanding how things work in theory but not in practice is a bit frustrating because you can feel how it's meant to go but not what or where.
I had hoped to find a list of those things but couldn't find the words to search either (things like currentRow) so that when it didn't come up in the suggestions, I could find what sounded right.
Your explanation of scope is very helpful and will reduce my trial and error by punching in everything I know so far into each field to see if it works. And makes clear why my attempts were not working. I think I had read about scope previously but now it makes sense with a gui representation of it not working to go along with it :p. I think I better understand what a transformer is and what it's limitations are too. Tyvm
I felt that because the two tables are seperate there would be some way to connect them but also firebase has got some sort of linking between docs and users via the UID so I thought maybe something in there but that's where my newly found experience with this stuff ended.
I'm working on the SQL script you have send me but so far no dice. I'm reading up on SQL join etc too so hopefully this will make something clearer.
Thank you so much, will reply again if i feel I'm close but something isn't working.
And if it's not too impudent of me to ask, could you break down the statement you wrote above? I can see the similarities to other examples but not what some things mean.
? - Select is self explanatory and I found examples that show it goes by putting the tablename.columntitle, tablename2.columntitle but what does the * mean? Is it like the regEx 'zero or more'?
? - when there is an 'as' does that sort of work like a variable so it's just easier to write later? Does it need to be in an order or can it just go anywhere? I assume this means that throughout this document, it will count any instance of dt as 'DocTable' (within the scope of this query). Right?
currently experimenting with making queries as 'query json with sql' and 'managed db -> sql query' though I think the prior is the correct one. Currently getting "Table does not exists: userTable" but I'm going to keep playing.
means return all fields. Otherwise you can specify just which ones you want.
As creates an alias for the table. This is required when you are doing joins. You can actually skip the as in most cases but I prefer keeping it there for readability.
Sorry, one very important thing about those JSON SQL queries that I left out here, is you need to use handlebars (aka mustaches, aka curly braces) around the Retool variables just like you have to in transformers. So you query would actually be:
Select ut.*, dt.docLen
from {{userTable.data}} as ut
join {{DocTable.data}} as dt on ut.uid=dt.uid
Here are the docs explaining the this type of query and how is differs from standard SQL:
