Update Max date from Child referenced table

Hi,

I am newbie to Appsheet, my question might sound very simple for many of you.

I have a contact master table with following columns

  1. Company (Column type Text)
  2. Phone
  3. Call Log (Ref Table - Ref Key is Company)
  4. Follow-up date (should come from history)

When a company is selected it goes to detail page and user can call and register feed back in history along with next follow up date.

While logging follow-up date in Call log it should update in Contact Master.
Or I should be able to lookup the Log table and find next call date.

LOOKUP([_THISROW].[Company],“Call History”,“Company”,“Next Follow-up”)
Tried this but returns the first record in the matched row.

Please help
Thanks
Jayadev

Hi @Jayadevan! Welcome to the community.

I’m having trouble understanding your question. Please provide more detail (exact column names for the “Master” and “Call History” tables; example of what is going wrong; screenshots of the app; etc.).

By the way, this is a question, not a “tip” so the category will probably be changed by someone monitoring this community to “question.”

1 Like

Yes, this should be changed to the Question Category. Please update the original post. @Steve


From what I gather the Call Log (named Call History ) is used for both past calls and the next future call,at the very least.

I would recommend the above - establishing a Virtual Column in your Master table for the “Next Call Date” as a REF column and then assign it using a ANY(SELECT()) function.

The SELECT filters Call Log rows by:

  1. Company matches the Master row company
  2. Type is of “Next Call Date” (if you have such a column, otherwise omit this part)
  3. Call Date is > TODAY()

The ANY() function will return only the first item in the list. This arrangement will work for any number of future Call Back records listed - 0, 1, >1 - to return only the Next listed call back record.

NOTE: Rows should be in the order entered which is probably sorted how you need. If they are not in Date order then you may need to include an ORDERBY() function between the ANY() and SELECT() functions.

If you are not familiar with implementing the SELECT() function, trying reading the below article first and come back here with any questions.

4 Likes

Thanks a lot for your clear and point to point notes.

However, I am trying to get the last call date, now it returns the first from the matched records. Could you please help me further. The formula is below.

ANY(SELECT(Call History[Next Follow-up], ( [Company] = [_THISROW].[Company]) ORDER BY [Next Follow-up] )).

I think if order by can be Descending, it will return the last date, how to add descending order ? Or suggest me the right method.

Once again thanks a lot for your support

Hi @Jayadevan_V

You may want to have a look there:

3 Likes

ORDERBY() is in the wrong place. Move it and the follow the article suggested above by @Aurelien to understand how to change the order from ascending to descending.

3 Likes

Hi @Jayadevan_V

Also, you may want to use this expression:

If that may help, I dealt myself with this question weeks ago:

1 Like

THANKS FOR YOUR GUIDANCE. MADE THE CHANGES AS SUGGESTED, BUT SHOWING THE FOLLOWING ERROR

ORDERBY has invalid inputs

ANY(ORDERBY(SELECT(Call History[Next Follow-up], ( [Company] = [_THISROW].[Company]))))

I DON’T KNOW WHERE I AM MISTAKENING .
KINDLY HELP

Hi @Jayadevan_V

Huge capital letters, you may want to deactivate that :slight_smile:

About that:

Did you read the post indicated in this previous message ? If so, you probably found examples close to your request in this article.

About the message in the expression validator:
In general, when you have this kind of message “has invalid inputs” it will help you to go on this search engine and type the name of the function in error:

About expression analysis:
I suggest you indent your expression in order to better see where errors come.
This way, for example:

ANY(
  ORDERBY(
    SELECT(
      Call History[Next Follow-up], 
      [Company] = [_THISROW].[Company]
    )
  )
)

Tips & Tricks : For writing expression here, there is this button (add all necessary blank spaces at each row in order to get expression aligned) :
image

About YOUR expression:
Syntax expected is (when looking at the link I provided earlier):
ORDERBY( row-keys , sort-key [ , descending-order? [ , sort-key ] ]... )
You easily see from previsou indentation that at least one input is missing with the ORDERBY expression.
So, sort-key is missing.

2 Likes