I am brainstorming some ideas to make some things at work a bit easier. One thing that happens every day is we get emails from customers asking "Which of my parts are ready?". We have a locally hosted SQL database that tells us this info, and we also store every contact that we work with (about 500) in that same db.
I know we could build a secure way for customers to access the SQL db (create a hosted db, assign user IDs, pwd's, ensure firewall is solid, etc.), but I thought a simpler way might be to create a flow like that shown below. This is NOT working right now. It's more like me scribbling on a whiteboard. Before I do anything, I wanted to get some feedback from the community to see if there is a giant red flag or two that I am missing. I am not looking for help in configuring the actual nodes or debugging / troubleshooting, because I have not built anything yet.
General description is as follows:
- We would set up a dedicated email account (something like "status@blah.com")
- The incoming emails would have to contain a subject line that follows a specific format, e.g. Which Parts Are Ready? If the email subject did not contain this exact phrase, then it would be rejected.
- If it was accepted, then a SQL query would run which sends the sender's email (e.g. robert@abc.com) to SQL and compares against the 500 or so contacts that exist in the database. If it matches, then it returns a 1. If there is no match, then it returns a 0.
- If it returns a 1 (i.e. the contact is valid), then another SQL query would run that returns all the parts that are ready for that customer which the contact belongs to
- The contents of the above SQL query would be sent via an email to the sender's email address.
The flow would look something like this: