Jump to content


  • Posts

  • Joined

  • Last visited

  • Days Won


rwoods last won the day on July 13 2019

rwoods had the most liked content!

1 Follower

About rwoods

  • Birthday 07/09/1973

Profile Information

  • Title
  • Gender
  • Location
    Nottingham, UK

Contact Methods

  • Website URL

FileMaker Experience

  • Skill Level
  • FM Application

Platform Environment

  • OS Platform
  • OS Version
    OS X 10.14 Mojave

FileMaker Partner

  • Certification
  • Membership
    FileMaker Business Alliance
    FIleMaker Platinum Member

Recent Profile Visitors

5,157 profile views

rwoods's Achievements


Collaborator (7/14)

  • First Post
  • Collaborator
  • Conversation Starter
  • Week One Done
  • One Month Later

Recent Badges



  1. Hello FileMaker will ask for the related files whenever it thinks it *might* need them. When entering the sort dialog there may be related fields on the current layout, so it will need to display their name in the list of fields that you can sort on, so it will need the files. Also, if any of the fields that could be sorted on are calculated and need the related files, it will ask for them. There may be other reasons why FM's internal engine wants to check the related files. You will have to experiment, perhaps you can script a layout change to a layout that only has fields on it that don't need the related files, and maybe that will help. Anything that removes the possibility of needing to check the related files would help. Or you may find that FileMaker will just do this whether you like it or not.
  2. No problem, I'm glad my name will live on somewhere in the world forever (or as long as your file survives!)
  3. @comment Yes, you may be correct, the ExecuteSQL on the whole table may be inappropriate, but could be modified to restrict the matching records. It would be good to check it only matched a single record too!
  4. Hello OP I think your issue here is you are thinking 'What would a human do if they had a list and needed to find some data from one of the rows'. The human would run their finger down the list, stop when they see the matching name, and then read across to find the ranking. In a database we wouldn't do that as it is slow an inefficient. If you really did want to do that you could use a loop like this Set Variable [ $$PlayerToFind ; "Steve Jobs" ] Go To Record/Request/Page [First] Loop #Check if we found the correct record and leave this loop if we have Exit Loop If [ table::playerName = $$PlayerToFind ] #Else carry on looking at the next record Go to Record/Request/Page [Exit after Last:On] End Loop #We must be on the correct record now, that matches "Steve Jobs" Set Variable [ $$Rank ; table::Rank ] #You'd need to handle the situation where we exit the loop without finding the right name However, much better would be just to ask the database for the value, something like this using ExecuteSQL Set Variable [ $$PlayerToFind ; "Steve Jobs" ] Set Variable [ $$Rank ; ExecuteSQL [ " SELECT rank FROM tableOccurence WHERE name = ? ; "" ; "" ; $$PlayerToFind ] ] This doesn't involve the long, slow looping through record, and also doesn't change the currently selected record which can be helpful.
  5. Hi Hurlz Someone else here may explain this better, but just in case you are sitting there waiting for a response, here goes! FileMaker doesn't allow two 'paths' on the relationship graph to link any given two Table Occurrences (TO's) as it would lead to two different ways of calculating which records are 'related'. Therefore FileMaker will create new TO's automatically when it needs to prevent this, and it will call them things like 'Permits 2'. From your point of view, this is not great as the naming of that TO doesn't give you any clue as to what to expect from the relationship that leads there. Most FileMaker developers use some form of the anchor-buoy method of creating and naming TO's. Very simply, this means creating a TO for each source table that you will use as the basis for any layouts based on that table (the 'anchor'). You then create TO's for any related tables that needs to be referenced from that source table, and you name them in a meaningful way (the buoy's). e.g customers_SALES_forThisCustomer would be a good name for a TO based on your SALES table, that is used exclusively for when you wish to refer to, or display, sales that relate to a given customer. You would never base layout who's source table is the SALES table on that TO though, you would have a separate TO for that (probably called just 'Sales'). And you would never create a relationship from any other TO to this 'buoy' TO, as that would have a confusing meaning. This method means you have a 'starting' or 'anchor' TO for each of the tables on which you need to base your layouts, then lots of 'buoys' linked those anchors. You do end up with lot's off TO's that are based on the same table, but this is a compromise of the anchor-buoy method that has been discussed a great deal. So if you wish to follow this method then you need to create some 'Anchors' for your tables, then create (or rename existing) 'buoy' TO's to give meaningful relationships from you anchors. Did that actually come across as simple??!!
  6. Hi Mike You need to leave my calculation mostly as it is, and just replace the 'yourStringField' part with the name of the field in which you have the data ("101_010_0010"). Something like this :- GetValue ( Substitute ( table1::field1 ; "_" ; "¶" ) ; 2 ) I'm not sure how else to describe this! A number field in FileMaker would just hold a numbers, like 12 or 10.9 You cannot store any other characters, except 0-9 and your decimal point character. Because your text has underscores then it cannot be a number, it is a string of characters and therefore belongs in a text field.
  7. Hi Mike What you have is some text, rather than a number, to begin with. Hopefully that string '101_010_0010' is in a Text field, not a number field! I'm not sure if you really mean 'paste' (as in 'copy and paste'), or whether you are trying to extract it using a calculation or a script? The other question I would need to ask is :- Is the format of the string always the same? i.e Is it always the information between the first and second 'underscore' that is required. If so, the the following FileMaker calculation will find that for you (there are many other ways of achieving this) GetValue ( Substitute ( yourStringField ; "_" ; "¶" ) ; 2 ) This works by converting your string to a carriage-return-separated list of the separate sections (the parts between underscores), and then taking the second one from the list. This means it will work regardless of how long each section is. It converts it to :- 101 010 <- and then selects this one 0010 You could use this calculation in another field definition or in a 'Set Field' line in a script. Let me know if you needed something different.
  8. I expect the content of your Excel file is unusual. Perhaps the space character between John and Doe is not a normal space, but something else that FM doesn't recognise.
  9. Hello Sorry to be slightly unhelpful, but what you have there is a mathematical problem, not a FileMaker problem. First you need to solve the issue of how best to utilise your raw materials to fulfil the orders, and then work out the best way to do it in FileMaker. Maybe you do this to try some ideas:- Create a table of raw materials with fields :- id (text), startLength (num), unusedLength (num), ordersFulfilled (text, list of id's) unusedLength starts with the same value as startLength. Create table of orders with fields :- id (text), requiredLength (num), isFulfilled (num boolean, starts FALSE) Sort your table of raw materials biggest first, and then LOOP through them in FileMaker For each raw material in the loop, find the biggest order that can be fulfilled from it from the orders table, looking at only the orders where isFulfilled is FALSE. Mark the chosen order as isFulfilled = TRUE. Add the id of the fulfilled order to the ordersFulfilled list on the raw material, in case we want to refer to that later. Subtract the length of the order from the unusedLength field of the raw material. After finding the biggest fulfillable order, then find the next biggest and do everything above again. When you cannot find any further orders that can be fulfilled (as they are all too big, or your raw material has been used up (unusedLength hits zero) ) then stop and go back to the beginning of the loop. You now carry on to the next Raw Material record, and do the same again. When finished, you will have fulfilled all the orders you can (they will be marked as isFulfilled = TRUE), and you will know the wastage from each raw material (it's whatever is left in the unusedLength field). The problem you have is optimising this. The routine above will come up with a solution, but not the best solution. Unless you can find an algorithm on the internet that elegantly solves this, then you need to use some trial and error. Run the above system, and then count how much raw material you are left with, that is the wasted raw material (SUM the unusedLength field from all raw materials). You may also have some orders that you couldn't fulfil, add up the total unfulfilled order lengths. Now remember those two figures somewhere, and re-run the loop, but this time fulfil the smallest order first for each raw material, not the biggest, and then try the next smallest until you run out of raw material. After doing that, have you got less wastage and/or more orders fulfilled? Remember that result too. I would then use a random number generator to try it again with randomly selected orders (instead of sorting them in any particular order), rejecting them if they are too big, and seeing what wastage you get then. FileMaker can do all this for you, but it is the 'algorithm-inventor' within you that will be the biggest help!
  10. Hi John Filemaker stores times as seconds from the programmers point of view, so you can just add a number of seconds and FM will do the arithmetic correctly. 3 hours = 10,800 seconds So 10:00AM + 10800 = 1:00PM newTimeField = oldTimeField + 10800
  11. Hello Relationships need indexable (or global) fields on both sides of the relationship, so the problem will be that c.OrderStatus is not indexed. And you can't index it, because it is a non-stored calculation, probably because it references fields from a relationship itself. You need to make c.OrderStatus a stored field. Do this by either :- 1) Making it an Auto-Enter calculation (rather than a calculated field) as this can be indexed. FileMaker will not update this Auto-Enter calc automatically whenever the related fields it references change their value, so you will need some sort of trigger in there to force it to update. 2) Maintain the value of c.OrderStatus in your scripts (or layout/field Script Triggers), don't have it be calculated at all. It is a pain that you can't make relationships be based on unstored calculations, but I suppose it is for performance reasons. Relationships need to work fast, and unstored calcs can be very slow.
  12. You can also tell if your fields are within your portal by using the 'Objects' pane on the left hand side of the FM17/18 screen in layout mode. Single-click the portal in the layout to select your portal, then look at the Objects pane. There will be a disclosure triangle to the left of the portal's entry, and if you click that you will see which fields and other objects are within the portal (they will be indented). If they are not within the portal, then they will not show within it. I have found that cut->click inside portal->paste sometimes convinces FM to paste them within the portal. Or just drag them out, drop them, count to ten potatoes, then drag them back in might do it. I believe it is the top-left pixel of the field that needs to be inside the boundary of the portal for it to be considered to be 'inside' it. The bottom-right of your portal does not matter, it can be way outside. The Objects pane is 100% accurate though...
  13. Hi Dominic You could rewrite your code as follows. Assuming the pattern you have established so far carries on into the future, it will save you having to edit this calc in the future Case ( //First case is true if date is prior to 1st April in its specific year Month ( Date of Referral to Regulatory Body ) < 4 ; Year ( Date of Referral to Regulatory Body ) - 2011 ; //Second case is true if date is on or after 1st April in its specific year Year ( Date of Referral to Regulatory Body ) - 2010 )
  14. OK, in that case... 1) Enter find mode 2) Select First Aid Level 2 3) Hit return (you will now have those that have First Aid Level 2) 4) Enter find mode 4) Choose Car at level 3 5) Choose 'Constrain Found Set' (this performs the second find only on the results from the first find) This may be awkward for inexperienced users, so you could create a script that steps users through this. I don't agree that you cannot concatenate two fields Table2::Skill & Table2::Skill Level would give FirstAid2, Car3 etc and you may need to do this. But I may be missing something...
  15. You have to add a second find request. So if you are in table 1... 1) Enter find mode 2) Select First Aid Level 2 3) Add a 'New Find Request' (Cmd-N or Ctrl-N depending on platform) 4) Choose Car at level 3 5) Hit 'Return' One thing you need to be aware of, is that FileMaker does funny things when searching in portals. If you select First Aid in one field in the portal, and Level 2 in another field in the same portal on the same find request, FileMaker may think you are trying to find one or the other (OR) rather than both (AND). You may have to create a single field in Table 2, which concatenates the two pieces of information, and then search on that new field in the two find requests, to get what you are looking for.
  • Create New...

Important Information

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