Need help to find formula for lookup

[Kendy511]

Well, Here’s the input. I have 2 table
Table 1: Clients orders
Table 2: Stocking Table. It has {Import Date} field.

The question 's for {Sold out (days)} field
i wanna calculate the period of a Package sold by the last day sell out (which lookup in table 1) minimus {Import Date}. So which formulas should i use in this situation ?

Hope your guide can help!

[Mohamed_Swella1]

Hi @Kendy511,

There is no formula for Lookup, there is a field type though. What you need actually is a Rollup field to use Max() on the dates. Here is how you achieve that:

1. The Item should be linked to the Client table, this will then create a new field in the Stocking table with the Client primary field. (Cant see the Client table so not sure what data will come here)

2. Using a Rollup Field, you can use the Max() argument on the Date field, this will bring the last date this item was ordered

3. You can then use this number in a formula field, something like DateTime_Diff({Import Date}, {Rollup Field}, 'days')

Let me know if this works for you or if you have any questions.

BR,
Mo

[Kendy511]

Hi @Mohamed_Swellam thanks for your support

The issue from Rollup field, in my case i’m not sure max (value) is my option. I wanna to choose the Date (from Orders table) which the final purchased of this product for this package (which means 1 product can have a lot of package with different timeline) and the Quantity available at that time = 0 product/ package 1. On another say, I wanna lookup the final purchased out stock date, not the closest date
So then i can use Datetime_diff ({Import Date}, { Date of final purchased this product of package 1}, ‘days’)
Can we have another suggest ?

[Mohamed_Swella1]

Im a bit lost, maybe if you share screenshots of both tables and explain the use case I can help you better.

What I can think of is that each product has to be unique to a package to be able to track it that way. How are you currently linking them?

[Kendy511]

@Mohamed_Swellam
I 'm trying to create a quick base with 2 table without modify any fields in here that you can imaging:
Table 1- Batch:
Table 2- Orders:

You can see Orders tables, when {Quantity available} = 0 for Batch 1 of product A , I wanna to get that Date Orders Values

So now in Batch Tables, i wanna to calculate period out stock of each batch by {Using Final Date Orders above } - {Import Date of that batch}

[Mohamed_Swella1]

That makes it much clearer :slightly_smiling_face:

Well, you can make something else, make a field in Orders and call it Date Sold Out On. It should be a Formula field with the formula being
IF(Arrayjoin({Quantity Availability})=0, {Date Orders}, Blank())

This will bring the date when the item is sold out. Then, you can use this in a Lookup Field in the Batch table.

Does that solve your problem?

[Kendy511]

Now another issues, This’s when i mannually type the quantity available

But when i come to formula table, the values 25 didn’t lock, it link and change to 0

So when i create insert a new field like your advice, it feels not right. How can i lock the values at that time ?

[Mohamed_Swella1]

What do you mean lock values? And why are you linking this value to anything?

First of all, the field type is wrong, it is a text field when it should be a number field.

Second, we dont need to touch this field (other than making it a number instead of text), we need to create a new field and make it a formula

[Kendy511]

Here’s the real one.
Table Batch: with product: SP89310011 - Quantity import: 34 products on 01/06/2020

And here’s Order table:

As you see, the first clients only buy 1 products, so quantity in stock must be 33 (but you see 0), the 6th clients buy 3 products, so it should be display = 30 (The lock values i means 's here, just display the current values not subtract all like this) instead of 0 as you see . In Quantity available field, it linked and final quantity avaible changed. So i got 4 values from {Date Sold out} when i use your formula

[Mohamed_Swella1]

Ah ok, I thought you are entering the numbers manually for some reason, I didnt know you are doing rollups and looks ups for the quantity.

You can do what you want in other ways.

1. You can use zapier to update the current qty (would be a bit complicated to explain in details here but it can be done)

2. You can use the Script Block as shown in this post