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:
-
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)
-
Using a Rollup Field, you can use the Max() argument on the Date field, this will bring the last date this item was ordered
-
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
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:
-
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)
-
Using a Rollup Field, you can use the Max() argument on the Date field, this will bring the last date this item was ordered
-
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
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 ?
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 ?
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?
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?
@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_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}

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?
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?
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 ?
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 ?
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
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
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
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
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.
-
You can use zapier to update the current qty (would be a bit complicated to explain in details here but it can be done)
-
You can use the Script Block as shown in this post
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.
-
You can use zapier to update the current qty (would be a bit complicated to explain in details here but it can be done)
-
You can use the Script Block as shown in this post
Wow, seem complicated script. I think i must seem times to read and learn how to figure out this, not knowing anything about code or scripts .
I’m really appreciate your help. Thank you so much!
Wow, seem complicated script. I think i must seem times to read and learn how to figure out this, not knowing anything about code or scripts .
I’m really appreciate your help. Thank you so much!
You are welcome.
Maybe using Zapier would be easier then.