Jump to content


  • Content Count

  • Joined

  • Last visited

  • Days Won


ah last won the day on January 6 2013

ah had the most liked content!

Community Reputation

1 Neutral

About ah

  • Rank
  • Birthday 07/22/1977

Profile Information

  • Gender
  • Location
    Oslo, Norway
  1. Evaluate() is exactly what I needed, and more than I hoped for! Thank you so much Comment, you just saved me many hours of work
  2. This seems to be what i am looking for! I will try to implement Evaluate ( "FieldToEvaluate1" ) into the calculation, and see if it works Hopefully this means that I no longer have to make extremely long Case-statements to get the info I want http://www.dwaynewright.com/filemaker-calculations/2007/9/16/filemaker-the-evaluate-function.html
  3. I mean the actual calculation expression, not the result of it. I am simply wondering if there exists any technique to let the expression itself be dynamic, so that i might write functions etc in a text field that would then be automatically inserted into the calculation, as part of the calculation. Imagine table with the following fields: ID name description textField (a text field) calculationField (a calculation field) In textField, I might write something like: Left(table::name;4) & Case(notEmpty (table::description); table::description) In calculationField I would somehow (scripting or any other way) be able to make a calculation expression that was exactly what the field textField said. So I might be able to change the expression in calculationField by changing the content of textField. @ comment I knew I had read something like this somewhere Thanks
  4. My solution needs quite a few calculations, and sometimes they get really complex. It takes quite a lot of time to 1. Go into the Manage database 2. Find correct field 3. Go to Options >> Calculated Value 4. Make a change 5. Save and go out of Manage database 6. See if it worked this time 7 Repeat X times until the result is satisfactory Is there any way to tell the calc field to update the actual calculation from a global field? A Get() function, maybe? Something like MakeACalculationBasedOn (global_field)
  5. If something is unclear, or my info seems wrong, or my English needs some tweaking, I would appreciate your comments.
  6. How I got a list with data from MySQL in less than 1 second with a SQL VIEW - from an awful 20-25 seconds when going through relationships between multiple tables from the MySQL database. My Filemaker solution is integrated with a MySQL database that has been highly normalized for different reasons. This means that a simple list with 4 different fields can easily contain info from 4 different tables, where a couple of the relationships might span 3 tables. This made the performance in FM really awful. In my case, this easily meant 20-25 seconds to pull out a list of 50-60 records from a total of 3000 records in the parental table. Not really acceptable, and I had to find a solution to it. These were the solutions I considered: 1. Update a shadow table in FileMaker each time the list was to be made, and perform the find on the shadow table. The shadow table would mainly contain the info the list needed, pulled from all the different MySQL tables. But I wasn't sure about the performance, and I was afraid of not managing to get it implemented in all the scripts different places. 2. Make a Execute SQL query, parse the data and add it to the correct fields with loops, but this meant a lot of scripting in different places, and it got really complicated getting the right info into the right fields each time. 3. I also considered making some new relationships in FM, in the Anchor-Buoy style, but cluttering up my relationship graphs with even more things (it already contains more than 60 unique tables + some "duplicate" TOs), and probably only gain a little bit performance wise, really didn't seem too tempting. 4. Then I read about MySQL views. This can be looked upon as a saved SQL query, making it easy to get info from different tables into one view, filtered and sorted. I suppose it can also be likened a bit to a FM layout with a search and sort performed at entering the layout. In the MySQL community, these views seem to be frown upon, and performance can apparently be really awful if a view is based on an other view. But for Filemaker, MySQL views mean that the processing is already being taken care of in the MySQL server, so that FM does not have to get all the info from all the external tables, process through a bunch of relationships and then spit out the wanted result. (Without really knowing how FM communicates with SQL databases, that means that views might also be a good idea to use if you only use a few fields in a table, especially if the table also contains big comment fields or the like. But I haven't really checked it out). In my case, where all I needed was 4 fields from 4 different tables, but actually spanning a total of 6 tables (two intermediary tables as well), making a SQL VIEW seemed promising. First, I needed to go into Manage External Data Sources, Edit Data Source, and check Filter by types "Views" by the bottom. This means that views will be listed in the same way as a table from the SQL database. In FIlemaker, I have made myself a sSQL table, where each new record has a field for a SQL statement with a comment field, a name field and a Excecute OK timestamp field attached. Plus buttons for the Execute SQl script and duplicate record on the layout. (And a second SQL statement field, result field and Excecute button for SELECTing the result, or SELECT count(t.uniqueField) FROM viewName, to easily see the result). This way, I could fiddle around with different VIEW statements until I got it right. The main statement is CREATE VIEW viewName AS SELECT ... (And ALTER VIEW to make changes to the same view.) The main thing to know, is that a view does not ahve any indexes itself. In native Filemaker tables, each record has a hidden unique key. In external ones, Filemaker thus needs you to specify a field, or a combination of fields, that will contain unique values for each record. In my case, the following statement worked: SELECT .... FROM tableWithUniqueRecords t LEFT JOIN ... AND language_ID=y While a where statement did not do the trick, returning a different number of records: LEFT JOIN ... WHERE language_ID=y There are many different JOINs, so make sure you get the right one for your solution. In my database, there were some orphans here and there, both form my early stages of making a FM interface, and from an other program we had bought, that had contained some faulty SQL statements. I thus had to spend quite a lot of time washing my data, but that was probably needed anyways. For one of my views, I only needed to get one result from the child table. Just like when it might sometimes be OK to put a field from a child table on a parent layout instead of inside a portal, only showing one of the children. For the views where this is necessary, all you have to do is to add the folowing statement to the bottom of the SQL statement: GROUP BY t.unique_id Then you ensure that the view will only have unique parent records. I am sure many real developers already know about this, and hopefully have some more information on it. But I didn't really find anything, and it just saved me so incredibly much development time. Plus whooped my performance: Finding a list with 50-60 records (from a total of about 3000 parental records) in the old way, took a whooping 20-25 seconds. Changing the layout source and the fields on the layout to reflect the new ViewName table occurrence took me about 1 minute to change. Finding the list with 50-60 records now takes less than 1 second - or only 1/20 the time it used to!!! So the performance in my solution really skyrocketed with the addition of a few views to the SQL database. I would be really interested in getting some performance info on different types of views from real developers with big test databases and testing kits, but my main reason for writing this post, is to hopefully help someone else struggling with the same problems that I did.
  7. I am trying to make a products picture database. To make things as easy as possible, the preferred procedure would be to simply right click the image in a browser, go to the right container field and paste. But the image size gets really bloated: As an example, I copy a jpg-image that the browser says should be 86 kb. But when I paste it into the container field, ithe actual size shows as 209 kb (Length(container_field)/1024). If I then export the field content, the exported image file shows as 196 kb. If I, on the other hand, import the jpg-image to my computer, and then inserts it into the container field, the size shows as 86 kb in Filemaker. I was hoping to not have to download the image and then have to find it in Filemaker, the simple copy-paste was a main reason for creating this database in Filemaker. Is there a reason the image size gets so big when pasted directly from the clipboard?
  8. Hi, I am trying to find a simple solution to solve the following problem: I have a table that stores images for a product. Thus one product can have many images. To decide how they will be shown, these images need to be sorted, and have a unique sort value. Is there a simple way to find the highest number in the sort field where the product_ID is the same, and auto increment this? Also, does anyone have a good solution for changing these values in a simple way? Kind of the way tab order is set up in filemaker, so that if you change the value to a lower one, all the other images with the same product_ID will automatically increment their values with one. Thanks in advance
  9. Is it impossible to easily update a native FM-table with changes from external tables?
  10. I am still not able to make neither the otions2.fp7-database work as it should, nor my original database Any help would be deeply appreciated
  11. It dsolved the value list problem, but the relationships were not right for the rest of the database. It might be that it just needs some more buoy/anchor-relationships, but I really have no clue how these should be made. I need to get the options and values right with regards to the attributes and products
  12. I have connected FM 11 Advanced to an external database (Zen Cart MySQL) which has a quite complicated structure. For different reasons I would like to make native FM tables as well that will get updated info from these external tables (one native FM table will have info from multiple external tables). Preferably the native FM-tables should be updated whenever the MySQL-database is updated (the MySQL-database might be updated from external programs as well, not only from FM). One of the reasons is that I would like to have a local file of the most important tables and fileds that will work even if internet is down. An other is that I have a value list problem. I have the following tables: tbl_Products tbl_Product_attribute tbl_Options_values tbl_OptionsValues_to_Options tbl_Options I would like to make a native FMtbl_Options_values table that also contains the field fk_options_ID from tbl_OptionsValues_to_Options, so that I can make a value list based on FMtbl_Options_values in direct relation to tbl_Options. Anyone knows how I can make a script that will run if a record in the MySQL-tables is created or updated (preferably automatically, but I can make a button as well, or perhaps make a calculation field that triggers the script if it is changed)?
  13. I have now done some minor changes to Options2.fp7: added some name fields to the tables Options OptionsValues and Products made a relationship between the tables products and productAttributes added some records in the above mentioned tables added some fields/portals to the layouts My attempts to make relationships between the tables just made a mess, so I have not made any other changes to the relationship than the link between products and productAttributes. How should the relationship be, so that for instance the layout Products shows a portal with all the related attributes and their option / optionValue names? Options2_added_fields.zip I have now read a bit about the AB method, and it does seem to have its advantages, but I am a bit uncertain about how I would make my relations with this approach (especially the real database, with 40+ tables). Seems interesting though, I will look more into the concept. I didn't rename the fields simply because of time constraints, as I spent some hours figuring out how to copy the tables, redefine the tables in the relationship graph and import data to each of the tables. (I didn't make the original database, so i just tried to follow conventional DB-standards when making my first draft of a simple ERD)
  14. It still doesn't work for my real database, when I tried to do different changes to make it closer to your solution I have now redone some of the tables in the database to a local database, and imported the records for each table. I relookuped all the tables in the relatonship graph as well, but apparently something is not working with these relationships (unfortunately). But at least, this database might better show the structure of the solution. I am uncertain about how the relationship between attributes, options, option_values and option_values_to_options should work, and how the value list should be set up. I hope someone is able to help, this is my main obstacle, and I would very much like to know for the future as well how more complex relationships like this may be solved. local_FM.zip
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.