Hi all,

It's me again....

I've temporarily gotten around my issues with "required value" fields in SQL data by adding a default value to all blank fields, however I have now found another error.

I have a table of customers (BP_Details) which is retrieved from SQL server view using a DSN.

I then have a local Filemaker table ("BPAddon") which I've linked to the BP_Detials table using a unique customer ID field.

I wish to populate the records in the addon table via the Cusomter layout to supplement the data from SQL.

As there are no records in the addon table I've selected "allow creation of records in this table via this relationship" on the add on table side.

I can browse records fine, however as soon as I add data in a field on the addon table I receive an ODBC error: "You cannot specify scroll locking on a cursor that contains a remote table".

Am I doing something wrong in the relationship?

 

Thanks for any help!

 

32 minutes ago, Cerbera said:

I then have a local Filemaker table ("BPAddon") which I've linked to the BP_Detials table using a unique customer ID field.

I wish to populate the records in the addon table via the Cusomter layout to supplement the data from SQL.

As there are no records in the addon table I've selected "allow creation of records in this table via this relationship" on the add on table side.

 

I don't follow why are doing this, why not work directly on the ESS table?  That's what it is there for...

I'm not sure that ESS respects the auto-creation relationship.

Hi Wim,

I thought that the only fields you could add to an ESS table are calculation and summary ones?

Basically the customer data from SQL is the likes of company name, phone number, etc.

I want to add fields for notes, last visit dates, etc.  I didn't think I could add them onto the ESS table? It's the first time I'm doing this though so it's very likely i'm missing something... probably a lot of things! 

Ok, fair enough.  It's a little uncommon because typically you'd expect the ESS tables to have the fields you want.

Change your workflow to explicitly go to the ESS table and create the customer record when you need it.  Don't rely on the auto-create part of the relationship.

Thanks Wim,

Maybe you're right that I need to look more into scripting and workflows?

Can I clarify whether my general use of an ESS table is correct in this instance where I'm importing customer data? Below is what I've done for customer contacts:

I've setup a contacts table related to the ESS table and on the customer layout I've added both a portal and an "add contacts" button with a new contact popup and have linked the name field in the portal to popup any existing contact record when clicked.

If however I click on a field and change it in the portal, e.g. the position, I receive the same ODBC error. I can avoid this by remove the edit in browser mode, but I thought it would be possible to edit related records on the layout of an ESS table?

The other way around I thought was to have a new FM customer table and associated layout and make the ESS table data appear on that, but have all of the contacts, etc. related to the FM local table.  But then I'd need to setup a record in the customer table for every record coming through onto the ESS table, and that table will be dynamic as new customers are added in SQL.

Maybe I was using ESS wrong/ had the wrong expectations?

Edited January 6, 201610 yr by Cerbera

Sorry to open an old post, but I am experiencing the exact same issue and was wondering what method you took to resolve it.