Re: When I use Max(Table[Date]), It returns the m....

Johann_Harshman · 08-30-2018 05:00 PM

When I use Max(Table[Date]), It returns the most recent date correctly…

but I want to pull the date for a project associated with a specific employee with (Maxrow(table, date, useremail() = employee email) + a ref column - as shown in the given appsheet example code.

When I use Maxrow(Table, Date) It says it cannot be parsed due to #VALUE!. What does this mean and how can I fix it?

tony1

@Johann_Harshman Can you try MAXROW(“Your Table Name”, “Your Date column”)

leahcarn

I’m trying to do a similar thing, I have MAX(SELECT(Hires[Date], “Borrower” = “Full Name”)) but, although it all looks correct, it just doesn’t return a date where it should. All my table/column names are correct, help?

Suvrutt_Gurjar

Hi,

Could you please check what the sub-expression “Borrower” = “Full Name” consisting of? Both seem to be text values. The select statement’s second argument typically needs to be either row based, thus having a column name denoted by [Column Name] in the argument or simply consisting of a TRUE or FALSE condition.

You may wish to take a look at following document on SELECT() statement construction https://help.appsheet.com/en/articles/2357314-select

leahcarn

Ok, so if I want to return a result where the “borrower” column matches the “Full name” column in the table I’m working in, how would I structure that?

Suvrutt_Gurjar

Thanks for the update. Do both the columns have same information type and column types and are those in the same table?

For example if [Full Name] consists of “John Doe” in a particular row, then the SELECT() will return the [Date] for that row, only if [Borrower] also consists “John Doe” in the same row.

The MAX statement including SELECT() statement in such a case will be something like
MAX(SELECT(Hires[Date], [Borrower] = [Full Name]))

leahcarn

So Full name is text, but Borrower is ref. Full name is in the table where I want to populate the Last active date, and Borrower is in the table where I want the date date retrieved from. I can provide screenshots if that would help clarify things?

Suvrutt_Gurjar

Yes, I believe you may wish to mention some more details such as whether the two tables are referenced to each other and which is the parent table among the two and what are the relevant columns in each table related to the expression.

You did mention borrower is a ref. Is it a reference column in the table containing full name. If borrower is reference column (then it is a key of borrower table) , then is there another column in the borrower table ( Say Borrower Name or is Borrower name itself key) to match the Full Name in the other table.

In this regard, in general, since you have mentioned reference and two related tables, I request you to take a look at following articles, just in case you have not before. If you are using related tables, then these articles are very useful.

leahcarn

Thanks, I’m completely lost and I don’t entirely understand how appsheet has connected things together, so I guess I’ve got more reading to do on those links

leahcarn

Ok, so the two tables are referenced to each other. I’m not sure which one is the parent table. “Full Name” is the key for the “Membership Database” table, and I think “Borrower” in the “Hires” table is referenced to that, but the key in the “hires” table is just the row number because each borrower can have multiple records against their name. Which gets to what I want the expression to do - pull the date of the most recent record. Hope this makes things slightly clearer

Suvrutt_Gurjar

Hi @leahcarn,
Thank you.The detailssurely help to some extent. I will still make some assumptions and request you to evaluate as follows. It sounds that “Membership Database” is your parent table , because each “Full Name” in that table could have multiple borrowings in the “Hires” table. Also as you have mentioned the two tables are referenced.

Presuming Hires is the child table, you will have a virtual column called something like “Related Hires” in your “Membership Database” table.
If this is the case , you may have a Virtual column in the “Membership Database” with an expression like MAX([Related Hires][Date])

If you still have challenge in terms of results from this expression, you may wish to post a picture of just the column with type “Ref” from Data - > table - > Column pane in AppSheet editor of the relevant table. ( Please mention the name of the table) and picture of settings pane of that Ref column. This will help community members in knowing exactly which table is referenced to what.

On a side note, you may wish to reconsider having Row_Number as a key as it is an implicit key. Please refer to the article

leahcarn

ARGH!!!
The tables are set up exactly as you described. I used the expression you suggested, and in testing, it looks great. When I go through and save it, it returns “Invalid Date”.
Where to from here?!

Suvrutt_Gurjar

Thank you for the updates. Very good to know the tables were set up as described and there is progress. I presume the LastActiveDate column is in “Membership Database”. Could you confirm the said column is of Date Type and could you please share a picture of settings pane of that column?

Also could you please mention if the date column is set up exactly in the same manner in both the tables?

You may also search the earlier posts in the community on "invalid date’issue faced before. You may wish to look at the following post. Request you to regenerate the table after making any change to a column, if not already done.

leahcarn

Thanks
I had a read of some of the other “Invalid Date” Threads, reformatted that column to a date column in the original table, and now it’s working. MM/DD/YYYY, but I can live with that if I have to.
Thanks again for all your help

Suvrutt_Gurjar

Thank you for the update. Good to know the desired functionality works. All the best.