Custom AI integration
You can enable custom-tailored AI assistance directly on your report or kick off AI-powered custom workflows. Example, you can integrate Azure OpenAI into your report to help plan your next proposal while factoring in context from the report. You simply select the influencer and click the ‘Generate AI Suggestion’ button, which runs a Fabric User data function that instantly provides an Azure OpenAI response based on a fully customizable prompt. Here is the Python code for the user data function that powers this scenario: import fabric.functions as fn import logging import openai udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB",alias="Translytical") @udf.function() def AISuggestion(sqlDB: fn.FabricSqlConnection, company: str) -> str : logging.info('Python UDF trigger function processed a request.') # Establish a connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() #Get offer status for the company SQL_read_Command = "SELECT * FROM [dbo].[CompanyStatus] WHERE Company = ?" cursor.execute(SQL_read_Command, company) record = cursor.fetchone() customer_name = record[0] last_comment = record[10] #Submit prompt to Azure OpenAI and get an AI suggestion prompt = "Respond with a short plan that is under 240 characters: I work at Contoso Outdoors, and we collaborate with influencers by offering them offers for custom designed bikes. Pretend we want to collab with the following influencer: " + customer_name +" from company: " + company + ". Here's a comment about their latest feedback " + last_comment + "." deployment = "gpt-4o" openai_client = openai.AzureOpenAI( api_key='<API Key here>', api_version = "2025-01-01-preview", azure_endpoint = "https://sico-oai-eus2.openai.azure.com/openai/deployments/gpt-4o/chat/completions?api-version=2025-01-01-preview" ) response = openai_client.chat.completions.create( model=deployment, messages=[ {"role": "user", "content": prompt} ] ) result = response.choices[0].message.content #Check if there is an exisiting AI suggestion for the company SQL_read_Command = "SELECT * FROM [dbo].[AISuggestions] WHERE Company = ?" cursor.execute(SQL_read_Command, company) if cursor.fetchone(): #if there is an existing AI suggestion record for the company, update just the AI suggestion SQL_update_command = "UPDATE [dbo].[AISuggestions] SET [AI_suggestion] = ? WHERE [Company] = ?;" Existing_Suggestion = (result, company) cursor.execute(SQL_update_command, Existing_Suggestion) else: #if there is NOT an existing AI suggestion record for the company, add a new record SQL_insert_command = "INSERT INTO [dbo].[AISuggestions](Name, Company, AI_suggestion) VALUES(?, ?, ?);" New_Suggestion = (customer_name, company, result) cursor.execute(SQL_insert_command, New_Suggestion) # Commit the transaction connection.commit() # Close the connection cursor.close() connection.close() return f"Generated Azure OpenAI suggestion for collaboration ideas with " + customer_name + " from " + company + "." Feel free to use this code as inspiration for your own custom AI integration scenarios!Wallet Watch Analytics: Expense Tracker
Introduction: WalletWise Analytics is a smart personal expense tracking application that combines the power of Microsoft Fabric and Power BI to create an intelligent expense tracking system. What It Does: - Track Expenses in Real-time: Add your daily expenses directly from a Power BI dashboard and watch your balance update instantly - Monitor Account Balance: See your current balance on a live card that updates every time you spend money - Email Notifications: Get email alerts sent to your inbox when your balance gets dangerously low - Monthly Salary Management: Automatically adds $8,000 monthly salary to keep your account funded - Expense Control: View all your expenses in a table and visualize the spending - Write-back Functionality: Enter expense data through Power BI that immediately saves to your database Full Demo Video: Click Here Steps Used: Step 1: Database Setup - Created two tables in Fabric SQL Database: Expense table and Bank table - Added sample data to test the system Step 2: User Data Functions - Built smart functions that can add expenses, add salary, and send email alerts - Connected these functions to the database to read and write data Step 3: Power BI Dashboard - Created a user-friendly dashboard with cards showing balance and alerts - Added input controls where users can type expense details - Built a table showing all expenses Step 4: Write-back Buttons - Added "Add Expense" button that saves data to the database when clicked - Added "Add Salary" button that adds $8,000 monthly credit - Added "Send Email" button for email alerts Step 5: Smart Notifications - Set up email notifications for critical balance levels - Made the system update in real-time when changes happen Why I Came Up With This: I created this project to solve a common problem many people face: Losing track of their spending and running out of money unexpectedly. This project showcases the power of translytical flows - where analytical tools like Power BI can write data back to databases, creating a seamless experience between viewing data and updating it. UDF: Adding Expense import fabric.functions as fn from datetime import datetime udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="YOUR_CONNECTION_ALIAS") @udf.function() def write_expense(sqlDB: fn.FabricSqlConnection, expenseDate: str, description: str, amount: float, category: str) -> str: # Error handling if amount <= 0: raise fn.UserThrownError("Amount must be greater than 0", {"Amount": amount}) if len(description) > 255: raise fn.UserThrownError("Description too long (max 255 characters)", {"Description": description}) # Establish connection connection = sqlDB.connect() cursor = connection.cursor() try: # Insert into Expense table expense_query = "INSERT INTO Expense (ExpenseDate, Description, Amount, Category) VALUES (?, ?, ?, ?)" cursor.execute(expense_query, expenseDate, description, amount, category) # Insert corresponding debit into Bank table bank_query = "INSERT INTO Bank (TransactionDate, Description, Amount, TransactionType) VALUES (?, ?, ?, ?)" bank_description = f"Expense: {description}" cursor.execute(bank_query, expenseDate, bank_description, -amount, 'Debit') # Commit transaction connection.commit() return f"Expense ${amount} for '{description}' added successfully" except Exception as e: connection.rollback() raise fn.UserThrownError(f"Failed to add expense: {str(e)}") finally: cursor.close() connection.close() Add $8000 Monthly Salary @udf.connection(argName="sqlDB", alias="YOUR_CONNECTION_ALIAS") @udf.function() def add_salary(sqlDB: fn.FabricSqlConnection) -> str: connection = sqlDB.connect() cursor = connection.cursor() try: # Add fixed $8000 salary to Bank table with current date current_date = datetime.now().strftime('%Y-%m-%d') salary_query = "INSERT INTO Bank (TransactionDate, Description, Amount, TransactionType) VALUES (?, ?, ?, ?)" cursor.execute(salary_query, current_date, 'Monthly Salary', 8000.00, 'Credit') connection.commit() return "Monthly salary $8000 added successfully" except Exception as e: connection.rollback() raise fn.UserThrownError(f"Failed to add salary: {str(e)}") finally: cursor.close() connection.close() Send Email when balance drops below $100 Configuration Setup (Detailed Steps) Part A: Get SendGrid API Key Create SendGrid Account: - Go to https://sendgrid.com/ - Click "Start for Free" or "Sign Up" - Fill out registration form with your details - Verify your email address Access SendGrid Dashboard: - Log into your SendGrid account - You'll see the main dashboard Create API Key: - In the left sidebar, click "Settings" - Click "API Keys" - Click "Create API Key" button - Choose "Restricted Access" (recommended) - Give it a name like "Fabric-Expense-Tracker" - Under "Mail Send", select "Full Access" - Click "Create & View" - IMPORTANT: Copy the API key immediately (it won't be shown again) - Store it securely (you'll need this for the code) Part B: Update Your Function Code import fabric.functions as fn import logging from flask import Flask from flask_mail import Mail, Message from datetime import datetime udf = fn.UserDataFunctions() # Flask Mail Configuration app = Flask(__name__) app.config['MAIL_SERVER'] = 'smtp.sendgrid.net' app.config['MAIL_PORT'] = 587 app.config['MAIL_USERNAME'] = 'apikey' app.config['MAIL_PASSWORD'] = 'YOUR_SENDGRID_API_KEY_HERE' # Replace with your actual API key app.config['MAIL_USE_TLS'] = True app.config['MAIL_DEFAULT_SENDER'] = '[email protected]' # Replace with your email mail = Mail(app) @udf.connection(argName="sqlDB", alias="YOUR_CONNECTION_ALIAS") # Replace with your alias @udf.function() def check_balance_and_notify(sqlDB: fn.FabricSqlConnection, recipientemail: str) -> str: """Function to manually check balance and send notification if needed""" connection = sqlDB.connect() cursor = connection.cursor() try: # Get current balance balance_query = "SELECT SUM(Amount) FROM Bank" cursor.execute(balance_query) current_balance = cursor.fetchone()[0] or 0 if current_balance < 100: send_low_balance_email(current_balance, recipientemail) return f"🚨 CRITICAL: Balance is ${current_balance:.2f}. Email notification sent to {recipientemail}" else: return f"✅ Balance OK: ${current_balance:.2f}. No notification needed." except Exception as e: raise fn.UserThrownError(f"Failed to check balance: {str(e)}") finally: cursor.close() connection.close() def send_low_balance_email(balance: float, recipient_email: str): """Send low balance alert email - SIMPLIFIED VERSION""" try: current_time = datetime.now() # Simple text email instead of complex HTML email_text = f""" CRITICAL: Low Balance Alert! Your account balance has dropped below $100! Current Balance: ${balance:.2f} Date: {current_time.strftime('%Y-%m-%d')} Time: {current_time.strftime('%H:%M:%S')} Please add funds to your account immediately to avoid any issues. """ with app.app_context(): msg = Message( subject="🚨 CRITICAL: Account Balance Alert - Action Required", recipients=[recipient_email], body=email_text ) mail.send(msg) logging.info(f"Low balance email sent to {recipient_email}. Balance: ${balance:.2f}") except Exception as e: logging.error(f"Failed to send email: {str(e)}") raise fn.UserThrownError(f"Failed to send notification email: {str(e)}") Other Use Cases That Can Be Implemented: - Student Budget Manager: Track tuition, books, food, and living expenses for students - Travel Expense Tracker: Monitor trip expenses, daily spending limits, and travel budgets - Investment Portfolio Monitor: Track investments, gains, losses, and portfolio alerts - Healthcare Expense Tracker: Track medical bills, insurance claims, and health-related spendingIntelliventra – Smart Event Management Application
Intelliventra lets participants easily browse all sessions and add them to their personal agenda. Attendees can view session details such as time, room, track, and speaker. The page also allows attendees to add,remove sessions. It is designed to be simple, clear, and works for any event, from small workshops to large conferences.
Accounting Process Flow Automation by Translytical Flow
Sales Commission Process in a Marketing Company In an organization offering marketing solutions to lending institutions, the commission process for a sales order was complex, relying on shared spreadsheets with inputs and approvals from multiple departments. Implementing a live dashboard with a translytical flow streamlined the process, reducing the workflow time from 10 days to 2 days. Commissions are paid based on: # of sales appointments Other data from Zohobooks Approval from CFO Final Processing of Payment by finance/accountant With a set of dash boards powered by an SQL database with translytical functions , we made the recording of sales appointments and approvals at multiple steps and the subsequent sales commission payment kickoff and notification seamless. Direct query is used for quick reflection of the write backs (apointment times and apporval status changes). User data functions are called from power BI buttons to achieve this. Power automate flow is used to send email notifications which is invoked by a button click from dashbaord. The users were most thrilled because they could see the commission outflow immediately reflected in the Cashflow forecasts. Process Flow: Sales appointments enetered by respective sales people ->Approval from CFO ->Processing of sales commission payment by accountant and sending email notifications User Data Function used given here: import fabric.functions as fn import logging from datetime import datetime udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB1", alias="TWFDB") @udf.function() def create_appointment( sqlDB1: fn.FabricSqlConnection, fullname: str, startdate: str, starttime: str, durationminutes: int ) -> str: if sqlDB1 is None: raise fn.UserThrownError("Connection injection failed. Check alias 'TWFDB' in UDF connections.") # Validate / normalize if not fullname or fullname.strip() == "": raise fn.UserThrownError("Full name is required.") try: dur = int(durationminutes) except ValueError: raise fn.UserThrownError("Duration must be a whole number of minutes.") if dur <= 0: raise fn.UserThrownError("Duration must be > 0.") t = (starttime or "").strip() or "09:00:00" if t.count(":") == 1: t += ":00" elif t.count(":") != 2: raise fn.UserThrownError("Time must be HH:mm or HH:mm:ss.") try: start_datetime = datetime.strptime(f"{startdate} {t}", "%Y-%m-%d %H:%M:%S") except ValueError: raise fn.UserThrownError("Invalid date or time format. Expected YYYY-MM-DD and HH:mm[:ss].") created_date = datetime.now() conn = cur = None try: conn = sqlDB1.connect() conn.autocommit = False cur = conn.cursor() # Upsert salesperson cur.execute("SELECT SalespersonID FROM dbo.Salesperson WHERE FullName = ?", (fullname,)) row = cur.fetchone() if row: sp_id = int(row[0]) else: try: cur.execute( "INSERT INTO dbo.Salesperson (FullName) OUTPUT INSERTED.SalespersonID VALUES (?)", (fullname,) ) sp_id = int(cur.fetchone()[0]) except Exception: cur.execute("SELECT SalespersonID FROM dbo.Salesperson WHERE FullName = ?", (fullname,)) sp_id = int(cur.fetchone()[0]) statusvar = "Entry" # Insert appointment cur.execute( """ INSERT INTO dbo.Appointment (SalespersonID, StartDate, DurationMinutes, Status, CreatedDate) OUTPUT INSERTED.AppointmentID VALUES (?, ?, ?, ?, ?) """, (sp_id, start_datetime, dur, statusvar, created_date) ) appt_id = int(cur.fetchone()[0]) conn.commit() return f"✅ Appointment {appt_id} created for {fullname} at {start_datetime} ({dur} min)." except fn.UserThrownError: raise except Exception as e: logging.exception("create_appointment failed") try: if conn: conn.rollback() except: pass raise fn.UserThrownError(f"❌ Failed to create appointment: {str(e)}") finally: try: if cur: cur.close() except: pass try: if conn: conn.close() except: pass @udf.connection(argName="sqlDB1", alias="TWFDB") @udf.function() def approve_appointment_by_cfo( sqlDB1: fn.FabricSqlConnection, appointmentid: int ) -> str: if sqlDB1 is None: raise fn.UserThrownError("Connection injection failed. Check alias 'TWFDB' in UDF connections.") conn = cur = None try: conn = sqlDB1.connect() conn.autocommit = False cur = conn.cursor() # Check if appointment exists cur.execute("SELECT AppointmentID FROM dbo.Appointment WHERE AppointmentID = ?", (appointmentid,)) if not cur.fetchone(): raise fn.UserThrownError(f"No appointment found with ID {appointmentid}.") # Update status to CFOApproved cur.execute( """ UPDATE dbo.Appointment SET Status = 'CFOApproved' WHERE AppointmentID = ? """, (appointmentid,) ) conn.commit() return f"✅ Appointment {appointmentid} status updated to CFOApproved." except fn.UserThrownError: raise except Exception as e: logging.exception("approve_appointment_by_cfo failed") try: if conn: conn.rollback() except: pass raise fn.UserThrownError(f"❌ Failed to update appointment: {str(e)}") finally: try: if cur: cur.close() except: pass try: if conn: conn.close() except: pass
Decrypting data on the fly
This project addresses the performance and scalability challenges of working with data encrypted by legacy systems. By modernizing the data pipeline using Microsoft Fabric, we transitioned from resource-intensive batch jobs to a streamlined, translytical flow. Data is ingested into Delta Tables, compared efficiently using delta logic, and decrypted on-the-fly via Python UDFs integrated into SQL—enabling direct use in Power BI without burdening the database. This approach improves system responsiveness, reduces storage overhead, and enhances security by supporting encryption at rest and in transit. The decryption algorithm presented below was chosen at random; any decryption algorithm could replace the one displayed. @udf(returnType=StringType()) def app_decrypt(cipher_text): if not cipher_text: return '' try: # This is not our actual encryption algorithm; for display only! import base64 from cryptography.hazmat.primitives.ciphers import Cipher, algorithms, modes from cryptography.hazmat.backends import default_backend from cryptography.hazmat.primitives import padding from cryptography.fernet import Fernet fernet = Fernet(FERNET_KEY) decrypted = fernet.decrypt(cipher_text.encode()) return decrypted.decode('utf-8') return decrypted.decode('utf-8') except Exception: return "{Decrypt Error}"IoT Defect Tracking and Resolution from PBI report
Scenario description: IoT-Driven Maintenance with Power BI Writeback Overview In this workflow, industrial equipment is fitted with IoT sensors streaming operational metrics (temperature, vibration, pressure, etc.) into a real-time event stream. An event processor (Activator) continuously evaluates incoming readings against expected patterns. If a defect or anomaly is detected, the system: Sends an instant notification to the maintenance worker’s mobile device. Creates a new defect tracking record in the operational SQL database with initial details. After performing the repair, the maintenance worker accesses a Power BI maintenance dashboard. Here, they can: Review the defect record (with sensor data snapshots, alerts, and location). Document the defect resolution, root cause, and parts replaced. Submit this information directly back to the SQL defect tracking table via a User Data Function (UDF). The UDF securely updates the operational database in real time, eliminating the need for separate maintenance forms or manual syncing. This ensures that historical defect data stays complete, accurate, and available for both operational and analytical purposes. Here is the code used for this scenario to update the defect after resolving it through workforce: import fabric.functions as fn udf = fn.UserDataFunctions() # =============================== # UDF: update_defect_resolution # =============================== # Take a defect id, resolver, resoltuion notes and parts replaced (Status will be set automatically to "closed" as this is the intended action) as input parameters # Write the data back to the SQL database # Users will provide these parameters in the PowerBI report @udf.connection(argName="sqlDB",alias="<Your DB alias from connections>") @udf.function() def update_defect_resolution(sqlDB: fn.FabricSqlConnection, defectid: int, resolvedby: str, resolutionnotes: str, partsreplaced: str) -> str: """ Updates defect tracking record in the operational SQL database. Status is default set to "Closed" if a resolution is entered. Parameters: ----------- defectid : int Unique identifier of the defect record. resolvedby : str Name or ID of the maintenance worker. resolutionnotes : str Description of how the defect was resolved. partsreplaced : str Comma-separated list of parts replaced. Returns: -------- str "Defect resolution updated successfully." """ try: # Establish a connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() # Update query query = """ UPDATE [maintenance_demo].[defect_tracking] SET resolved_by = ?, resolution_notes = ?, parts_replaced = ?, status = 'Closed', resolution_timestamp = GETUTCDATE() WHERE defect_id = ? """ # Execute update cursor.execute(query, (resolvedby, resolutionnotes, partsreplaced, defectid)) # Commit the transaction connection.commit() cursor.close() connection.close() return "Defect resolution updated successfully." except Exception as e: return { "defect_id": defectid, "status": "Error", "message": str(e) } Feel free to use this code as inspiration for your own scenarios!Approval workflows
You can allow users to kick-off approval workflows to enable admins or specific users to approve or reject various requests. For example, on the report below non-admin users can propose a discount and submit it as request such that an admin will be notified of the request. The admin will get the discount request in a Teams channel, and when they click approve, they are redirected to a separate admin report that is automatically filtered to the right data and shows the requested discount percentage. The admin can do the final review and apply the requested discount. This approval workflow scenarios requires two user data functions to: Send a request : Request for an admin to approve a discount Approve a request: Admin can apply the discount Here is the user data function for the Send a request scenario: import fabric.functions as fn import logging import requests # Initialize the UserDataFunctions udf = fn.UserDataFunctions() #This function is designed for sending an approval request message to a specific Microsoft Teams channel using the Microsoft Graph API @udf.function() def RequestDiscount(user: str, comment: str, discount: float, revenue: float, URLfilter: str) -> str : logging.info('Python UDF trigger function processed a request.') try: # Define the endpoint URL for Microsoft Graph API to send a message to Teams team_id = "<team id>" # Replace with your actual team ID channel_id = "<channel id>" # Replace with your actual channel ID url = f"https://graph.microsoft.com/v1.0/teams/{team_id}/channels/{channel_id}/messages" # Define the headers including the authorization token headers = { "Authorization": "<auth token here>", "Content-Type": "application/json" } Discountpercent = str(discount) + "%" Discountdollars = '${:,.2f}'.format(((discount/100) * revenue)) ApprovalURL = "<Report URL here>" + URLfilter + "%20and%20DiscountValues%2FValues%20eq%20" + str(discount) #add your report URL here # Define the message body with HTML links #I only configured an approval flow for demo purposes. You will need to add reject action as well or remove this option. message_body = f""" <div> <strong>Discount Request from {user}</strong><br> <p>Discount percentage: {Discountpercent}</p> <p>Discount total: {Discountdollars}</p> <p>Comment: {comment}</p> <p> <a href={ApprovalURL} target="_blank">Approve</a> | <a href="https://example.com/reject" target="_blank">Reject</a> </p> </div> """ # Define the payload payload = { "body": { "content": message_body, "contentType": "html" } } # Make the POST request to the Microsoft Graph API response = requests.post(url, headers=headers, json=payload) # Check if the request was successful if response.status_code == 201: return "Approval request successfully posted in Teams." else: raise fn.UserThrownError("Failed to post approval request.", {"Status code": response.status_code}, {"Response": response.text}) except Exception as e: raise fn.UserThrownError("We ran into an issue.", {"Error:": str(e)}) Here is the user data function for the Approve a request scenario: import fabric.functions as fn import logging udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="Translytical") @udf.function() def ApproveDiscount(sqlDB: fn.FabricSqlConnection, opportunities: str, discountinput: float, comment: str)->str : logging.info('Python UDF trigger function processed a request.') connection = sqlDB.connect() cursor = connection.cursor() if (discount < 0): raise fn.UserThrownError("Discount cannot be negative") if (discount > 1): discount = discount / 100 if (discount > 0.50): raise fn.UserThrownError("Discount cannot exceed 50%") # SQL update command SQL_update_command = "UPDATE [dbo].[Opportunity] SET [Discount] = " + str(discount) + " WHERE Opportunity_Number IN (" + opportunities + ")" cursor.execute(SQL_update_command) # Commit the transaction connection.commit() # Close the connection cursor.close() connection.close() try: # Define the endpoint URL for Microsoft Graph API to send a message to Teams team_id = "<team id>" # Replace with your actual team ID channel_id = "<channel id>" # Replace with your actual channel ID url = f"https://graph.microsoft.com/v1.0/teams/{team_id}/channels/{channel_id}/messages" # Define the headers including the authorization token headers = { "Authorization": "<auth token here>", "Content-Type": "application/json" } # Define the message body with comment message_body = f""" <div> <strong>Approved Discount Request</strong><br> <p>Comment: {comment}</p> </div> """ # Define the payload payload = { "body": { "content": message_body, "contentType": "html" } } # Make the POST request to the Microsoft Graph API response = requests.post(url, headers=headers, json=payload) # Check if the request was successful if response.status_code == 201: return "Discount has been approved and applied. Team has been notified." else: raise fn.UserThrownError("Failed to post approval request.", {"Status code": response.status_code}, {"Response": response.text}) except Exception as e: raise fn.UserThrownError("We ran into an issue.", {"Error:": str(e)}) Feel free to use this code as inspiration for your approval workflow scenarios!Dynamic email notifications
You can trigger dynamic notifications to raise awareness or promote a call to action. In this example report, changing an offer status to ‘Accepted’ will automatically send a dynamic marketing email to the partner company’s point of contact. Here is the Python code for the user data function that powers this scenario: import fabric.functions as fn import logging import requests from flask import Flask from flask_mail import Mail, Message udf = fn.UserDataFunctions() app = Flask(__name__) mail = Mail(app) # instantiate the mail class # configuration of mail app.config['MAIL_SERVER']='smtp.sendgrid.net' app.config['MAIL_PORT'] = 587 app.config['MAIL_USERNAME'] = 'apikey' app.config['MAIL_PASSWORD'] = '<your API key here>' app.config['MAIL_USE_TLS'] = True app.config['MAIL_DEFAULT_SENDER'] = '<your email here>' mail = Mail(app) html_email = """ <Your HTML email here> """ @udf.connection(argName="sqlDB",alias="Translytical") @udf.function() def get_data_write_to_sql_db_send_email(sqlDB: fn.FabricSqlConnection, company: str, status: str, date:str, comment: str) -> str: logging.info('Python UDF trigger function processed a request.') # Error handling for no status selected or no company input if(status=="" or len(status) < 1): raise fn.UserThrownError("The status isn't valid.", {"status:": status}) if(company=="" or len(company) < 1): raise fn.UserThrownError("The company isn't valid.", {"company:": company}) #Call External API to get Company contact information url = "https://dummy-json.mock.beeceptor.com/users" response = requests.get(url) if response.status_code == 200: Jsondata = response.json() for i in Jsondata: #Check if company contact is registered in the external system if i['company'] == company: # Combine timestamp with comment comment_value = date + " - " + comment # Get customer contact and put in format based on if updating an record or adding new record Existing_Partner_data = (i['name'], i['username'], i['email'], i['address'], i['zip'], i['state'], i['country'], i['phone'], status, comment_value, company) New_Partner_data = (i['name'], company, i['username'], i['email'], i['address'], i['zip'], i['state'], i['country'], i['phone'], status, comment_value) # Establish a connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() #Check if there is a status record for the company SQL_read_Command = "SELECT * FROM [dbo].[CompanyStatus] WHERE Company = ?" cursor.execute(SQL_read_Command, company) if cursor.fetchone(): #if there is a status record for the company, update the status and contact info SQL_update_command = "UPDATE [dbo].[CompanyStatus] SET [Name] = ?, [Username] = ?, [Email] = ?, [Address] = ?, [Zip] = ?, [State] = ?, [Country] = ?, [Phone] = ?, [Status] = ?, [Comment] = ? WHERE [Company] = ?;" cursor.execute(SQL_update_command, Existing_Partner_data) else: #if there is not a status record for the company, add new record SQL_insert_command = "INSERT INTO [dbo].[CompanyStatus](Name, Company, Username, Email, Address, Zip, State, Country, Phone, Status, Comment) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);" cursor.execute(SQL_insert_command, New_Partner_data) # Commit the transaction connection.commit() # Close the connection cursor.close() connection.close() #If status is set to accepted, send an email to company contact #Note: this is hard-coded to a temp working email for demo purposes if(status == "Accepted"): with app.app_context(): msg = Message (company + " x Contoso collab time!", recipients = ['<Email here>'] #I put a static temp email here but you can make this dynamic ) msg.html = html_email mail.send(msg) return "Accepted collab offer with " + company + ", and " + i['name'] + " will be notified shortly." return "Collab with " + company + " is now " + status.lower() + "." #if reached here in the code, then the company contact is NOT registered in the external system, throw error raise fn.UserThrownError("The company is not a registered partner.", {"company:": company}) Feel free to use this code as inspiration for your own dynamic notification scenario!
