Update Max date from Child referenced table

[Jayadevan_V]

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

[Kirk_Masden]

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.”

[WillowMobileSys]

Kirk_Masden:

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

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.

Jayadevan_V:

While logging follow-up date in Call log it should update in Contact Master.

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.

help.appsheet.com

SELECT()

Gather column values from row in table or slice

[Jayadevan_V]

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

[Aurelien]

Hi @Jayadevan_V

You may want to have a look there:

help.appsheet.com

ORDERBY()

Sort row references

[Aurelien]

Hi @Jayadevan_V

Jayadevan_V:

However, I am trying to get the last call date

Also, you may want to use this expression:

help.appsheet.com

TOP()

Initial list items

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

ORDERBY by date - question Questions

HI @Suvrutt_Gurjar I’m not sure I fully understand your question as English is not my first language, but here is my answer Short answer No, I used it in another table. Long answer This expression is in a “Quote” table. Because I used the FILTER, I had a list of ref from “PRIX VENTE”, that I called and ordered simultaneously in the table “Quote”. So, said differently (please apologize if I’m not clear enough 😞 Table “PRIX VENTE” contains prices: key-column…

[WillowMobileSys]

WillowMobileSystems:

ORDERBY() function between the ANY() and SELECT() functions.

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.

[Jayadevan_V]

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

[Aurelien]

Hi @Jayadevan_V

Huge capital letters, you may want to deactivate that

About that:

Jayadevan_V:

I DON’T KNOW WHERE I AM MISTAKENING .

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

Update Max date from Child referenced table Questions

Hi @Jayadevan_V You may want to have a look there:

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:

help.appsheet.com

AppSheet Help Center

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) :

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.