Skip to main content

Hi!


I’m trying to make an automation that will notify me when an entered record shares a field with other entries within a certain time. Essentially, we track issue tags and want to know if there have been 3 issue tags in the same state within a two week period (the state and receipt date are tracked as fields on the issue tag).

My thought is to make a formula that counts how many entries within the past two weeks share a state with the given entry, and make that the condition for the automation.

My question is, what formula (or group of formulas) would I use to look at other entries? I can’t seem to find formulas that look anywhere except inside entry that the formula outputs on.

If I can’t do it with formulas, is there an app that will help me?


Thanks

Formulas can’t look across rows as they can with a spreadsheet. As you noticed, they can only reference fields within the row. You can accomplish what you are looking to do with scripting/automations, however. I tend to lean toward writing scripts in most cases due to a number of reasons (including my background as a developer), but that’s where I would start. You may find that the built in automation actions are limited in what they can do. In that case, scripting (either via automation or Scripting app depending on your use case) would be the way to go.

Formulas can’t look across rows as they can with a spreadsheet. As you noticed, they can only reference fields within the row. You can accomplish what you are looking to do with scripting/automations, however. I tend to lean toward writing scripts in most cases due to a number of reasons (including my background as a developer), but that’s where I would start. You may find that the built in automation actions are limited in what they can do. In that case, scripting (either via automation or Scripting app depending on your use case) would be the way to go.


Thanks a bunch! I was really hoping that formulas would be able to do it since I haven’t written any scripts, but thanks for putting me on the right path

Hey Christian,


You might be able to accomplish this with the VLOOKUP function in our On2Air Actions app.






You can try it out and see if it fits your needs > On2Air.com

Reply


Terms & Conditions