Skip to main content

How to change a dependent column to be independent by using formula

  • December 5, 2022
  • 3 replies
  • 69 views
T
Forum|alt.badge.img+3

Dear All,

I have come across a dilemma and see if anyone of you could give me a hand. Is it possible to transform a lookup value, which is dependent, to an independent value in the same table?

Like in the above diagram, Main (from Set) is a lookup value, which it changes everyday. Since I am using this table to record the transaction, I would like to see if there is any way that I don't need to cut those records and paste it to Main Dish column manually.

Or is there any script that can help making this happen? For example, on a daily basis, it would carry out a schedule job by copying one column to another one?

Appreciate your kind advice and comment.🙏

    3 replies

    A
    Forum|alt.badge.img+17
    • Andy_Lin1
    • Inspiring
    • December 5, 2022

    An automation would be the easiest solution (and you can set it to trigger once a day or anytime the "Main (from Set)" field updates in a record, but it does require you to have a Pro plan.

    You can manually set up an extension or script to perform a similar function, but since these need to be manually triggered, you're not really saving any time compared to doing it manually. You can make the process more streamlined by setting up a view where you have the filter: {Main Dish} is empty. (And optionally, if you have a last modified timestamp field {Last Modified Timestamp} is after "yesterday.") And then just copy the {Main (from Set)} to {Main Dish} by click on the tops of the fields/columns. This way, it doesn't interfere with your other views, and you can be sure to only update the records you want.

    There is a more labour-intensive option, but could offer a permanent solution without the need for automations. If your set menu changes every day, but draws from a pool of possible dishes (instead of being completely new every day), then you could set up two tables* (this could also be a single table with a few drop-down fields):

    Table: [Daily Set Menu]

    Fields: Set 1 (link to other table, Dishes; or drop-down), Set 2 (same thing) ... Set 8

    Records: Date

    So a table set up like this would have a record for each day, and would have the daily specials from that day. I'm not sure what you're currently linking to your transaction records, but you could link them to the [Daily Set Menu] table as well. Here's where the labour-intensive part comes in: since Airtable doesn't allow you to dynamically select a field from the lookup record, you have to add lookup fields to the transactions table. Specifically, you'd have to create a separate lookup field for each set menu item, and then use a formula field to select the one you want. Once it's set up, the process is invisible to you, but requires a little bit of legwork in the beginning.

    Hopefully that isn't too confusing! It would be easier to make recommendations if you provide more information about the structure of your table.

    Side note: I love beef brisket and tendon rice noodle, haha

      T
      Forum|alt.badge.img+3
      • twmeric
      • Author
      • New Participant
      • December 5, 2022

      An automation would be the easiest solution (and you can set it to trigger once a day or anytime the "Main (from Set)" field updates in a record, but it does require you to have a Pro plan.

      You can manually set up an extension or script to perform a similar function, but since these need to be manually triggered, you're not really saving any time compared to doing it manually. You can make the process more streamlined by setting up a view where you have the filter: {Main Dish} is empty. (And optionally, if you have a last modified timestamp field {Last Modified Timestamp} is after "yesterday.") And then just copy the {Main (from Set)} to {Main Dish} by click on the tops of the fields/columns. This way, it doesn't interfere with your other views, and you can be sure to only update the records you want.

      There is a more labour-intensive option, but could offer a permanent solution without the need for automations. If your set menu changes every day, but draws from a pool of possible dishes (instead of being completely new every day), then you could set up two tables* (this could also be a single table with a few drop-down fields):

      Table: [Daily Set Menu]

      Fields: Set 1 (link to other table, Dishes; or drop-down), Set 2 (same thing) ... Set 8

      Records: Date

      So a table set up like this would have a record for each day, and would have the daily specials from that day. I'm not sure what you're currently linking to your transaction records, but you could link them to the [Daily Set Menu] table as well. Here's where the labour-intensive part comes in: since Airtable doesn't allow you to dynamically select a field from the lookup record, you have to add lookup fields to the transactions table. Specifically, you'd have to create a separate lookup field for each set menu item, and then use a formula field to select the one you want. Once it's set up, the process is invisible to you, but requires a little bit of legwork in the beginning.

      Hopefully that isn't too confusing! It would be easier to make recommendations if you provide more information about the structure of your table.

      Side note: I love beef brisket and tendon rice noodle, haha


      Hi Andy,

      Appreciate your explanation and this help me to have a broader scope to see how to resolve this issue. Automation is good, but each run counts, therefore, a simple cut and paste before sleep may serve the purpose with the lowest cost.

      In fact, I am developing an APPs and using Softr to build it. I am using the the following formula to trigger the change of menu automatically on a daily basis:

      DATETIME_FORMAT(
      SET_TIMEZONE(
      NOW(), 'Asia/Hong_Kong'),
      'ddd'
      )
      The table itself took me half a day to figure out how to organize it in order to make the whole mechanism works.
      Feel free to give a try on the MVP and do feel free to give me further comment or advice.
      It is fully functional now but whatsapp functionality is restricted to HK mobile No. Hopefully I can launch after X'mas. This is a very niche scenario - for lunch hour rush and restaurants can't afford to use foodpanda or other platform is my target. They can use it for FREE and I get data in return.
        A
        Forum|alt.badge.img+17
        • Andy_Lin1
        • Inspiring
        • December 5, 2022

        Hi Andy,

        Appreciate your explanation and this help me to have a broader scope to see how to resolve this issue. Automation is good, but each run counts, therefore, a simple cut and paste before sleep may serve the purpose with the lowest cost.

        In fact, I am developing an APPs and using Softr to build it. I am using the the following formula to trigger the change of menu automatically on a daily basis:

        DATETIME_FORMAT(
        SET_TIMEZONE(
        NOW(), 'Asia/Hong_Kong'),
        'ddd'
        )
        The table itself took me half a day to figure out how to organize it in order to make the whole mechanism works.
        Feel free to give a try on the MVP and do feel free to give me further comment or advice.
        It is fully functional now but whatsapp functionality is restricted to HK mobile No. Hopefully I can launch after X'mas. This is a very niche scenario - for lunch hour rush and restaurants can't afford to use foodpanda or other platform is my target. They can use it for FREE and I get data in return.

        Wow, that's a pretty slick interface – makes me wish I was having lunch in HK! Since you'll be dealing with a number of clients, it sounds like it's worth the effort to set up a system that doesn't require daily manual input from you. I haven't used Softr before, so I don't really know if there are any best practices to working with it, but perhaps one of the other community members can help you there.

        Terms & ConditionsAccessibility statement