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!
