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 :Intelliventra – 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.Dynamic Power BI Experiences
I created a Power BI learning game to demonstrate how Translytical and UDF capabilities can transform user journey and user experience in Power BI to boost adoption. I focused on the following Translytical use cases: 1. Registration and State Management The game begins with an engagement action : players choose to start and see their email recognized. This creates commitment to the learning experience. A UDF populates the SQL database table with active players, notifications, timestamps, avatars, progress, and points. Using DAX to control transparency based on written columns, the registration menu "disappears" for active players but reappears for new users opening the Power BI report. 2. Notification Management I created personalized progress with guided steps to direct users through the prepared journey. Notifications attract player attention and explain game rules. This approach applies to any business report (eg: pushing notifications about report readiness, data accuracy, issues, guidance, etc. significantly boosts adoption). Users can dismiss notifications to avoid spam from already-consumed information. 3. Custom Time-Based Scoring To demonstrate this functionality, users are guided to click a specific item and select an avatar. Avatar selection triggers a UDF that calculates progression speed and assigns scores based on time spent. This enables progress tracking and maintains learning engagement. Result: These three combined UDFs showcase new possibilities for user experience, storytelling and user journey design, accessibility and engagement levels that boost adoption. The report is fully personalized based on each player's progress, allowing players to start from where they left off. This showcases an example of personalized journey tracking. Impact: While gamification provides an excellent confidence boost and aids change management, these techniques extend far beyond gaming scenarios. By combining state management, notification handling, and adaptive scoring, organizations can create engaging user journeys that drive adoption and sustained usage. The techniques showcased here provide a foundation for any scenario requiring user engagement, progress tracking, and personalized experiences. UDF used: TRANSLYTICAL GAMING FRAMEWORK - UDF #1 of 3 Purpose: User registration and state management for personalized Power BI experiences Business Value: Enables session persistence and user journey tracking insert_gaming_user import fabric.functions as fn from datetime import datetime # Create the UserDataFunctions instance udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="AlexSQL") @udf.function() def insert_gaming_user(sqlDB: fn.FabricSqlConnection, userPrincipalName: str) -> str: """ Insert a new gaming user record with default values including timestamp Args: sqlDB: Connection to the Fabric SQL database userPrincipalName: The user's principal name from DAX (e.g., from USERPRINCIPALNAME()) Returns: str: Success message for Power BI """ try: # Input validation if not userPrincipalName or userPrincipalName.strip() == "": raise fn.UserThrownError("User principal name cannot be empty.") # Clean the input userName = userPrincipalName.strip() # Get current timestamp for when UDF is executed current_timestamp = datetime.now() # Establish connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() # Check if user already exists (optional - remove if you want duplicates) check_query = "SELECT COUNT(*) FROM [dbo].[Gaming] WHERE [UserName] = ?" cursor.execute(check_query, userName) user_count = cursor.fetchone()[0] if user_count > 0: cursor.close() connection.close() raise fn.UserThrownError(f"User '{userName}' already exists in the gaming table.") # Generate next ID (since you're using INT NOT NULL PRIMARY KEY) # Get the maximum existing ID and add 1 id_query = "SELECT ISNULL(MAX([Id]), 0) + 1 FROM [dbo].[Gaming]" cursor.execute(id_query) next_id = cursor.fetchone()[0] # Insert the new gaming user record with extended columns including Points and Avatar ID insert_query = """ INSERT INTO [dbo].[Gaming] ( [Id], [UserName], [BeginID], [NotificationStart], [TimeStamp], [Level1], [L1_TimeStamp], [Level2], [L2_TimeStamp], [Points], [ChallengePoints], [Avatar ID] ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """ # Execute the insert with specified values # TimeStamp = current datetime, Points and ChallengePoints = 0, Avatar ID = 2, other new columns = NULL cursor.execute(insert_query, ( next_id, # Id userName, # UserName 1, # BeginID 0, # NotificationStart current_timestamp, # TimeStamp (current datetime) None, # Level1 (NULL) None, # L1_TimeStamp (NULL) None, # Level2 (NULL) None, # L2_TimeStamp (NULL) 0, # Points (set to 0) 0, # ChallengePoints (set to 0) 2 # Avatar ID (set to 2) )) # Commit the transaction connection.commit() # Close connections cursor.close() connection.close() return f"Successfully added user '{userName}' to gaming table with ID {next_id} and Avatar ID 2 at {current_timestamp.strftime('%Y-%m-%d %H:%M:%S')}" except fn.UserThrownError: # Re-raise user errors as-is raise except Exception as e: # Handle any other database errors raise fn.UserThrownError(f"Database error occurred: {str(e)}") TRANSLYTICAL GAMING FRAMEWORK - UDF #2 of 3 Purpose: Notification management and user engagement flow control Business Value: Enables dismissible notifications and prevents information spam, applicable to any business report requiring guided user experiences update_notification_status import fabric.functions as fn # Create the UserDataFunctions instance udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="AlexSQL") @udf.function() def update_notification_status(sqlDB: fn.FabricSqlConnection, userPrincipalName: str) -> str: """ Update the NotificationStart column to 1 for an existing user Args: sqlDB: Connection to the Fabric SQL database userPrincipalName: The user's principal name from DAX (e.g., from USERPRINCIPALNAME()) Returns: str: Success message for Power BI """ try: # Input validation if not userPrincipalName or userPrincipalName.strip() == "": raise fn.UserThrownError("User principal name cannot be empty.") # Clean the input userName = userPrincipalName.strip() # Establish connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() # Check if user exists check_query = "SELECT COUNT(*) FROM [dbo].[Gaming] WHERE [UserName] = ?" cursor.execute(check_query, userName) user_count = cursor.fetchone()[0] if user_count == 0: cursor.close() connection.close() raise fn.UserThrownError(f"User '{userName}' does not exist in the gaming table.") # Update the NotificationStart column to 1 update_query = """ UPDATE [dbo].[Gaming] SET [NotificationStart] = 1 WHERE [UserName] = ? """ # Execute the update cursor.execute(update_query, userName) # Commit the transaction connection.commit() # Close connections cursor.close() connection.close() return f"Successfully updated NotificationStart to 1 for user '{userName}'" except fn.UserThrownError: # Re-raise user errors as-is raise except Exception as e: # Handle any other database errors raise fn.UserThrownError(f"Database error occurred: {str(e)}") TRANSLYTICAL GAMING FRAMEWORK - UDF #3 of 3 Purpose: Custom time-based scoring and progress tracking with avatar personalization Business Value: Measures user engagement speed and rewards timely interactions, adaptable for training completion, task performance, and user adoption metrics complete_level1_challenge import fabric.functions as fn from datetime import datetime # Create the UserDataFunctions instance udf = fn.UserDataFunctions() @udf.connection(argName="sqlDB", alias="AlexSQL") @udf.function() def complete_level1_challenge(sqlDB: fn.FabricSqlConnection, userPrincipalName: str, selectedAvatar: int) -> str: """ Complete Level 1 challenge, assign points based on time difference, and update Avatar ID Args: sqlDB: Connection to the Fabric SQL database userPrincipalName: The user's principal name from DAX (e.g., from USERPRINCIPALNAME()) selectedAvatar: The selected avatar ID from DAX measure Returns: str: Success message with points awarded for Power BI """ try: # Input validation if not userPrincipalName or userPrincipalName.strip() == "": raise fn.UserThrownError("User principal name cannot be empty.") if selectedAvatar is None: raise fn.UserThrownError("Selected Avatar cannot be empty.") # Clean the input userName = userPrincipalName.strip() # Get current timestamp for L1_TimeStamp current_timestamp = datetime.now() # Establish connection to the SQL database connection = sqlDB.connect() cursor = connection.cursor() # Check if user exists and get their initial TimeStamp check_query = """ SELECT [TimeStamp], [L1_TimeStamp] FROM [dbo].[Gaming] WHERE [UserName] = ? """ cursor.execute(check_query, userName) result = cursor.fetchone() if not result: cursor.close() connection.close() raise fn.UserThrownError(f"User '{userName}' does not exist in the gaming table.") initial_timestamp = result[0] existing_l1_timestamp = result[1] # Check if Level 1 challenge already completed if existing_l1_timestamp is not None: cursor.close() connection.close() raise fn.UserThrownError(f"User '{userName}' has already completed Level 1 challenge.") # Check if initial TimeStamp exists if initial_timestamp is None: cursor.close() connection.close() raise fn.UserThrownError(f"User '{userName}' does not have an initial TimeStamp.") # Calculate time difference time_diff = current_timestamp - initial_timestamp total_minutes = time_diff.total_seconds() / 60 # Determine points based on time difference # GAMING MECHANICS: Time-based scoring rewards faster completion # < 10 min = 20 pts (expert), < 1 hour = 10 pts (good), etc. if total_minutes < 10: points = 20 time_category = "< 10 minutes" elif total_minutes < 60: # < 1 hour points = 10 time_category = "< 1 hour" elif total_minutes < 1440: # < 1 day (24 hours * 60 minutes) points = 5 time_category = "< 1 day" else: # more than 1 day points = 3 time_category = "> 1 day" # Update L1_TimeStamp, Level1 points, and Avatar ID update_query = """ UPDATE [dbo].[Gaming] SET [L1_TimeStamp] = ?, [Level1] = ?, [Avatar ID] = ? WHERE [UserName] = ? """ # Execute the update cursor.execute(update_query, (current_timestamp, points, selectedAvatar, userName)) # Commit the transaction connection.commit() # Close connections cursor.close() connection.close() return f"Level 1 completed for '{userName}': {points} points awarded ({time_category}), Avatar ID updated to {selectedAvatar} at {current_timestamp.strftime('%Y-%m-%d %H:%M:%S')}" except fn.UserThrownError: # Re-raise user errors as-is raise except Exception as e: # Handle any other database errors raise fn.UserThrownError(f"Database error occurred: {str(e)}") If this Translytical entry resonates with you, I'd love to connect! Please give it a ❤️ if you found it valuable, and feel free to reach out on LinkedIn Alexandru Badiu | LinkedIn Best regards, Alexandru Badiu
