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!
