Jump to content


  • Content count

  • Joined

  • Last visited

  • Days Won


NLR last won the day on November 9 2015

NLR had the most liked content!

Community Reputation

4 Neutral

About NLR

  • Rank

Profile Information

  • Gender
  • Location
    Donvale, Victoria Australia
  • Interests
    Gliding, Flying, Radio-control hobbies

FileMaker Experience

  • Skill Level
  • FM Application
    16 Advanced

Platform Environment

  • OS Platform
  • OS Version
    El Capitan

FileMaker Partner

  • Certification

Recent Profile Visitors

5,113 profile views
  1. NLR

    Duplicates and Empties . . .

    There's a quirk in the way Filemaker finds duplicates when there's a constrain involved. I know your pain. In the past I spent many hours trying to understand why and finally saw the answer. If you remove the index from the field where duplicates are being found, it should solve the problem. I came to the conclusion that Filemaker internally takes advantage of indexing (if it exists) when it searches for duplicates, and the duplicates it comes up with are always across the ENTIRE Table. Unfortunately found sets are ignored! However if there's no index, Filemaker will properly constrain itself to the found set when it looks for duplicates. To turn off indexing make sure automatic indexing is turned off as well as specifying NO index. If you really need indexing to remain on this field, just create another (unindexed) calc field based on it, and use it instead.
  2. I'm assuming the field you refer to is defined as a text field. The test below is based on this. A test for a TEXT field's contents being purely numeric is: GetAsNumber ( Field) = Field An example: If a text field contains the value: 1+4 GetAsNumber (1+4) = 14 returns false, therefore the value '1+4' would be excluded for you. On the other hand, GetAsNumber (18) = 18 returns true, therefore the value '18' would be accepted. But if your field is defined as a number field, a more stringent test needs to be used such as: GetAsNumber ( Field) = Field and Length(GetAsNumber ( Field)) = Length(Field) Ralph
  3. The summary approach isn't the only way. I also like a looping method like one shown earlier by someone else. I must admit I found it difficult to grasp what your intentions are, however on a few more read-throughs of your various letters I think I get the idea... Anyway, I've put together a script which runs a loop. And within this loop there's a "Find" which gets records for each Client for the 63 day range you decribed (ie. going back 9 weeks from "today"). Then a second smaller loop within, which counts up each occasion a Pickup is done by that person. So for each person, we know the number of days involved as well as the number of pickups that person did. This leads us into deriving a percentage result for each. Hope this is closer to what you're after. Regards Ralph TEST.fmp12.zip
  4. Try this: To use a sub-summary value, consider the GetSummary function
  5. Take a look at my revision. I basically just redefined the field MISSED PICKUP QUANTITY••• I've left comments within the definition explaining that because this field needs to calculate when the other field it relies on is empty, you must remove the tick which says "Do not evaluate if referenced field is empty" Also I changed the definition to read: If ( IsEmpty (PICKUP QUANTITY) ; -1; 1 ) Other than that I adjusted your script a little. Because you have defined that summary field, there's actually no need to run the bulk of your script because once it's sorted, the result you seek is shown. Ralph TEST.fmp12.zip
  6. There's a number function called Int (number) which strips off any decimal portion leaving just the integer part. In your example, Int ( 100.4 ) = 100
  7. It's not that hard. There's no need to close the file. Filemaker Pro has a command: "Save a Copy as" which saves a copy of the current file. It's a script step too. There are a few options when saving, such as Saving a compacted copy (slightly compressed), and saving a "Clone" (without records). It has been possible for many versions of Filemaker Pro. https://fmhelp.filemaker.com/help/16/fmp/en/index.html#page/FMP_Help/save-a-copy-as.html It's purpose is for Backing up. The command is available for single user Filemaker Pro (and Advanced) and to Filemaker when acting as the host - in a peer to peer situation. It's not available to client users (those connected to a host).
  8. There are one or two ways I've done this type of thing. The sample I've just made doesn't use a script. A relationship and three fields are used: theYear: A number. An auto entered calculation = Year(Get(CurrentDate) Relationship: Based on theYear=theYear. I named it BaseTable_SameYear IncidentNo: A number. An auto enter calculation which replaces existing value. Evaluate always = Last ( BaseTable_SameYear::IncidentNo )+1 YearlyIncidentNo: A calculation of type text = theYear & " - " & IncidentNo It's important when defining IncidentNo, to evaluate always. This ensures things work for the first record. I've opted to replace existing value, the reason being that it allows a record to be duplicated without causing the IncidentNo to also be duplicated. Incidentally, the use of the Last function is much faster than using the alternative Max function, which is noticable when there are a large number of records. Ralph YearlyBasedRecordNo.fmp12.zip
  9. Given the name of a script, you can use the Design functions to work out where its name appears in the List of scripts and knowing that, deduce its scriptID - because it appears at the same level in a List of all your script IDs. Or use this calculation: Let ( [ WantedScriptName = "MyScriptName" ; // <-- the name of your script goes here AllScriptNames = ScriptNames ( "" ) ; AllScriptIDs = ScriptIDs ( "" ) ; PositionOfScript = Position ( ¶ & AllScriptNames & ¶ ; ¶ & WantedScriptName & ¶ ; 1 ; 1 ) ; Nth_Item_In_List = ValueCount (Left(AllScriptNames ; PositionOfScript)) ; ScriptID = GetValue ( AllScriptIDs ; Nth_Item_In_List ) ] ; ScriptID ) Ralph
  10. NLR

    Count repeating field repetitions with data

    It's probably a better idea to use a related table instead of a repeating field. It really depends on how far you might want to extend things later... Kept simple, repeating fields are OK for this type of thing, however you might find yourself hitting limitations later. Anyway, here's a method which gives sequential Item numbers which skip any empty entries... Description is your Description 'Text' field Repetition is an additional repeating Calculation field of type 'Number' defined as: If ( not IsEmpty ( Description ) ; Get(CalculationRepetitionNumber)) Item is another repeating Calculation field of type 'Number' defined as: Let ( [ theList = List (Repetition) ; thePosition = Position ( theList ; Repetition ; 1 ; 1 ) ; shortList = Left ( theList ; thePosition ) ] ; If ( Repetition > 0 ; ValueCount (shortList )))
  11. Regarding XML versus MER, I think either would do. I used to think of XML as safer because of its delimiters. Then again I've just now done some practice exports and re-imports back into Filemaker. So far I haven't been able to trip up the MER format. It seems to handle carriage returns, double quotes, tabs, and other characters just fine, and for ease of reading the raw file (if for no other reason), I'd now use that. On checking FM Help I see that the character separating fields varies according to language, if that's a consideration. Regarding the use of FM12 versus FM14 for importing fp7 format files… I know that FM12 behaves the same, that is both require conversion to fmp12 format before importing. Regards Ralph
  12. I sympathise. I can see your problem and imagine the pain and frustration at not having a clear way to get user's data out of the old and into the new. I have had quite a lot of experience in providing updaters. As I see it you have the worst possible situation in trying to jump two hurdles. First the conversion from an fp7 file format to the new fmp12, and the second being that a Runtime application is absolutely restricted to only "knowing" its own bound files with that internal binding code. Each case on its own presents challenges but both together... (a really tough one). I know it's always nice to have a press-button answer whereby a user merely clicks and the whole process is fully automated. In your situation this is hardly possible. There are a few ways to go and it appears you've looked hard. Further to that, to consider a "best/least painful" way it would be helpful to know a bit more: 1) How many files? 2) How many tables within each file, and the record counts involved (just roughly). 3) Can you accept loss of container data, or are you prepared to look at workaround to save images. (Such as another script to manage exporting of field contents looping over all records) 4) Global fields are far less of a problem because only one value per table (if you need to do it) is required. 5) If there are repeating fields, you have an extra problem to look at and solve. (It's possible but tricky) If you are happy to confine data retrieval to all types except containers and repeating fields, one solution I'm thinking of is based on your saying that "This has been fine over many updates/new versions which we've written over the past few years, our restore function imports the data back smoothly. Until now…" I would therefore do it in 2 stages: Stage1 means providing your users an (old format) updater to their solution as you've done in the past. In it there would be a script (and button) which basically exports each table (of each file) in a format which is universally understood - such as XML. That way you get the field name info along with the data. The script would loop through each file, then each table, show all records, unsort them, and export. A scheme for naming each export file is important so you can match later (knowing such names in advance). For $Filepath I would name it after the BaseTable, for example "file:Contacts.xml". Saved to local folder, (unless you can manage the extra step of providing them with a "SavedData" folder beforehand). It would then be "file:/SavedData/Contacts.xml" So then you have a stack of xml files such as "Contacts.xml", "Invoices.xml", "LineItems.xml" etc etc. - All this for one file. (If you have multiple files, include part of the file's name prior to each Table name). Stage2. At this point your new runtime system can take over and import from all those xml files. I think this should work. You would obviously need to rigorously test beforehand. There are other aspects too. Things like Value Lists, and Serial numbers... Regards Ralph
  13. I've used a method similar to this in the past for creating Name badges for people attending meetings, where the purpose is to make a person's name as large as possible to read at some distance. (People with short names like Tom have an advantage). I've reworked some of what I remembered into a demo (see attachment)... There are two versions. Because of a difference in behaviour between fp7 and fmp12 files, it was found that doing a conversion of the fp7 file was problematic. I therefore revised the script, and the fmp12 version (as provided here) now works correctly. In fact it's a bit more "visually interesting" because the rendering time is a bit longer. I haven't tested using Filemaker 13 or 14, only Filemaker 11 and 12. To run it, click the "Fill all " button. Initialise using the "Reset all" button. The way it works makes it completely independent of the font family or variant used. It works by using a "suck it and see" approach - where the script begins with a small size (I used 10 but it could be less). It then fills a THIN version of the final field, the purpose being to deliberately swell the field's vertical dimension with each pass of a loop. Each loop pass uses an incrementing font size. This consequently pumps up the text box, so we measure the text-box's vertical height at each pass. The increased dimension is then compared with the previous result. The technique uses a calculation: $CurrentHeight = GetLayoutObjectAttribute ( "TextBoxSlim" ; "Height" ) This continues, until suddenly the active text box suddenly increases its height by a factor of around 2, and this moment is obviously the moment the text decides to overflow onto a fresh new line. We capture this event and exit the loop. Knowing we have exceeded the point-size by "1" we simply render the final "Presentation" text-box using a font size 1 point less than that which caused the overflow. Getting this to work means initially allowing a bit of time for text box expansion and contraction. It's worth noting that you can inflate a text box and it stays that way but if you then replace your text selection with a small font-size, the box stays expanded. You need to exit the field and re-enter it before it shrinks down. (Again I don't know if this behaviour changes with v13 or v14). Ralph DynamicFontSize.zip
  14. I watched your video and that helped a lot. Following from that, I was able to make a small file to cement my ideas, and I think it miht be what you are after.. Basically I used your Tables with minor changes. I defined a new Employees field: "NextAppointment" which calculates the maximum date from all visits by that particular person (as per your BloodTests Table). Then, in your Dashboard table where you currently have a portal displaying BloodTests, I chnged that portal to show Employees. And with those employee records you can see the "NextAppointment" field. I'm sure there are variations to how this can be done, and one consequence of using an unstored calculated field (of necessity because it's referencing a related field), is that a portal display in another window will need to be refreshed to show any changes, and this might be a problem if there are many records. To get around this issue, a variation (which I've shown in a second file) is to use a so-called auto-enter Date field which actually uses the same calculation. Because auto enter fields rely on changes to "Local" fields to update themselves, we need to employ a means of doing this artificially, and the method I used was to have a script trigger activated by any entry/change in the "NextAppoint" field in your Blood Tests layout. Normally I'm hesitant to use triggers. In this case it might be an opportunity to use such a script for other follow-up purposes such as checking an entry. Regards Ralph NextBloodTest.zip
  15. NLR

    Finding Date within a Range

    Using a relationship between a "Date" field in one table related to the "FinishDate" (for example) in your PayPeriods table will work if you take advantage of Filemaker being able to look up the nearest highest or lowest value when there's no exact match. I've attached a sample file. Regards Ralph PayPeriods.fp7.zip

Important Information

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