1. Example - Importing Timesheets from Kimai to FileMaker

Kimai is an open-source timesheet application written in PHP. It's easy to install on a web-hosting server, configure with users, customers, projects, activities, and more. However, as FileMaker enthusiasts, we often need this data within FileMaker itself. While a web-based system is excellent for tracking registrations, as managers, we require this data in FileMaker for more comprehensive analysis and integration. The solution presented here involves using an ACF function to seamlessly import all registrations into a timesheet table in FileMaker. Once the data is within FileMaker, you can use it for various purposes, including invoicing. You can also extend this example by creating new projects and customers directly from FileMaker.

1. Example - Importing Timesheets from Kimai to FileMaker
2. Uppdate for the new ACF-Plugin version 1.7.0

Setting Up Kimai:

To implement this solution, I set up an EC2 instance on Amazon, installed the ispConfig control panel, and then deployed Kimai on a web site. Kimai offers an export function that allows you to export data to Excel, which I imported into FileMaker manually. While this approach worked, it involved repetitive manual operations. Therefore, I used the ACF plugin to create a function that interacts directly with the MySQL database of Kimai.

Alternate solution without ACF

While it is possible to pull this off without ACF, I think this solution presented here gives you a very fine-tuned and direct solution to the task. Doing it without ACF would require you to buy a ODBC driver software for your Mac, and then configure it to work with Kimai. Then in FileMaker you could connect the tables kimai2_timesheet, kimai2_projects, kimai2_customers, kimai2_activities and kimai2_users as external data-sources. Then use the "import" script-step in filemaker to import the data.

Timestamp Challenges:

During this process, I encountered timestamp discrepancies between the MySQL database and FileMaker. The MySQL timestamps were in Zulu time, while the imported timestamps were in the local time zone, two hours ahead. To resolve this, I added a timestamp conversion step to the function, aligning the times accurately.

Database table in FileMaker:

Here is the Table setup for the "TimeSheet" table in FileMaker.

You can then create the neccessary relations in FileMaker and add some calculated fields to whatever neccessary. If you allready has this table, but different names you can adjust the SQL insert in the ACF function to fit whatever needed in your solution.

As in my example, I say that activity named FREE should not be invoiced, and "MEETINGS" should go with 60% timeconsume for invoicing. I have then added those two calculations.

ACF Functions:

See the source code of the ACF functions below:

Package Kimai "ACF functions for Kimai integration"

// Some utility functions
// -----------------------------------------------------

// MySQL connection function. Makes it easy to connect from other functions as well, if example is extended with other functions. 
function connect_kimai_db ()
    // set in your own values below to do a successfull connect. 
    int db = mysql_connect ("kimai host", "3306", "mysql user", "mysql password", "kimai databasename"); 
    return db; 
end 

// Do string to timestamp conversion. Using "dformat" as filemakers current date format. 

function SQL_to_timestamp ( string s, string dformat )
    timestamp ts;
    
    date d = date ( left(s,10), "%Y-%m-%d"); 
    s = string ( d, dformat) + substring ( s, 10); 
    ts = s; 
    return ts  ; 
end 

// The main function to be called from a FileMaker script
// ------------------------------------------------------
function sync_timesheet ()
    string dformat; 
    // Auto-detect date format used by FileMaker, for use in timestamp asignments. 
    dformat = @date(11;12;2023)@;
    dformat = substitute ( dformat, "11", "%m");
    dformat = substitute ( dformat, "12", "%d");
    dformat = substitute ( dformat, "2023", "%Y");
    
    // Connect to the mySQL database
    int db = connect_kimai_db();
    
    // Set up the mysql query to fetch the new rows we haven't imported before. 
    string sql = "SELECT ts.id, ts.user, ts.activity_id, ts.project_id as projectID, cust.id as customerID, 
    start_time, end_time, duration, description, pro.name as proName, cust.name as custName, act.name as actName, 
    exported, u.username
    FROM kimai2_timesheet as ts
    left join kimai2_projects as pro on pro.id = ts.project_id
    left join kimai2_customers as cust ON cust.id =  pro.customer_id
    left join kimai2_activities as act ON act.id = ts.activity_id
    left join kimai2_users as u ON   u.id =  ts.user
    WHERE exported=0;";
    
    int rs = MySQL_Query ( db, sql );
    int rrc = mySQL_GetRowCount (rs); 
    string dur, res; 
    
    array string remrow; 
    
    int i, import_count=0, h, m, s; 
    array string doneIDs;  
    
    timestamp rts1, rts2;
    
    // Process each row in a loop
    for ( i = 1, rrc )
        remrow = mySQL_GetRow (rs); 
                // print format ("\nColumns for row %d: %d", i, sizeof ( remrow ));

        // Duration in Kimai is in seconds, convert it to a TIME format. 
        s = long ( remrow[8] ); 
        h = s / 3600 ; s = s - h*3600;  
        m = s / 60; s = s - m*60; 
        dur = format("%02d:%02d:%02d", h, m, s); 
        
        // Adjust the timestamps to local time-zone
        rts1 = SQL_to_timestamp(remrow[6], dformat)+7200;
        rts2 = SQL_to_timestamp(remrow[7], dformat)+7200;
        remrow[6] = string ( rts1, "%Y-%m-%d %H:%M:%S");
        remrow[7] = string ( rts2, "%Y-%m-%d %H:%M:%S");
        
        // Create the SQL for insert into TimeSheet table
        sql = format ( "INSERT INTO TimeSheet (Kimai_ID, UserName, RegDate, FromTS, ToTS, Customer, Project, Activity,Duration, Description) 
            VALUES (%d, '%s',TIMESTAMP '%s', TIMESTAMP '%s', TIMESTAMP '%s', '%s', '%s', '%s', TIME '%s','", 
            int(remrow[1]),remrow[14],remrow[6],remrow[6], remrow[7], remrow[11], 
            remrow[10], remrow[12], dur ) 
            + substitute(remrow[9],"'", "\'")+"')"; 

        // Run the SQL
        print "\n"+sql; 
        res = executeSQL ( sql ) ; 
        if ( res == "" ) then
            import_count += 1; // Successfull insert
            doneIDs[]=remrow[1];
        else
            print "\n"+res; // Some error
        end if 
    end for
    
    // Update the Timesheet with the "exported" field set to 1, so we dont import it next time. 
    if ( sizeof (doneIDs)>0) then
        sql = format ( "UPDATE kimai2_timesheet SET exported=1 WHERE id IN (%s)", implode ( ",",doneIDs)); 
        print "\n"+sql+"\n";
        res = executeMySQL (db,  sql);
        print res; 
    end if
    
    // Close the database
    mysql_close ( db);

    // Return status to the calling script. 
    if (rrc != import_count) then
        return format ("Some records did not import well, %d imported, %d has errors, See the console output for details", 
            import_count, rrc-import_count);
    else
        $ImportStatus = format ("Success: %d timesheets imported", import_count);
        return "OK"; 
    end if
end

A button to run this:

A script haveing this script step will run your import, suppose we have a field in our table, as a global field to hold the console output, so we can see how the import perform. The $res variable should be exmined for the "OK" return from the function. If it throws some error, they could be present in a custome dialogue, to example.

Set variable [$res; ACF_Run ("sync_timesheet")]
Set field [AnyTable::Console; ACF_GetConsolleOutput]
If [$res ≠ "OK"]
    Show Custom Dialog ["Import errors"; $res]
else
    Show Custom Dialog ["Import Success"; $ImportStatus]
End If

Run this script after adding one new timesheet in Kimai, shows this console output (From the print statements in the code). Also a success message with one new timesheet.

INSERT INTO TimeSheet (Kimai_ID, UserName, RegDate, FromTS, ToTS, Customer, Project, Activity,Duration, Description) 
            VALUES (35, 'olekeh',TIMESTAMP '2023-10-12 12:45:00', TIMESTAMP '2023-10-12 12:45:00', TIMESTAMP '2023-10-12 13:00:00', 'TestCompany LTD', 'Project-X', 'MEETINGS', TIME '00:15:00','Phone meeting with Gjerterude, looking at the statistics')
end-for
UPDATE kimai2_timesheet SET exported=1 WHERE id IN (35)

Data Processing:

Once the ACF function syncs the data from Kimai into FileMaker, you can effectively manage, analyze, and utilize the timesheets for various tasks. With the data in FileMaker, you can perform invoicing, reporting, and other essential operations.

References:

• Kimai Official Website

With this solution, you can effortlessly import and synchronize timesheets from Kimai into FileMaker, streamlining your workflows and enhancing your data management capabilities.

2. Uppdate for the new ACF-Plugin version 1.7.0 ↑

The SQL routines for both mySQL and FileMaker SQL have been rewritten, and we have in place a new exciting placeholder concept for the SQL sentences making it easier to craft SQL's, and also the INTO definition to allow SQL queries to hand the result directly into arrays, making the source code far more readable and with higher quality. See the example of the function I modified to use those concepts. It is backward compatible, but we need testing and this was a good candidate for test:

function sync_timesheet ()
    
    int db = connect_kimai_db();
    
    // Declare the data-arrays for the mysql query
    array int       aKimaiID;
    array timestamp aStartTime; 
    array timestamp aEndTime;
    array int       aDuration; 
    array string    aDescription; 
    array string    aProject; 
    array string    aCustomer; 
    array string    aActivity; 
    array string    aUserName; 
    
    // declare the variables for insert. 
    int KimaiID;
    string User, Customer, Project, Activity, Description; 
    date tsDate; 
    timestamp from, to; 
    
    // The SQL
    string sql = "SELECT ts.id, start_time, end_time, duration, description, pro.name as proName, cust.name as custName, 
    act.name as actName, u.username
    FROM kimai2_timesheet as ts
    left join kimai2_projects as pro on pro.id = ts.project_id
    left join kimai2_customers as cust ON cust.id =  pro.customer_id
    left join kimai2_activities as act ON act.id = ts.activity_id
    left join kimai2_users as u ON   u.id =  ts.user
    WHERE exported=0
    INTO :aKimaiID, :aStartTime, :aEndTime, :aDuration, :aDescription, :aProject, :aCustomer, :aActivity, :aUserName";
    
    string rs = executeMySQL ( db, sql );
    if ( rs != "OK") then
        throw rs; 
    end if
    int rrc = sizeof ( aKimaiID ); 
    string res; 
    time dur; 
        
    int i, import_count=0, h, m, s; 
    array string doneIDs;  
    
    bool found; 
    timestamp rts1, rts2;
    
    for ( i = 1, rrc )
         
        s = aDuration[i]; 
        h = s / 3600 ; s = s - h*3600;  
        m = s / 60; s = s - m*60; 
        dur = format("%02d:%02d:%02d", h, m, s); 
        
        // Prepare the values
        KimaiID = aKimaiID[i]; 
        User    = aUserName[i];
        tsDate  = date (aStartTime[i]); 
        from    = aStartTime[i]; 
        to      = aEndTime[i]; 
        Customer= aCustomer[i]; 
        Project = aProject[i]; 
        Activity= aActivity[i]; 
        Description = aDescription[i];

        // Insert into FileMaker table. 
        sql =   "INSERT INTO TimeSheet (Kimai_ID, UserName, RegDate, FromTS, 
            ToTS, Customer, Project, Activity,Duration, Description) 
            VALUES  ( :KimaiID, :User, :tsDate, :from, :to, :Customer, :Project, 
                :Activity, :dur, :Description)"; 
            
        res = executeSQL ( sql ) ; 
        if ( res == "" ) then
            import_count += 1; // Successfull insert
            doneIDs[]=string ( KimaiID);
        else
            print "\n"+res; // Some error
            return res; // throw res; 
        end if
    end for
    
    // The insert is complete. Now, update the Kimai timesheet table 
    // with "exported" to true, so we don't pull those records next time. 

    if ( sizeof (doneIDs)>0) then
        sql = format ( "UPDATE kimai2_timesheet SET exported=1 WHERE id IN (%s)", implode ( ",",doneIDs)); 
        
        res = executeMySQL (db,  sql);
    end if
    
    mysql_close ( db);
    
    if (rrc != import_count) then
        return format ("Some records did not import well, %d imported, %d has errors, See the console output for details", import_count, rrc-import_count);
    else
        $ImportStatus = format ( "Success: %d timesheets imported", import_count);
        return "OK"; 
    end if
end