I have multiple tracking links in a single cell separated by space. How can I Track all the links using UPS?
The method, using the “formula map”, as explained by Ben Lee in this post will move the items in a new table.
Thanks for suggestion but I am unable to figure this out. I made one test document
Can you see if this works for you?
I added a formula to your example doc that I think will work. I’d test to make sure though.
Tracking.Split(",").FormulaMap( UPS::Track(CurrentValue).LastActivitySummary )
I also added a comma to split your two tracking numbers. This formula splits the numbers into a list so each one can be evaluated individually. Then we put them through a
FormulaMap() so we can process each one the same way.
CurrentValue refers to the number in the list being processed.
Thanks you so much. Let me apply this to other document and get back to you.
Its working for me
But I am not able to update the status column using switch case.
I want few conditions to filter data. Ex - if its not shipped then I want to show processing, Same way I want to show shipped for “In Transit” and Completed for “Delivered” and “Shipment has been delivered to consignee”
It’s getting complicated using if case and at the same time I want to add check for payment that like if it’s COD then upon completion I want to send reminder.
Doing this with multiple entries per row will make each step a good bit more complicated. If you can, I would keep each row as a single tracking number. If these are tied to orders, then I would use the order table as a lookup and assign each row that applies to that order.
If you need to keep multiple entries per row, can you say more about what you need overall from this? If 2 items are delivered and one is in transit, do you just need to know that it’s incomplete or do you need a status for each here? Since it’s multiple entries, each step will require filtering or comparing multiple values.
If there are multiple entires then even one is showing delivered then consider it as completed. Here is the priority order Delivered then shipped and then processing. So, If highest priority is achieved I will go for that status.
Here’s a formula that will check the list of UPS statuses and show a correlating status that you choose. It’s a
SwitchIf() formula and order matters. I would go from top to bottom like you mentioned, if anything is delivered, then all count as delivered.
The last line in the
SwitchIf() can be a catch-all. “If nothing else matches, then this”.
You can also create a lookup table of statuses and use row values in the formula too. This can be helpful if you want to pull stats on how many shipments are delivered or shipped, etc.
SwitchIf( [UPS Status].Contains("Delivered"),"Delivered", [UPS Status].Contains("In Transit"),"Shipped", "Processing" )
I added another column that uses a lookup to a “Status Table”. This way you can pull in totals for each status if you want.
It’s working. Thank you so much for your help.