Formula to Make a Field = Most Recent Value of Same Field
The table I'm building allows the user to create a product offer for an order. So in this table they create a product offer, write the details of the product and then save it and now the table shows all the details for "product A" that the user created for this product offer. I currently have it set so that the product offer# = the record ID#.
However, sometimes the product offers contain more than one product but the user can only enter one product at a time. So when the user enters product A, B, C, the table will show record ID# 1, 2, 3, and if left at that, the "product offer#" field will match that. But I need to be able to show that the product offer# is the same for all those products. So my solution was to create a check box field called "order related to last product" that the user can select while they are creating that product offer. Then I'd create a formula for the product offer# saying:
"if 'order related to last product' is not checked off, then product offer# = record ID#, else product offer# = the last/most recent product offer# created"
As a formula I got this far: IF([order related to last product]=false, [record ID#], ???)
Everything I've tried so far doesn't work because 1. I'm not sure how to write "find last/most recent product offer# created", and 2. I know the formula doesn't allow me to use a reference to itself which in this case is the product offer#.
Does anyone know how I can get around this? I would greatly appreciate the help. Thank you!
------------------------------
JB
------------------------------
However, sometimes the product offers contain more than one product but the user can only enter one product at a time. So when the user enters product A, B, C, the table will show record ID# 1, 2, 3, and if left at that, the "product offer#" field will match that. But I need to be able to show that the product offer# is the same for all those products. So my solution was to create a check box field called "order related to last product" that the user can select while they are creating that product offer. Then I'd create a formula for the product offer# saying:
"if 'order related to last product' is not checked off, then product offer# = record ID#, else product offer# = the last/most recent product offer# created"
As a formula I got this far: IF([order related to last product]=false, [record ID#], ???)
Everything I've tried so far doesn't work because 1. I'm not sure how to write "find last/most recent product offer# created", and 2. I know the formula doesn't allow me to use a reference to itself which in this case is the product offer#.
Does anyone know how I can get around this? I would greatly appreciate the help. Thank you!
------------------------------
JB
------------------------------
