Skip to main content

Preserve Original Lookup Value

C
  • cwc
  • New Participant
  • 1 reply

Hi all,

Airtable is turning out to be really awesome but there is literally just one issue stopping me from moving over from FileMaker. Is there a way to preserve the original lookup value even after the source data changes?

For example, I may have a product for sale in which its price may change over time but obviously I need the value to be historically accurate for every sale done…

Thanks!

    3 replies

    J

    What’s needed here (as in many other parts of Airtable) is the ability to create some business logic. This use case is very common but is not really an example of where a lookup field is the right choice because the value should be the price when the product was selected not the current price whenever the record is viewed.

    There is a way to achieve this at the moment (with no coding) and that is to use Zapier to look up the price of the product and then update the price in the order line table. However, this is pretty clunky at the moment given that a)the zap will not run immediately, b)the zap will only run once pre order line so if the product is changed the price wouldn’t and c)the Price field updated from zapper would be updatable by users which may not be very safe (it would be better to have a discount field).

    I believe it would be possible to update the zapier integration to use instant (webhook based) zaps - and I think this also gets round the problem of them only running once for any given record as they don’t de-duplicate. The only remaining issue would be to be able to make fields read only (by user role ideally).

    The combination of Zapier and Airtable would be pretty awesome if you were able to implement instant zaps in my view.

    C
    • cwc
    • Author
    • New Participant
    • 1 reply
    • January 18, 2017
    Julian_Kirkness wrote:

    What’s needed here (as in many other parts of Airtable) is the ability to create some business logic. This use case is very common but is not really an example of where a lookup field is the right choice because the value should be the price when the product was selected not the current price whenever the record is viewed.

    There is a way to achieve this at the moment (with no coding) and that is to use Zapier to look up the price of the product and then update the price in the order line table. However, this is pretty clunky at the moment given that a)the zap will not run immediately, b)the zap will only run once pre order line so if the product is changed the price wouldn’t and c)the Price field updated from zapper would be updatable by users which may not be very safe (it would be better to have a discount field).

    I believe it would be possible to update the zapier integration to use instant (webhook based) zaps - and I think this also gets round the problem of them only running once for any given record as they don’t de-duplicate. The only remaining issue would be to be able to make fields read only (by user role ideally).

    The combination of Zapier and Airtable would be pretty awesome if you were able to implement instant zaps in my view.


    Hi Julian,

    Zapier sounds like a really useful service but yes it’s a pretty clunky way for my use case right now as having historically accurate lookup values is a core part of the database I am trying to build so I’d rather it not depend on another service outside of Airtable.

    Is there a way to copy the lookup value only to another column on the same table during record creation? In any case though that is still not the ideal way to go about it. Shame, I thought I could finally leave FileMaker and move on to Airtable, which is quite frankly delightful in so many ways.

    Thanks for taking the time and effort to reply, Julian. Good day.

    J
    cwc wrote:

    Hi Julian,

    Zapier sounds like a really useful service but yes it’s a pretty clunky way for my use case right now as having historically accurate lookup values is a core part of the database I am trying to build so I’d rather it not depend on another service outside of Airtable.

    Is there a way to copy the lookup value only to another column on the same table during record creation? In any case though that is still not the ideal way to go about it. Shame, I thought I could finally leave FileMaker and move on to Airtable, which is quite frankly delightful in so many ways.

    Thanks for taking the time and effort to reply, Julian. Good day.


    Unfortunately, there isn’t a way to copy the lookup value to another column in the way you would want (i.e. only once when the product is entered). For example, if you used a formula field it would also update if the product price changed in the product table.

    This kind of thing is such a common use case for a database - maybe Airtable could comment here?

    C
    1 person likes this

    Reply


    Most helpful members this week

    Terms & Conditions