2 posts in this topic
Required Values in field from SQL Data
Hello all, Happy New Year and all that!
I'm back at it already and have hit my first hurdle with using ESS (now the DSN is working that is!)
I have several SQL views on a read only DSN which I'm pulling through to FM. The first view has data relating to our customers and this is pulled from our ERP, which is the system of record, hence the data is read only.
The problem I have is that there are four fields that are showing as "required values". The first 3 are indeed required and will always be populated, the fourth (ID of parent account) however is only likely to be populated about 10% of the time in the ERP data.
Of course when I edit any local fields which I've added to a layout using a related FM table, the solution validates the data from SQL and complains that the parent account ID field is empty, but of course I can't fill this in as the data is read only and set to can't edit... vicious circle! -worryingly I originally typed viscous circle which is a completely different thing!
Can anyone advise why this field is insisting on being populated - does it come from the SQL data, e.g. some kind of primary key? If I remove the "required value" bit from the field in manage database it just reappears.
Thanks for any help you can offer.
SQL Server 2008 ESS - Only displays system tables
This is my first post in the forum - one of no doubt many to come!
I'm working on my first solution and as part of that I'm pulling in data from a SQL Server 2008 database which will be augmented by file maker tables.
I have been able to locate the DSN and setup the External Data Source, however when I try to add the table in the relationships screen I can only see the system tables in the SQL database - not the views and tables that I have setup.
Does anyone have an idea of a possible solution for this?
Running FileMaker Pro Advanced 14 on Windows 7.
MS SQL in Azure and ESS
I am trying to connect to SQL database hosted in MS SQL server, located in Azure.Â
- Win 2012 Server, FMS 13 Advanced,
- ODBC Driver:Â ODBC Driver 11 for SQL Server 11.0.2270.0 (64-bit)
- FMSA 13, tried MAC & WIN
Setting up System DNS without problems, test completedÂ succesfully
Added this DNS as external DataSource in my DB.
When trying to add TO to Relationships graph , get the message:Â
SQL Database & All its Tables and Views shows in "Specify Table" Dialog
"This action cannot be performed because the required table is missing"
I though that the credentials for SQL Database does not allow to see tables/ edit data. But the SQL database guy said that I have all rights there. At the moment, I can't check it
I am new to MS SQL connection, but done quite a lot work with MySQL databases.Â
I would be happy to get some hints what to do. The database guy in SQL side is not very helpful.Â
Connection problems between MySQL and FM databases
I have trying to combine data from a MySQL source with my local FM database using a shadow table with ESS connector. Basically I have done a Point Of Sale (POS) that gets the data from a MySQL database (it contains the information about the products, prices, stock, etc). I have two problems:
1. I have done an script that connect to this shadow table when I insert a barcode for a product and extract the information about that product. If I open the FM solution and initially run the script, the process is too slow but if I go firstly to the shadow table and then I run again the script, in that case the process is faster. I have solve this problem using and script that goes briefly to the shadow table (when I open the application) before returns to the main layout. Is there other alternative more elegant?
2. Certain products are duplicates in the database and have different barcodes. If I insert one of these barcodes, a floating panel is shown, an script using ExecuteSQL query is run to show only this products (from shadow table). To do this query, I need to go to the shadow table and do a search in Find Mode and then run again the ExecuteSQL query.
I think that both problems are related and I suppose that I need to optimize the connection to MySQL shadow table. Could anyone help me to enhance it? Do I need to include any other step in the scripts to establish or maintain the connection to the MySQL database?
Thank you very much,
Testing ESS availability in a Server Scheduled Script
By Max Heller
Using Filemaker 11 Server.
I have a server script that is going to several ess layouts based on ORACLE views over ODBC, and exporting the data to excel files in the Documents Folder on the server. A separate database has a scheduled server script to import the data into local Filemaker tables that mirror the ORACLE structure.
This works great when the ORACLE server is online, but when it goes offline (usually without notice), the script just hangs (it eventually times out at the time limit I have set on the server).
On the client I can do an Open File script step and check for an error before going to the ESS layout, but that script step is not supported on the Server. Is there some other way to test for availability before going to the layout to prevent it from hanging?