Skip to main content

I created this post a couple of years ago and we're still hitting a wall trying to figure out the best way to accomplish this: Dynamic formula to change output based on username viewing the table

I have started thinking about using an external no-code app interface designer to create a simple portal, where someone can login, search all of our PRODUCTS table (from an Airtable view) and get a custom URL with their Affiliate ID injected within the formula url, like so...

https://website.com/cart/?ref=AFFILIATE_NAME&add-to-cart=PRODUCT_ID&quantity=1

Note, I have another AFFILIATES table that identifies partners by their phone, email, and Affiliate ID.

If this is too hard, what about an option where the user enters their affiliate ID, and the app, spits out a formula, like above but the app (SOFTR, Glide, etc.) takes an entry field where they enter their ID, and then provides a custom link for all products, using that ID they enter.

What's the best way to accomplish this, so that each affiliate is seeing a different, custom/dynamic product link, based on their affiliate code?

---

The current Airtable formula to create dynamic URL is:

IF(PRODUCT_ID=BLANK(), BLANK(),"https://pickleballist.com/cart/?ref=" & {Affiliate_ID} & "&add-to-cart=" &PRODUCT_ID & "&quantity=1")

 

If each affiliate has their own login, perhaps you could just create the formula using a custom extension that retrieves the username from the session, and displays the link for each record clicked on in the grid:

import {useSession} from '@airtable/blocks/ui';

function getLink(productId) {
   const session = useSession()
   const affiliateId = getAffiliateIdFromUserId(session.currentUser.email)
   const link = "https://pickleballist.com/cart/?ref=" + affiliateId + "&add-to-cart=" + productId + "&quantity=1"
   return <a href={link}>Link</a>
}

The code needs finishing but just illustrates the point. You could expand this a bit more to get it to print out a list from some or all of the records in a table/view rather than just the selected record.

If each affiliate has their own login, perhaps you could just create the formula using a custom extension that retrieves the username from the session, and displays the link for each record clicked on in the grid:

import {useSession} from '@airtable/blocks/ui';

function getLink(productId) {
   const session = useSession()
   const affiliateId = getAffiliateIdFromUserId(session.currentUser.email)
   const link = "https://pickleballist.com/cart/?ref=" + affiliateId + "&add-to-cart=" + productId + "&quantity=1"
   return <a href={link}>Link</a>
}

The code needs finishing but just illustrates the point. You could expand this a bit more to get it to print out a list from some or all of the records in a table/view rather than just the selected record.


This seems like a great solution but not something I know how to implement, out of the box. 

We do not want to pay for user accounts for each of our affiliates, and we don't want them to see any of the rest of the bases, so I created a direct/live/dynamic link to the main table, where I was letting my affiliates go. Here's a public link, for reference: https://airtable.com/shrRJAk7TcpJjgDxT/tblBFA9wOyJc5r9UN

@Steve_Haysom I can give you access to the base if that might help you help us with this solution. just shoot me a DM with your email, if that's possible here.

I think the ideal scenario would be that each affiliate signs up for a free airtable account and gets the app. I give them access to a base like that (either grid view or gallery) and they can use the search 🔍 button to find a particular product... then they see the affiliate URL, and it has their ID baked into it, so they can copy and forward/text it to one of their referrals. 

I also have the Affiliate partner database that connects to the main base. If we could use your extension to compare the email of the logged in user and query the "Partners" table for the same email, and grab the user ID, to inject into any of the product URLs on the main Inventory page, that would be ideal!

Reply


Terms & Conditions