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!Handling Multi-Row Inputs for Translytical Task Flows
This post offers a practical guide to passing multiple rows of data from a Power BI report to a User-Defined Function (UDF) using translytical task flows. The solution leverages the TOJSON() DAX function to overcome the limitation of scalar-only inputs from slicers. Translytical Task Flows At their core translytical task flows allow buttons within your Power BI reports to trigger a UDF. Within the button definition you map a button, list, or text slicer; a data field; or measures from the report to the input parameters of the UDF. However, a key limitation is that when using a slicer, you can only pass a single (scalar) value. If a user selects multiple items, the button becomes inactive, preventing the UDF from being called. But with a measures, you are able to pass tabular data a string, with the help of the seldomly used DAX function TOJSON(). Sudoku This example uses a Sudoku game built with Power BI to demonstrate the solution. The game state is stored in a Fabric SQL database, and UDFs are used to manage game actions. The game UI consists of several components: Game Selection: A slicer to select a game template. Game Grid: A button slicer representing the 81 cells of the Sudoku board. Each button uses a DAX-generated SVG to display numbers and pencil marks. Number Selector: A button slicer for selecting the number to enter (1-9). Pen/Pencil Buttons: Buttons that trigger UDFs to enter numbers or pencil marks into the selected cell(s). The architecture is as follow: The Pen and Pencil buttons are mapped to the position from the grid button slicer and the value from the value selector button slicer. This works perfectly for a single cell selection. Generally in Sudoku, you want to mark two or more cells simultaneously when entering pencil marks. However when we select more than one cell, the Pen and Pencil buttons becomes inactive: Using TOJSON() to Pass Tabular Data To enable multi-cell input, we need a method to generate a single string that encapsulates information about multiple selected positions. For those familiar with REST APIs, calling the UDF in this context is analogous to a POST request, where the JSON format is the preferred payload. Fortunately, DAX provides a perfect solution: the built-in TOJSON() function. This function generates a JSON string from a given table. We can create a DAX measure that generates a JSON string containing all the selected grid positions. Selected Positions = IF( ISFILTERED( 'Grid Position'[Position] ), TOJSON( VALUES( 'Grid Position'[Position] ) ) ) This provides the following output: We can update the mapping from the position provided by the grid button slicer, to the DAX measure: Handling the JSON in the UDF The Python code in the UDF can then parse this string back into a list of positions to be processed. import fabric.functions as fn import json from datetime import datetime udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="Game") @udf.function() def toggle_pencil_mark(sqlDB: fn.FabricSqlConnection, gameId: int, position: str, pencilValue: int) -> str: """ Toggle a pencil mark in a Sudoku cell Args: gameId: The game ID position: JSON string with cell positions pencilValue: Pencil mark value (1-9) Returns: JSON string with operation result """ # Parse positions from JSON positions_data = json.loads(position) positions = [pos[0] for pos in positions_data["data"]] ## Open connection and SQL database and write values to cells conn = sqlDB.connect() cursor = conn.cursor() processed_positions = [] last_action_type = None last_is_active = False for pos in positions: # Skip cells with values cursor.execute("SELECT CurrentValue FROM GameState WHERE GameId = ? AND Position = ?", (gameId, pos)) if cursor.fetchone()[0] is not None: continue # Toggle pencil mark cursor.execute("EXEC sp_TogglePencilMark ?, ?, ?", (gameId, pos, pencilValue)) conn.commit() # Check if added or removed cursor.execute("SELECT IsActive FROM PencilMarks WHERE GameId = ? AND Position = ? AND PencilValue = ?", (gameId, pos, pencilValue)) is_active = cursor.fetchone()[0] last_action_type = "added" if is_active else "removed" last_is_active = is_active processed_positions.append(pos) cursor.close() conn.close() return json.dumps({ "status": "SUCCESS", "message": f"Pencil mark {pencilValue} {last_action_type} at position(s) {processed_positions}", "game_id": gameId, "positions_processed": processed_positions, "pencil_value": pencilValue, "is_active": last_is_active, "action_type": last_action_type, "action_time": datetime.now().isoformat() }) By parsing the JSON string, the UDF can now iterate through the list of positions and perform the required database operations for each one. This allows players to mark multiple cells with the same pencil value simultaneously. This approach is highly versatile and can be used as a template for any scenario where you need to pass multiple selected items from a Power BI report to a UDF. More in-depth details can be found at these blog posts: Translytical Sudoku Handling Multi-Row Inputs for Translytical Task Flows Hope this example is useful and sparks some ideas!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 spendingVacation Tracker
Code Repo : https://github.com/NandanHegde15/MSFTFabric-Projects/blob/main/Vacation%20tracker/User%20Data%20Function.py Work Flow: The code implements a Vacation Tracker workflow in Microsoft Fabric using User Defined Functions (UDFs). It enables employees to submit and the Supervisor of that employee to approve, or reject vacation requests based on notification received. validates requests, checks public holidays, uses Azure OpenAI for recommendations, and sends notifications (via email + SMS). Employee │ │ (Submit Request: EmployeeId, StartDate, EndDate, Reason, Priority) ▼ Vacation Request UDF (vacation_tracker_request) │ ├─► Validate input (dates, reason, priority flag) │ ├─► SQL MERGE into VacationTracker │ ├─► Fetch Employee & Supervisor details from SQL │ ├─► Call Nager.Date API → Get holidays │ ├─► Query SQL → Past 12 months leave history │ ├─► Build prompt → Call Azure OpenAI GPT │ │ │ └─► AI Suggestion (approve/reject context) │ ├─► Notify Supervisor: │ • Email via Logic App (Approve / Reject buttons → Power BI report link) │ • SMS via Twilio (if priority = Yes) │ ▼ Supervisor Action (Approve / Reject) │ ├─► If Approve → vacation_tracker_approval UDF │ • Update SQL (status = Approved, reason, modified date) │ • Notify Employee (Approval Email via Logic App) │ └─► If Reject → vacation_tracker_rejection UDF • Update SQL (status = Rejected, reason, modified date) • Notify Employee (Rejection Email via Logic App) Employee Page : Governed by RLS (Every employee would see his/her own details and can raise a Vacation request) #Data Function 1 Supervisor would be able to see all details of his resources : Supervisor would receive an email and in case of High priority receive an SMS with AI sugesstion based on past data and public holiday in range : Based on Approve or Reject decision, Supervisor would be redirected to either Approval page or rejection page #Data Function 2 #Data Function 3 Based on approval/Rejection, Backend database and the Direct Query report would be updated and the employee would receive an email notification :Data annotations
With Translytical task flows you can enable datapoint annotation directly within your report. For example, in the report below you can add, edit or delete annotations about each month’s sales data. Here we can see that to add a new data annotation, you select the datapoint, input your comment, and then submit, and it appears immediately on the report. Data annotation scenarios may consist of up to three user data functions to: Add Annotation: Add a new datapoint annotation Edit Annotation: Update an exisiting annotation Delete Annotation: Delete a specific annotation Here is the user data function for the Add Annotation scenario: import fabric.functions as fn import logging import calendar udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB",alias="Translytical") @udf.function() def AddAnnotation(sqlDB: fn.FabricSqlConnection, date: str, commentdate: str, comment: str, user: str) -> str: logging.info('Python UDF trigger function processed a request.') # month abbr to YYYY-MM-DD month_num = list(calendar.month_abbr).index(date) formatted_date = f"{2023}-{month_num:02d}-10" data = (commentdate, formatted_date, user, comment) # Establish a connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() logging.info("Adding comment ... ") # Insert data into the table insert_query = "INSERT INTO [dbo].[DataReasoning] ([Date_Created],[Date_Month],[User],[Comment]) VALUES (?, ?, ?, ?);" cursor.execute(insert_query, data) logging.info("Comment was added") # Commit the transaction connection.commit() # Close the connection cursor.close() connection.close() return "Comment was successfully added" Here is the user data function for the Edit Annotation scenario: import fabric.functions as fn import logging import calendar udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB",alias="Translytical") @udf.function() def EditAnnotation(sqlDB: fn.FabricSqlConnection, comment: str, commentdate: str, user: str, newcomment :str ) -> str: logging.info('Python UDF trigger function processed a request.') data = (newcomment, commentdate, user, comment) # Establish a connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() # Insert data into the table logging.info("Updating comment") update_query = " UPDATE [dbo].[DataReasoning] SET [Comment] = ?, [Date_Created] = ?, [User] = ? WHERE [Comment] = ?;" cursor.execute(update_query, data) logging.info("Comment was updated") # Commit the transaction connection.commit() # Close the connection cursor.close() connection.close() return "Comment was successfully updated" Here is the user data function for the Delete Annotation scenario: import fabric.functions as fn import logging udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB",alias="Translytical") @udf.function() def DeleteAnnotation(sqlDB: fn.FabricSqlConnection, comment: str ) -> str: logging.info('Python UDF trigger function processed a request.') # Establish a connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() # Delete comment logging.info("Deleting comment ... ") delete_query = "DELETE FROM [dbo].[DataReasoning] WHERE [Comment] = ?" cursor.execute(delete_query,comment) logging.info("Comment was deleted") # Commit the transaction connection.commit() # Close the connection cursor.close() connection.close() return "Comment was successfully deleted" Feel free to use this code as inspiration for your data annotations 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!Modify data record(s) in a SQL table
This translytical task flow can be used as a template / guide for how to modify data records within a SQL table. For example, you can use translytical task flows to modify the discount value seen in the table without ever leaving the report. You simply enter in the new value in the text slicer and click the ‘Submit discount’ button, which runs a Fabric User data function that instantly updates the data source records that match the applied filters. Here is the Python code for the user data function that powers this scenario: import fabric.functions as fn import logging udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="Translytical") @udf.function() def UpdateDiscount(sqlDB: fn.FabricSqlConnection, quantity: int, risklevel: str, dealexpiration: int, discount: float)->str : logging.info('Python UDF trigger function processed a request.') sqlConnection = sqlDB.connect() cursor = sqlConnection.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%") risk = get_risk(risklevel) if risk == 0: return f"This opportunity cannot be changed since it is either in Won or Lost state." days_to_close_sql = get_days_to_close_sql_string(dealexpiration) query = f"UPDATE [dbo].[Opportunity] SET [Discount] = ? WHERE Rating = ? {days_to_close_sql} AND Quantity = ?" params = (discount, risk, quantity) # Convert the query to a properly formatted T-SQL statement tsql_query = query.replace("?", "{}").format(*[f"'{p}'" if isinstance(p, str) else p for p in params]) # Print the actual T-SQL statement logging.info(f"Executing SQL Query: {tsql_query}\n") cursor.execute(query, params) sqlConnection.commit() sqlConnection.close() return f"Opportunities with {risklevel} are updated." def get_risk(risklevel:str)->int: match risklevel: case "High risk": return 1 case "Medium risk": return 2 case "Low risk": return 3 case _: return 0 def get_days_to_close_sql_string(dealexpiration: int)->str: match dealexpiration: case 60: return "AND Days_To_Close <= 69" case 300: return "AND Days_To_Close >= 300" case _: return f"AND Days_To_Close >= {dealexpiration} AND Days_To_Close <= {dealexpiration + 9}" Feel free to use this code as inspiration for your writeback scenarios!Augment data on the fly
You can programmatically fetch additional data related to the data within your report. Example, you can use this report to not only track and update the status of various collaborations, but you can also fetch additional data via API, such as the latest contact information for a registered partner company. Here is the Python code for the user data function that powers this scenario: import fabric.functions as fn import logging import requests udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB",alias="Translytical") @udf.function() def GetContactInfo(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 contact info within registered partner company 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() 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 data ops + write back scenario!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!
