filemaker pro 10 - update selected row in one table with value from other table

Question

Hi I'm new to filemaker - liking it, but taking a while to get used to it

I'm creating a solution where customers checkin with their ID card. The first time they do this, I need to link their card to their user record.

I have a user table, and want to click a button on the layout for this table to set the ID of a card. The ID of the card is simply sitting in a different table.

So I just want to get the most recent row in the checkin table

take the cardid from there

update the current user's card field to that value

I'm using mysql as a backend for filemaker, to imporve syncability to the web etc.

I know who to do this with SQL - its

update users set cardid = (select cardid from checkins order by checkintime desc limit 1) where id = [current user id];

so I was thinking the executesql from filemaker would work, but I can't figure out how to pass in the [current user id]

I'm so close to getting this working I can taste it, but stumped! Any hints would be appreciated. thanks.

Answer 1

OK I figured this out finally. Will put the answer here in case it helps someone.

Was simple, just need to use the Calculated SQL text option in the Execute SQL script step: this is what worked "update users set felicaid = (select felicaid from checkins order by checkintime desc limit 1) where id = " & users::id

Also needed a refresh window with flush SQL data option enabled step after that to update the UI