Jump to content


  • Content count

  • Joined

  • Last visited

  • Days Won


rwoods last won the day on December 22 2017

rwoods had the most liked content!

Community Reputation

15 Good

1 Follower

About rwoods

  • Rank
  • Birthday 07/09/1973

Profile Information

  • Title
  • Gender
  • Location
    Nottingham, UK

Contact Methods

  • Website URL

FileMaker Experience

  • Skill Level
  • FM Application
    16 Advanced

Platform Environment

  • OS Platform
  • OS Version
    OS X 10.12 Sierra

FileMaker Partner

  • Certification
  • Membership
    FileMaker Business Alliance
    FIleMaker Platinum Member

Recent Profile Visitors

2,526 profile views
  1. Hi Jim Well you kind of answered your own question, they are different. "Do not store calculation results - - recalculate when needed" tells FileMaker to recalculate the result whenever it is displayed or needed for another calculation. It helps to make sure that results are not retained when values in related records (in particular) change. FileMaker doesn't usually notice these related-table changes if you don't check that box. "Indexing - None" - is a separate consideration, and I would say you should not very often need to select this. It tells FileMaker not to index the field, which means that searching on that field will be much slower potentially. It can be useful to save disk space if you have a field on many, many records that you never want to search on, and you don't want to waste the disk space that an index uses. I would say that for a field with "Do not store calculation results - - recalculate when needed" then FileMaker will not store an index anyway, as you have explicitly told it to recalculate that field whenever required, which would include a 'Find' being performed on that field. Hope this helps?
  2. WebDirect doesnt match

    Hi Ponderosa The experience you are having is typical of WebDirect renderings of FileMaker Pro layouts. Not everything behaves as you would expect. When you are designing a layout that you expect will be used in WebDirect, then the advise is test, test and test again as you develop your solution, and use multiple browsers on multiple platforms to test with. FileMaker explain some of the limitations of WebDirect at https://fmhelp.filemaker.com/docs/16/en/fmwd/index.html#capabilities , however some of the issues you mention are not listed there. Part of the issue is that fonts behave differently in web browsers that they do in FileMaker Pro. You cannot guarantee that fonts will render at the same size exactly, or even that fields will be exactly the same size with the same positioning and size. Keep your WebDirect layouts simple, make sure you use themes (rather than styling individual layout items 'locally'). It is often advisable to have different layouts for WebDirect than the layouts intended for use in FileMaker Pro. It is a shame it has to be that way. You show as using FileMaker 15 in your profile, improvements were made in FileMaker 16 Server for WebDirect, and you may find that some of the problems go away if you upgrade. I hope this helps and you now know that you are not alone!
  3. Hello again Yes I believe that by 'touching' the 'Date of Hire' field you will cause FileMaker to re-evaluate the calculation as update the Years of Service field. You may want to put a 'Show All Records' in before the GTRRP [First] step, just to make sure all the records are being used. Otherwise, looks good!
  4. Hi Chris Your calculation is probably showing as 'Stored' if you look at it in the Manage->Database window. So FileMaker stores the result to make it indexable and quicker when you are performing a find or calculation based on it. However, because you are using Get ( CurrentDate ) in your calculation that stored value becomes out of date every day. FileMaker will re-evaluate the result when one of the other fields that are referenced changes (as you pointed out), but sadly FileMaker doesn't really notice that the result of Get ( CurrentDate ) has changed! To fix this, you need to make the result of the calculation 'unstored' (by clicking 'Options' in the Manage->database window for that field). This way FileMaker will re-evaluate the field whenever it is on screen or required for a find or whatever. The down side of this is that it will be slower to view and work with the result of the calculation, which can become a problem if you have a lot of records. Because you are using a 'Calculation' field-type, there is no real way to have both the performance of a stored result AND the convenience of the calculation field being always-updated. If this were my database, I would change it to be a 'Text' field, and use the 'Auto Enter Calculated Result' option, using exactly the same calculation as you have above. Then, I would have the database run a script on startup (once per day only) that set's the field correctly for todays date using the same calculation. FileMaker would handle any intra-day updates if any of the other fields referenced changed. You then have a 'stored' field, which gives you the best performance, and the result will always be correct. Make sure to uncheck the box that says 'Do not replace existing value of field (if any)' in Manage->Database->{yourField}->Options if you try this technique. Hope that helps, it's a common question, and the topic of different techniques for calculated fields is well-trodden on this forum!
  5. Filtering portal based on 2 fields

    Hi Grimfool If you are using the 'Filter' option in the 'Portal Setup' dialog, then you can just extend your filter calculation to include two fields, for example :- ( relatedTable::qty > 0 ) or ( relatedTable::qtyRequiredFlag = 0 ) where qty and qtyRequiredFlag are both fields in the table that he portal is based on. Does that answer your question?
  6. Good morning from the UK! Many performance issues are caused by the use of unstored calculations. (this may or may not apply to you!) Have a look at the field that your summary field is totalling. If that field is of type 'Calculation', and FileMaker tells you that it is 'Unstored' when you view it in the 'Manage Database' dialogue, then that may be the cause of the slowness. FileMaker calculation fields are very convenient, but can be slow if the calculation is using fields from a related table, or an SQL statement. In that case, FileMaker has to recalculate that field every time your solution needs it, and the summary field would need it for every one of your 138K records to perform the summary, hence it is slow. To resolve this you should try to have that field be stored, and generally you do this by changing it to a number (instead of a calculation). You then manage the contents of this field by updating it whenever it's value may have changed. You have to look at the buttons and scripts in your solution and spot wherever that field may need to be updated. Another technique is using the 'Auto Enter Calculated Result' (AEC) option. Change the field to a 'Number' instead of a 'calculation' and put the same calculation as you currently have in your 'Calculation' field into the 'Auto Enter Calculated result' box. You then have to manage how FileMaker keeps that field up to date, since it won't do it automatically when the Auto Enter Calculation looks across relationships. FileMaker will only update an AEC when one of the fields that it references THAT IS IN THE SAME TABLE AS THAT AEC FIELD is changed. If that is your preferred option the you need to use a Let statement in the calculation that references a new 'Trigger' field in your table, and 'touch' that trigger whenever the value needs to change. If that is your preferred option let me know and I will explain more! It's a little bit complicated.
  7. Can't show related portal records

    Well Rob, feel free to ask for any clarification on join tables. They are used when you have a MANY to MANY relationship. That is, in your case, where one product can be in many bundles, and each bundles can contain many products. You cannot simply join product and bundles using a direct primary key -> foreign key relationship (which is a 'normal' one to many relationship), because then one product could only feature in one bundle, or one bundle could only feature one product. Your join table that I created has one record for each time the Product and Bundle tables need to be joined, that is when a product features in a bundle. Join tables always have at least two foreign keys, one relates to a product in your example, and one to a bundle. Join tables should also have a primary key, allowing you to specifically reference that join table record when you need to. Your join table should have fields that are specific to that instance of a product being in a bundle. For instance, the 'qty' field. You want to be able to have a different quantity of a specific product in each bundle. You might want one beef patty in a standard burger, but two beef patties in a quarter pounder burger. I hope this helps, but feel free to ask other questions.
  8. Related or Filtered Records in Portal

    Oh OK, right well I've updated again. Had to just create a new Value list that shows the correct staff. You can see it in the list of Value Lists. I also renamed all the table occurrences in the graph using standard anchor-buoy naming conventions so they weren't all called Office 2, Staff 2 etc. Assets.fmp12
  9. Related or Filtered Records in Portal

    Hi Ash I've enclosed a revision of your sample file. I added a global field in your 'Asset' table to act as the selector, to decide which 'Assignments' to view. Then I added a filter to your portal that only show the records that match the current selection. You can't have it based on the 'idOffice' in Assets since that changes which office the asset is assigned to, which is wrong, hence needing the new global field. I also added a script trigger to my new selector field to refresh the window when the value changes, to force the portal to refresh which records it shows. Also, I had to add an Auto-Enter 'Lookup' on the 'idOffice' field in 'Assignments' so that each assignment knows which office the asset it references is located in. This was required to do the portal filter. Note that portal filters only change what is viewed, it doesn't actually change which values are found by the relationship which shows the portal. Therefore if you did any calculations such as Sum (Assignments::qty) in the Assets table, then it would add up all the related records, NOT just the ones shown in the portal. You'd have to change the relationship itself if that was a problem. Hope that helps and good luck as you learn! Assets.fmp12
  10. Can't show related portal records

    No problem, I got a bit carried away once I started on it, I love how well join tables work once you get your head around them!
  11. Can't show related portal records

    Hi Rob You need to rethink your tables in my opinion. Normally it would be setup like this :- PRODUCTS (one record per product) id_pk productName cost price etc BUNDLE (one record per bundle, rather than multiple per bundle in your setup) id_pk bundleName BUNDLE_ITEMS (one record for each occurrence of a product in a bundle, it's a JOIN table) id_pk id_product_fk id_bundle_fk qtyInBundle So BUNDLE_ITEMS is a join table between products and bundles. You have one record in the BUNDLE table for each bundle, and then create join table records whenever a product features in a bundle. I've amended your sample file with this setup, you can add products to bundle's just by choosing from the popup list! I also added a qty field so you can have multiple qty's of a product in a bundle, and totals for each bundleItem, and a grand total for each bundle. Enjoy. Bundle.fmp12
  12. Launch script from calculation

    It may be to do with the brackets and comparing to an empty string. Does If ( not ( IsEmpty ( BtlBarcode ) ) and ( IsEmpty ( LiquorCatalog::Barcode) ) work better?
  13. New record very slown

    Hi Reynir If changing the layout makes no difference, then I suppose it must be something in your field definitions that causes FileMaker to do a great deal of work each time a new record is created. It would be possible to put something in a calculation field (or in the 'Auto Enter Calculation) box that was recursive, or tried to perform complicated Sum calcs on all existing records (maybe the found set). It sounds like it is getting exponentially worse with more records in your DB, so I can only speculate it is something in your Field Definitions. If you posted the file maybe we could take a look for you and see if we get the same issue? It must be something fairly simple.
  14. Hi Genelia This is tricky area. FileMaker will only export as a single Excel sheet on a spreadsheet, and you have no control over the formatting (i.e. all the columns are narrow, headings are restricted to the names of your fields) etc etc. Plus you can't include any nice formatting, such as colours, bold text, spacing etc. FileMakers features are really just a raw data export, and the formatting has to happen elsewhere. If you want more control over the Excel output then we have found the best way is to generate an HTML file within FileMaker with your data formatted exactly how you want it, and then use a web API to convert the HTML file to Excel. That way you get a file formatted exactly as you wish. You will need to see if they can also do two or more sheets in a document, we've never tried that. The API we use is https://grabz.it/htmltabletofile.aspx . There is a free trial and free tier. You could also look at https://docraptor.com/how-it-works (another API) or https://www.coolutils.com/HTML-XLS-CommandLine which is a Windows command line interface.
  15. Hi muskee You need to add accounts to Database B with exactly the same username and password as you have in Database A. That way, FileMaker will try to open Database B with whatever name/password was used to open Database A and show the data without asking for login. If there is not a matching account in database B, then you will be asked for login credentials. There is no way to tell FileMaker to use a specific name/password when opening a file that is referenced in 'Manage -> External Data Sources', you have to do what I describe above.

Important Information

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