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!