Notification workflow advice

[rgreen]

Hi,
Would love some advice on how to set up a notification workflow. Don't think the stock notification plugin will work - although I might be wrong. Here is my situation:
I have products that list online stores that the product is available. If their are no stores for a product. a user sees the message "No stores are available. click here to be notified" and the user is added to the "queue table" below. We've built that part. It's the notification we need to build. We need to notify users on the "Queue" table (this holds the users waiting to be notified) via email when one of the products they have signed up is added to the the "productStores" table.

I have a product table, a store table, a user table, list of stores carrying product and a "Queue" table

product
id, productName
1, table
2, chair
3, towel

store
id, storeName
1, target
2, walmart
3, Joes

user
id, name
1, hugh
2, scott
3, jim

productStores
product__id, store__id
1,3
1,2
2,2

queue
product__id, user__id
3,2
3,3

(so scott and jim are waiting for any store to carry a towel and wants to be emailed when any store comes online)

so if the state of the productStores became
1,3
1,2
2,2
3,1

scott, and jim would get emails...

I'm just hoping that someone will point us in the right direction...

Thanks

rg

 

[troester]

Should the notification happen immediatly after a product is added?
Add an email form plugin to the form which is adding to the productsStore table.

In Conditions (Option tab) check, if somebody is waiting for this product
In Email to (eval) get the ones who are waiting.

Or add a php form plugin doing the checking and emailing yourself.

If you only want to send the notification e.g. once a day you can create a Fabrik scheduled task with a php plugin doing checking and emailing there.

 

[rgreen]

The productStores table is being updated outside of Fabrik and will be updated nightly (with the goal of a nightly email to queue users). So I suppose we will should go the route of using the Fabrik scheduled task with a php plugin doing checking and emailing.

Thanks!

 

[cheesegrits]

Note that you don't have to select a list in the PHP plugin. I don't think that's entirely clear in the tooltips. Usually you'd select a list, and we would load the data up for you. But in this case I think you just need to do an entirely custom query, to get the set of 'queue' rows which match. Shouldn't be too hard to build the right query.

Obviously you'll also need to either remove the queue rows once they have been matched, or update a 'status' field.

There should be enough code in existing plugins to "borrow" for handling the emailing.

-- hugh