UpdateExcelBudget
Back to ListDescription: Update function for the Excel CashFlow App
FileMaker Prototype:
Set Variable [$res; ACF_Run("UpdateExcelBudget"; string_BudgetKey)]
Category: EXCEL
Dependencies
- find_in_array: Find an item in an ACF array
- NextTransRepeat: Calculate the next date (>= minDate) for a recuring transaction.
- DefineStyles: Define the styles used in the cash-flow demo spreadsheet.
- GetJsonArrayS: ACF function: get an array from a JSON object into an regular array.
- InsertNewRow: Insert new row in CashFlow budget demo application
- FindSheetName: Searches the sheets in a workbook for a sheet name, and returns it sheetID
- FindDataRow: Locates a specific label in the first column (column A) of a given sheet.
- MoveFileToBackupLoc: Rename file to a backup name, finds an unused name to rename to.
NOTE: This function serves as a reference template. To use it effectively, you may need to adjust certain parts of the code, such as field names, database structures, and specific data lists to fit the requirements of your system. This customization allows the function to work seamlessly within your environment.
Function source:
/*
Function: UpdateExcelBudget
Description: Main routine to update an existing Excel budget sheet based on SQL data.
This function pulls data using SQL, compares it to a previous snapshot,
and updates the Excel workbook while conserving manual edits.
Parameters:
- BudgetKey (string): The key identifying the budget to update.
Returns:
- "OK" on success or an error message if the update fails.
*/
function UpdateExcelBudget (string BudgetKey)
ARRAY STRING budTitle;
ARRAY STRING budSubtitle;
ARRAY DATE budFromDate;
ARRAY DATE budToDate;
ARRAY STRING budExcelPath;
ARRAY STRING traPrimaryKey;
ARRAY STRING traBudget_fk;
ARRAY STRING traName;
ARRAY DATE traDateStart;
ARRAY STRING traIncomeExpense;
ARRAY STRING traPeriodType;
ARRAY INT traPeriodCount;
ARRAY FLOAT traAmount;
ARRAY STRING traCategory;
// SQL Query to fetch budget and transaction data
string sql = "SELECT bud.Title, bud.Subtitle, bud.FromDate, bud.ToDate, bud.ExcelPath,
tra.PrimaryKey, tra.Budget_fk, tra.Name, tra.DateStart, tra.IncomeExpense, tra.PeriodType,
tra.PeriodCount, tra.Amount, tra.Category
FROM Budget AS bud
INNER JOIN Transactions AS tra ON bud.PrimaryKey = tra.Budget_fk
WHERE bud.PrimaryKey = :BudgetKey
ORDER BY tra.IncomeExpense DESC, tra.Category ASC, tra.Name ASC
INTO :budTitle, :budSubtitle, :budFromDate, :budToDate, :budExcelPath,
:traPrimaryKey, :traBudget_fk, :traName, :traDateStart, :traIncomeExpense, :traPeriodType,
:traPeriodCount, :traAmount, :traCategory";
// Execute the SQL query and fetch the data
string res = ExecuteSQL(sql);
int ctrans = sizeof(budExcelPath);
if (res != "OK" || ctrans == 0) then
return "No transactions: " + res; // Return if no transactions found
end if
// Extract date range and calculate month/year ranges
DATE fromDate = budFromDate[1];
DATE toDate = budToDate[1];
int mndFrom = int(string(fromDate, "%m"));
int mndTo = int(string(toDate, "%m"));
int YearFrom = int(string(fromDate, "%Y"));
int YearTo = int(string(toDate, "%Y"));
// Define month names for sheet naming
ARRAY STRING mndNames = {"January", "February", "March", "April", "May", "June",
"July", "August", "September", "October", "November", "December"};
// Retrieve the previous copy generated along with the original file.
JSON prevCopy;
prevCopy = ExecuteSQL ( "SELECT jsCopy FROM Budget WHERE PrimaryKey = :BudgetKey");
// Check if the from-date has changed; if so, updating is not allowed
string xx;
if ( prevCopy["budFromDate"] != string (budFromDate[1], "%Y-%m-%d" )) then
xx = prevCopy["budFromDate"];
alert ( "Original sheet generated with from-date: " + xx + ", that has changed. Cannot update sheet");
return "Date changed");
end if
// Parse previous transaction names into an array for comparison
array string prevTraNames = GetJsonArrayS(prevCopy, "traName"); // Used to find if its a new transaction item.
print "prevTraNames Size: " + sizeof ( prevTraNames)+"\n";
print "prevTraNames: " + implode (",", prevTraNames)+"\n";
// Prepare a new JSON snapshot for the updated data
JSON js;
js["budTitle"] = budTitle[1];
js["budSubtitle"] = budSubtitle[1];
js["budFromDate"] = budFromDate[1];
js["budToDate"] = budToDate[1];
js["budExcelPath"] = budExcelPath[1];
js["BudgetKey"] = BudgetKey;
js["traPrimaryKey"] = traPrimaryKey;
js["traName"] = traName;
js["traDateStart"] = traDateStart;
js["traIncomeExpense"] = traIncomeExpense;
js["traPeriodType"] = traPeriodType;
js["traPeriodCount"] = traPeriodCount;
js["traAmount"] = traAmount;
js["traCategory"] = traCategory;
string ExcelPath = budExcelPath[1];
// move the old file to a backupfile, so we can create a new file with the updated content.
string BackuPath = MoveFileToBackupLoc ( ExcelPath);
int wb = Excel_Open ( BackuPath, "r" );
int sheetCount = Excel_CountSheets ( wb);
// res = DefineStyles(wb); // We dont define styles in update, as all the styles is in the sheet allready.
string sh1, sh2, snum; // Styles used in the original sheet. They have different names now. Style1, Style2, etc.
int sumary = Excel_GetSheetID ( wb, 1) ;
int i,j, k, s, dr;
// Start updating the sheet.
// Extract styles from the first month sheet
int s = Excel_GetSheetID(wb, 2); // First month data sheet
string sh1 = Excel_GetCellStyle(s, 1, 1); // Header style 1
string sh2 = Excel_GetCellStyle(s, 2, 1); // Header style 2
// Locate a matching data row in the sheet using transaction names
for (i=1, ctrans)
dr = FindDataRow( s, 5, traName[i] );
if ( dr>0) then
break;
end if
end for0
// If no matching row is found, abort the update
if (dr < 0 ) then
excel_close ( wb );
res = move_file (BackuPath, ExcelPath ); // Move back to original name.
return "Cant find any income/expence name match in the sheet. Is it the right sheet?";
end if
string snum = Excel_GetCellStyle(s, dr, 1); // Style for data rows
// Loop all years, all months, and do changes to the datarows if there is any change.
int yy, mm, immf, immt, position, dim;
date cdate, fim, lim, today = date(now());
float cellAm, prevAmJs, f1, f2;
bool d1, a1, iv, t1, newinc, okToDel;
int sumExpRow, sumIncomeRow, firstIncRow, lastIncRow, firstExpRow, lastExpRow,newRow, prevCount, x;
string sheetName, formula1, formula2;
array string confirmedDeleteList ;
// Iterate through the years and months to update each sheet
try
for (yy = YearFrom, YearTo)
immf = ((yy == YearFrom) ? mndFrom : 1); // Starting month for the year
immt = ((yy == YearTo) ? mndTo : 12); // Ending month for the year
for (mm = immf, immt)
// Determine first and last day of the month
fim = date(format("%04d-%02d-%02d", yy, mm, 1), "%Y-%m-%d");
if ( mm == 12 ) then
// special for december, as month 13 does not exists.
lim = date(format("%04d-%02d-%02d", yy, mm , 31), "%Y-%m-%d");
else
lim = date(format("%04d-%02d-%02d", yy, mm + 1, 1), "%Y-%m-%d") - 1;
end if
dim = lim - fim + 1;
// Find the sheetname in the workbook.
sheetName = mndNames[mm] + "-" + yy;
s = FindSheetName ( wb, sheetName );
if (s<0) then
// Sheet does not exists. - Check if last sheet name is previous month.
throw "We does not support adding new sheets when update yet: " +sheetName ;
else
// Process income and expense rows for the month
sumExpRow = FindDataRow(s, 5, "Sum Expenses");
sumIncomeRow = FindDataRow(s, 5, "Sum Income");
if ( sumExpRow < 0 || sumIncomeRow < 0) then
throw "Sheet does not have 'Sum Income' or 'Sum Expenses' rows. Cannot update";
end if
// Parse formulas to extract row ranges for income and expense
formula1 = Excel_getCellFormula ( s, sumIncomeRow, 2);
formula2 = Excel_getCellFormula ( s, sumExpRow, 2);
// Use regex to extract the first and last row from the formula.
firstIncRow = regex_replace ( ".*?([A-Z]+)(\d+):[A-Z]+\d+.*", formula1, "$2");
lastIncRow = regex_replace ( ".*?[A-Z]+\d+:[A-Z]+(\d+).*", formula1, "$1");
firstExpRow = regex_replace ( ".*?([A-Z]+)(\d+):[A-Z]+\d+.*", formula2, "$2");
lastExpRow = regex_replace ( ".*?[A-Z]+\d+:[A-Z]+(\d+).*", formula2, "$1");
print format ( "\nFormula Income: %s, Expence: %s, Fi %d, Li %d, Fe %d, Le %d\n",
formula1, formula2, firstIncRow, lastIncRow, firstExpRow, lastExpRow);
// Find deleted transactions.
prevCount = sizeof (prevTraNames);
for (i=1, prevCount)
x = find_in_array (traName, prevTraNames[i]);
if (x< 0) then
okToDel = false;
// We use a confirmedDeleteList to collect deleted and confirmed deletes, so We dont ask for each sheet.
x = find_in_array (confirmedDeleteList, prevTraNames[i]);
if ( x>0) then
okToDel = true;
else
if (confirm ("Sheet "+sheetName+" has a transaction named " + prevTraNames[i] + " that is not in the list. Delete it?")) Then
okToDel = true;
confirmedDeleteList[] = prevTraNames[i];
end if
end if
if (okToDel) Then
dr = FindDataRow( s, firstIncRow, prevTraNames[i] );
if ( dr > 0 ) then
excel_DeleteRows ( s, dr, dr);
sumExpRow--; sumIncomeRow--; // the column sums has moved up 1
if (dr <= lastIncRow) then
lastIncRow --; firstExpRow --; lastExpRow--;
else
lastExpRow --; // It was an expence, only the last expence row has moved.
end if
end if
end if
end if
end for
// Porcess transactions, insert any new transaction as new row in the sheet.
for (i=1, ctrans)
dr = FindDataRow( s, firstIncRow, traName[i] );
print format ("FindDataRow: %s, %d\n", traName[i], dr);
if ( dr<0) then
// The datarow does not exists. Create a new one.
newInc = (( left(traIncomeExpense[i],1) == "I")?true:false);
if ( newInc ) then
newRow = InsertNewRow ( s, firstIncRow, lastIncRow, traName[i], snum);
lastIncRow++; lastExpRow++;firstExpRow++;
else
newRow = InsertNewRow ( s, firstExpRow, lastExpRow, traName[i], snum);
lastExpRow++;
end if
sumExpRow++; sumIncomeRow++;
dr = newRow;
// Clear the new row with cell style.
for ( j = 0, dim-1)
excel_setCell ( s, dr, j + 2, "", snum);
end for
// Populate values for our new row.
cdate = traDateStart[i];
repeat
// We only update future dates.
if (cdate <= lim && cdate >= fim && cdate >= today) then
position = cdate - fim;
Excel_SetCell(s, dr, position + 2, traAmount[i], snum);
end if
cdate = NextTransRepeat(cdate, fim, traDateStart[i], traPeriodType[i], traPeriodCount[i]);
until (cdate > lim);
else
// We found the datarow. Compare the values.
k= find_in_array (prevTraNames, traName[i]);
// print "Find k: "+k+"\n";
if ( k>0 ) then
d1 = !(prevCopy[format("traDateStart[%d]", k)] == string ( traDateStart[i],"%Y-%m-%d" ));
f1 = Float(prevCopy[format("traAmount[%d]", k)]);
f2 = traAmount[i];
a1 = !(Abs(f1-f2)<0.0001);
iv = !(int(prevCopy[format("traPeriodCount[%d]", k)]) == traPeriodCount[i]);
t1 = !(prevCopy[format("traPeriodType[%d]", k)] == traPeriodType[i]);
print format("%s: Booleans (d1 a1 iv t1): %d %d %d %d, %.2f %.2f\n",
traName[i], int(d1), int(a1), int(iv), int(t1), f1, f2);
if ( d1 || a1 || iv || t1 ) then // DateStart, interval, type or Amounth is changed. Rewrite the sheets columns.
if ( d1 || iv || t1 ) then // All changes except amount, clear the columns for the row.
// clear the columns before start.
case
: ( today < fim ) // today belong to previous months
position = 0;
: ( today <= lim && today >= fim) // in this month, do a partial clear
position = today - fim;
default // sheets before today should not be updated.
position = dim+1;
end case
for ( j = position, dim-1)
excel_setCell ( s, dr, j + 2, "", snum);
end for
end if // end clear
cdate = traDateStart[i];
repeat
// We only update future dates.
if (cdate <= lim && cdate >= fim && cdate >= today) then
position = cdate - fim;
cellAm = float(Excel_GetCell ( s, dr, position + 2));
prevAmJs = float ( prevCopy[format("traAmount[%d]", k)]);
print format("\nUpdate %s: CellAm: %.2f prevAmJs: %.2f",traName[i],cellAm,prevAmJs );
if ( abs(cellAm-prevAmJs)<0.0000001 || abs(cellAm) < 0.00001 ) then
Excel_SetCell(s, dr, position + 2, traAmount[i], snum);
end if
end if
cdate = NextTransRepeat(cdate, fim, traDateStart[i], traPeriodType[i], traPeriodCount[i]);
until (cdate > lim);
end if // Changes to the transactions.
end if // Found previous name
end if // found datarow
end for // ctrans
end if // Found sheet
end for // mm
end for // yy
catch
// Some error occured, clean up and return error message.
excel_close ( wb );
res = move_file (BackuPath, ExcelPath ); // Move back to original name.
return last_error;
end try
// Save updated Workbook with original name
Excel_SaveAsWorkbook ( wb, ExcelPath);
excel_close ( wb );
// Update the jsCopy ellement with the new values for next update.
string jsCopy = string (js);
res = ExecuteSQL ( "UPDATE Budget SET jsCopy = :jsCopy WHERE PrimaryKey = :BudgetKey");
return "OK";
end