I am new to FM, coming to it from a SQL Server/Oracle/Access background.  I have created a database with layouts and while there is certainly much more for me to learn it generally is working as I desire it.  However, I need to perform a task that I do not understand how it would be done in FM.  The example below is not my actual problem but does exemplify my core issue and hopefully is easier to understand than if I described my project.

I have a table of Users, a table of Entitlements, and a join table of UserEntitlements.    I want to execute SQL that, for a given User, will read from the Entitlements table and insert rows into UserEntitlements for this user.  The query will read from Entitlements, insert a row for every entitlement for this user and, based on data in Entitlements take, will mark some of the entitlements as 'active'.

Ideally, every time I created a new user this SQL would be called  (I'd have it as a trigger in a SQL or Oracle database, or I'd have a batch process that ran frequently looking for new users).   Basically, as a new user is created I want to create their entitlements profile and activate standard entitlements.

I don't need/expect a full answer - was just hoping I could get pointed in the right direction: is 'scripts' the area I need to explore?  Is there a SQL capability to FM?  I have not found it if there is.

Thank you for any assistance you can provide,

-Neil

 

30 minutes ago, kisertn said:

Basically, as a new user is created I want to create their entitlements profile and activate standard entitlements.

In Filemaker you would a use a script for this. For example, your script could store the newly created user's ID in a variable, go to a layout of Entitlements, find the records of interest, import them into the join table and assign them to the new user.

IMHO you should not create join records that do not carry any information - but you could, if you used Show All Records instead of a find.

Internally, Filemaker implements a limited set of SQL commands - basically, only SELECT is supported, so you cannot use it to create new records. Note that this does not apply to a situation where Filemaker is being used as a data source for another application via ODBC.

 

Edited May 12, 20169 yr by comment

There are a couple of plugins for FileMaker that enable SQL CRUD.

29 minutes ago, comment said:

IMHO you should not create join records that do not carry any information - but you could, if you used Show All Records instead of a find.

Yes, agreed.  I will be carrying additional information in this join table - a lot of additional information actually.   I just wanted a quick way to populate it first so that a user does not have to manually add these entitlements every time.

Scripts it is then.   I saw this feature and looked at it - but it looked rather foreign to me on first glance.   That's why I wanted to inquire before diving into it - to make sure I am on the right track :-)      Thanks!

Welcome to FM Forums!