Retrieving the latest FSU code of a particular shipment

[Hussein_Osseily]

Hello Community, 

I have a table "Local Shipments" that has a list of shipment numbers "MAWB" , 
In a seperate table "FSU Messages" it has "FSU Code" of every shipment.
I would like to lookup the latest "FSU Code" received  "arrivetime" from the "FSU Messages" table 
into the "FSU Code" column of the table "Local Shipments" 
I Tried the following but its not working for a reason, if you may help me please by poining out what i wrote wrong: 
LOOKUP([_THISROW].[MAWB], "FSU MASTER SHEET", "MAWB","type") this is working but doesn't pull the latest FSU Code

i added MAXROW, but am assuming the syntex is wrong
MAXROW(("FSU MESSAGES" , "ARRIVETIME"),
LOOKUP([_THISROW].[MAWB], "FSU MESSAGES", "MAWB","FSU CODE"))

Thanks in advance

[Suvrutt_Gurjar · Accepted solution]

I will anyway post my suggestion. There maybe better solutions/approaches.

If understanding of your requirement is correct, please

1. create a slice on the " FSU MESSAGES" table called say "Latest FSU MESSAGES"  with a row filter expression of

[FSU Messages Key column]= MAXROW("FSU MESSAGES" , "arrivetime", [MAWB]=[_THISROW].[MAWB])

2. Then the lookup expression in the [FSU Code] in the table Local Shipments could be based on the slice created in step 1

LOOKUP([_THISROW].[MAWB], "Latest FSU MESSAGES", "MAWB", "FSU Code")

In one post you have mentioned type, in another [FSU Code] to be pulled. Accordingly the LOOKUP() may need adjustment.

Also depending on if [FSU Code] is real column or VC and if you want that update in "Local Shipments" table records with every added row in  "FSU Messages" table, you may need to run an action to update real columns or VC should do without action.

[SkrOYC]

I work on logistics so this seems familiar 😁

Could you separate the tables and columns or make sure I understanded correctly?:

Table: Local Shipments
[MAWB] is a column

Table: FSU Messages
[FSU Code] is a column
[Arrivetime] is another one
[Type] is the one you want
[MAWB]

Everytime there is an add into "FSU Messages" where [Arrivetime] is something you need to pull the [Type] column into "Local Shipments" where [MAWB] matches [MAWB] on "FSU Messages"?

Please post a list of the columns you are interacting with on both tables as well as the column in which you are trying to make your expression.

You will most likely need an action and avoid most of the expression stuff or a virtual column with an expression to get the latest one

[Hussein_Osseily]

Hello @SkrOYC 

Thanks for your time,
You working in the logistics sector make it easier to understand my purpose. 
So the table "FSU Messages", captures the FSU Messages and add it as rows in this table 
MAWB, Dest, arrivetime, FSU Code... the same MAWB might have several FSU Codes in that table, but i only care about pulling the latest one into the table "Local Shipments" and update the column "FSU Code" with the latest received " FSU Code" from the "FSU Messages" Table. 

Table to pull into: Local Shipments
Column to match: MAWB
Column to pull into: FSU Code(where the expression will be) or action

Table to pull From: FSU Messages
Column to match: MAWB
Column to pull from: FSU Code

---

@SkrOYC wrote:

Everytime there is an add into "FSU Messages" where [Arrivetime] is something you need to pull the [Type] column into "Local Shipments" where [MAWB] matches [MAWB] on "FSU Messages"?
Exactly the point, whenever a new FSU Code received , i want the NEW FSUcode to be pulled and replace the old FSU Code. 

---

@SkrOYC wrote:

You will most likely need an action and avoid most of the expression stuff or a virtual column with an expression to get the latest one
Now as you mentioned it, I do agree that it must be an action and not an expression as it wouldn't auto update the column unless edited.

---

Any tips on acheiving that ? 
thanks in advance

---

[SkrOYC]

Ok, two more questions to give you a step-by-step kind of setup.

1. Is FSU Messages taking the data of the MAWB from the Local Shipments via a Ref field? This would make things easy and you should do it this way anyway if...:
2. Is MAWB a unique value in the context of the Local Shipments table so that there is just one row per MAWB? If this is the case, this will be easy

[Hussein_Osseily]

I don't have a ref field yet as am just working on integrating the SITA table into the application.
The "FSU Messages" will have the data decoded from the FSU message and the Local Shipments will have the data pulled from the FFM , so the MAWB details such as pcs, qty, cbms,... are identical in both tables.
The MAWB is a unique value in the local shipment page as well, as it is unified, grouped, and summed using BIGQUERY 

[SkrOYC]

Ok. Please tell the key column of the Local Shipments table

[Hussein_Osseily]

The key Column for now is "ID" which is UNIQUEID() expression

[Suvrutt_Gurjar]

@SkrOYC ,

May I join this post thread to suggest an approach?

[SkrOYC]

Sure @Suvrutt_Gurjar! I left for a moment because of my work

[Suvrutt_Gurjar]

Hi @SkrOYC ,

Thank you very much. 

You had pursued this thread with some good questions. So I believe  this thread first belongs to you to answer and you should conclude it first. Today my morning, I saw the thread is open , so just requested you as I also had thought of an approach on this interesting requirement.

I will join in if I have anything worthwhile to add my tomorrow morning. 

[Suvrutt_Gurjar · Accepted solution]

I will anyway post my suggestion. There maybe better solutions/approaches.

If understanding of your requirement is correct, please

1. create a slice on the " FSU MESSAGES" table called say "Latest FSU MESSAGES"  with a row filter expression of

[FSU Messages Key column]= MAXROW("FSU MESSAGES" , "arrivetime", [MAWB]=[_THISROW].[MAWB])

2. Then the lookup expression in the [FSU Code] in the table Local Shipments could be based on the slice created in step 1

LOOKUP([_THISROW].[MAWB], "Latest FSU MESSAGES", "MAWB", "FSU Code")

In one post you have mentioned type, in another [FSU Code] to be pulled. Accordingly the LOOKUP() may need adjustment.

Also depending on if [FSU Code] is real column or VC and if you want that update in "Local Shipments" table records with every added row in  "FSU Messages" table, you may need to run an action to update real columns or VC should do without action.

[Hussein_Osseily]

I would like to thank you both sirs  @Suvrutt_Gurjar @SkrOYC  for your time and attention to my question, it is really appreciated. 

@Suvrutt_Gurjar this worked very well for me thanks for your time writting your answer, it helped in solving the riddle. and a new thing that i have just learned from you is that the VC doesnt require an action to keep its values up to date. it made me rethink of the real columns i have and the posibility of switching it to VC instead. 

I am yet curious to know if @SkrOYC  would have the same approach in doing this.

Thank you and have a great day gents

[Suvrutt_Gurjar]

---

@Hussein_Osseily wrote:

 this worked very well for me thanks for your time writting your answer, it helped in solving the riddle. and a new thing that i have just learned from you is that the VC doesnt require an action to keep its values up to date. it made me rethink of the real columns i have and the posibility of switching it to VC instead. 

---

Hi @Hussein_Osseily ,

Good to know it works the way you want. However please do not take my mention of VC  as a recommendation to include VC always. On the other hand, VCs with multirow expression such as SELECT(), MAXROW(), MINROW(), FILTER(), LOOKUP() eat up lot of sync time. I just mentioned that if you are using VC, you need not have action. But multirow expression VCs should be ideally avoided. In this case, though since we are already using a slice to filter rows, I believe the VC may not be too expensive in itself. 

Edit : The following is a very informative post on sync performance and talks a great deal on VCs, that you may want to refer.

Solved: Improving performance by getting rid of unnecessar... - Google Cloud Community

My main suggestion was around the approach to get the desired FSU codes and not around using VC.

You may still use action to update the rows. But in such cases even action will need to be used judiciously because if you have multiple records to update in Shipments table with frequent FSU code status changes, those many actions will invoke from time to time.

The third option may be to use API Webhook to update records using AppSheet automation.

[Hussein_Osseily]

Thanks for sharing your knoweldge in this community, its indeed needed and very helpful. 
thats what make this community great , thanks !

[SkrOYC · Accepted solution]

Here is my to take on this.

Create an action of type "Set the values of some columns in this row" inside your "Local Shipments" table

This should set the value of [FSU Code] with the one from the "FSU Messages" table using the following expression:

ANY(
  SELECT(
    FSU MESSAGES[FSU Code],
    [ID]=
    MAXROW(
      "FSU MESSAGES",
      "_RowNumber",
      [_THISROW].[MAWB]=[MAWB]
    )
  )
)​

Create another action inside the "FSU Messages" table of type "Execute an action on a set of rows".
This action should execute the previous action (Reference Action) on the "Local Shipments" (Reference Table) and use this Referenced Rows expression:

FILTER(
  "Local Shipments",
  [_THISROW].[MAWB]=[MAWB]
)

Finally, go to your Form view for the "FSU Messages" table and select the last action you created inside the "Form Saved" option.

PS: Both actions should be configured as "Prominence: Do not display"

---

This is very similar to @Suvrutt_Gurjar's take on this, the only difference is that I'm going a little away from the usage of virtual columns and you said that the [FSU Code] on "Local Shipments" table was a real one so I considered this method.

---

About the usage of Virtual Columns and performance in general, here is a good post by @pravse that I have bookmarked.
Solved: Re: Improving performance by getting rid of unnece... - Google Cloud Community

Considering Suvrutt's solution, his Select() (I mean, LOOKUP() and MAXROW() are SELECT() under the hood) shouldn't cause too much of a problem because the dataset being filtered (an already filtered slice) is small, but I try to avoid the fact that it is going to be executed/evaluated on each sync.

My solution has a more expensive (performance wise) expression with nested SELECT() but it is only going to run once every time you add a new row into "FSU Messages" and it runs on the device, which should be poweful enough for this kind of expressions.

To summarize, neither is better, it's a matter of taste, need and understanding of the platform's features

[Hussein_Osseily]

Thank you very much for your time and assistant in this, having seeing how you would acheive this in a different method is really interesting, different methods and tastes to acheive the same result. I will try your method as well and try to understand its logic too, because it is something new to me. 

---

@SkrOYC wrote:

Solved: Re: Improving performance by getting rid of unnece... - Google Cloud Community

---

Thanks for sharing this article about the performance.

Kindest regards and thanks again for your time

[SkrOYC]

@Suvrutt_Gurjar I took the time to read your latest reply and found that you already mentioned the post where Praveen's comments are. It's the closests thing we have to understand the backbox that AppSheet's core is