How to find and calculate certain strings?

[Bryan_Delmee]

Hi Airtable community,

Currently i am working on setting up a referral program. I have attached an images to explain what i am looking for. I hope someone could help me out, as i am quite new to airtable. I have been experimenting with the FIND() formula, yet i could not figure it out.

The “gebruikt download url” tells me from which url a subscriber of the Ebook has downloaded it. This can be the basis url, as is the first record. In the second column, you will find a generated referral url. This url is meant to be shared, in order to get some price.

In the second record, the column “gebruikte download url” shows that the ebook has been downloaded through the generated referral link from the 1e record, as both of them contain the same =ref"" code.

What i would like to do in the last column “Aantal referral” is to count how many times a certain referral url has been used. The before last column shows the referral code of the subscriber. I’d like to built a formula, which looks for that code in the “gebruikte download url” and counts that up in the last column. This way a can publish this view to generate a leaderbord

Does someone know what formula would work?

Best!

[Justin_Barrett]

Unfortunately a formula won’t work for this. Formulas only operate at the record level. When a formula is evaluated for a given record, the referenced cell values are always those from the same record. A formula can’t directly query the values of a cell from other records or tables.

That said, it’s still possible to get what you want, but it will require some tweaking to your base design. Because these referral URLs are effectively unique entities, and you want to track certain statistics about them, it would make more sense to build a separate table to contain them. I’ll call this [Referral URLs] for now.

You haven’t described the process for generating new referral codes, so this will all be described somewhat generically, but when a new referral code and URL is generated for a given user, it should be added as a new record in that table, and then your {Eigen Referral URL} field should link to that record.

When a download record is added that contains a referral code, it could trigger an automation to run. The automation would need to execute a script that would search for that referral URL in the [Referral URLs] table and link to the appropriate record if found.

You could then use either a count or rollup field to count the number of links that each referral URL has, turning that table—or at least one view in that table, depending on what else you might want to do with that data—into a leaderboard showing how well each referral URL is performing. If you want to see that number back in the main table, a lookup field would do the job.

[Bryan_Delmee]

Hi Justin,

Thanks! The automation is now working as i had in mind!

Bryan

[Justin_Barrett]

Glad to know that you got the answer you were seeking! If you would, please mark my comment (the one above, not this one) as the solution to your question. This helps others who may be searching with similar questions. Thanks!