Forum Discussion

JBGoriou's avatar
Qrew Member
3 years ago

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!


1 Reply

  • I think you should consider a different design, assuming I understand your problem. The real problem is that when you create a product offer you're only allowing the user to select one product. Why don't you make a many to many join table so that one product offer has many offer products and one product master has what many offer products.  So that way you could have multiple products on the same offer.

    This is what the relationship would look like.

    Offer < Offer Products > Product Master

    Mark Shnier (YQC)