i need to know which field is marked as unique in table. how can i know that.?
FM allows many fields in one table to be set as unique.
Validation attributes of field can not be programmatically queried.
If you need to know this info about a field you should devise and adhere to a field naming convention.
Its always a huge benefit to clearly identify primary keys in the name.
if you establish and use a field name convention you can get a list of fields with ExecuteSQL() or FieldNames and parse the results.
As far as I know, there's unfortunately no proper function for getting information on the field validation (like unique) at runtime, using a scrip tor calculation function. What you can do, using FileMaker Pro Advanced, is create a Database Design Report (DDR), and then either look through the report manually, or - better - use a paid software like BaseElements, Inspector Pro or FMPerception to get more user-friendly display of the information in the DDR.
Super interesting question.
I wondered if the DBMetaData in FMP's JDBC driver would expose the Unique setting for a field, but I couldn't find anything to help.
For example, for any given field in a JDBC ResultSet, here are the available methods to extract DB MetaData:
I did not see a way to get the Unique attribute for a field. I'm sure FMI could add this method to the list above.
Why both you don't mention Manage > Database ?
I think the OP wants a programmatic way to look this up.
Thanks coherentkris i will try the procedure you have mentioned above. i hope will find a clue.
my question is different and don't need to mention any database. let me elaborate it again for you. let suppose i have 50 tables and every table is having unique 2 to 3 fields so how can i know that which field is unique.
solution 1. go to manage database and check it manually by looking every table (which i don't want to do that)
solution 2. A programmatic approach you just need to call a function if there is any which can tell you info about the given table and there is no such function in FM so far.
But why would you need to know this at all?
There are also database design reports and third party tools that use the data from them...
I print all the tables to PDF and then open. These can be searched. I’ll try to create an example for the search criteria.
It would be nice to get this info with functions. But IDK if the SQLs even do that (I’ll verify). I usually run a SQL dump (of the schema only). That’s a text 'equivalent' of the print all tables in FileMaker.
Sent from miPhone
There is a specific error code 504: Value in field is not unique, as required in validation entry options. You could imagine using this to test, and/or keep a log of, which fields have unique validation. There's a bit too much overhead in this approach to my taste, but there may be cases where it could be useful.
In general, it would probably be better to use this in the error handling. Whether that will work for arslanniamat obviously depends on the details of how and when the information about a field having a unique validation is required.
For a proper solution, we would need new functions in FileMaker to get the validation information from the field definitions at runtime.
coherentkris - I am not a big fan of using naming conventions in this way. While it's a workaround that can perhaps be "better than nothing", or even work quite well if everybody who changes field definitions always does the right thing, there if course no guarantee that the name of a field actually reflects the current properties of the field. So this actually gives a false feeling of security.
Example print to PDF from Manage Database:
Info on SQL dbs:
The UNIQUE constraint ensures that all values in a column are different.Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.A PRIMARY KEY constraint automatically has a UNIQUE constraint.However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
The UNIQUE constraint ensures that all values in a column are different.
Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint.
However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
Retrieving data ...