Ralph Learmont

  • Content count

  • Joined

  • Last visited

  • Days Won


Ralph Learmont last won the day on November 9 2015

Ralph Learmont had the most liked content!

Community Reputation

3 Neutral

About Ralph Learmont

  • Rank

Profile Information

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

FIleMaker Profile

  • Platform
    Mac OS X Snow Leopard
  • Skill Level
  • Certification
  • Membership

Recent Profile Visitors

3,860 profile views
  1. 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
  2. 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
  3. 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 )))
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. I know that Bruce... When I mentioned the container using "external" storage was the likely problem, I didn't intend to give the impression that it was the wrong thing to do... Rather, that there may be a link type of problem, one that prevents Filemaker from getting the information it needs from those external sources - to allow the file to bring in those photos.
  10. I looked at your file, and see the problem... I tried the various "Save a Copy as..." options and all but one worked. As you say, that error message pops up if you attempt to save the file as a "Self-contained" copy. On looking at your field definitions, I see a container for photos that uses "external" storage. I suspect that's where the problem lies. See also http://help.filemaker.com/app/answers/detail/a_id/10797/~/could-not-be-created-error-when-saving-a-self-contained-copy Regards Ralph
  11. I have found this behaviour frustrating too! A workaround I have found, is to remove the index for the field you are searching.. To do this, go to Manage Database, go to Storage: Where it shows "Indexing", click the "None" button AND make sure Automatic creation of indexes is turned off. In many cases, indexing isn't an absolute necessity, and this workaround will work. (I'm aware there are exceptions). Regards Ralph Learmont
  12. Hi Vaughan, Here's my contribution to the discussion. Also my demo file. If I'm correct in understanding the problem, the way I worked it out is on the basis that: Each staff person works a certain number of hours a day whether they're actually "working" or "on call". Their day is composed of idle time and working time. Their minimum/basic/guaranteed pay for any given day is calculated by multiplying their total time at work by their "On-call" hourly rate. On top of that they gain extra pay by working any number of "periods" within the scope of that day, with each increment of pay being worked out by the "Difference-in-HrlyRate" multiplied by that particular "period". To separate the 2 types of "mode" (for want of a better word), I used a field called "WorkMode". It takes one of two values (from a value list) being "OnCall" or "Working". Whichever value appears affects calculations such as "HrlyRateDifference", which is either zero when a person's mode is "OnCall", or when they're "working" - it's the difference between the bigger-hourly-rate and the On-call-rate. From that I calculate the "PaymentThisPeriod"; and from that the "PaymentThisDay", the latter being the sum of related records from a simple relationship called "SamePerson_SameDay" In essence, I have (for a given person on a given day), one record tagged "OnCall" with its StartTime and EndTime running the full "extent" of a working day (for example from 9am to 5pm). Then additional records within that "extent" tagged "Working", showing periods whenever a person actually does work. There's one script used only for updating the display. Regards Ralph Learmont DailyPay.zip
  13. Hello Vaughn, I think you must have missed what I said in my last letter: "…To prevent unauthorized access to that Lock field, it's simply a matter of modifying the operator's privilege set to one that enforces "no access" to the "Lock" field." That protection is given at the field level (within the "Records" area) of security of the original file. Only a person having the required/higher privilege-set can change that Lock field. This protection extends to external files as well. Nothing to do with layouts. You said it yourself - "Restrictions set up at the record access level work universally". Regards Ralph
  14. Steven, With all due respect, I believe you have misjudged the intent of the query given by Oyseka and the response given by me. He asked for a way of stopping an operator from changing the value in a field after the initial entry. Also, in the event that a genuine error had been made, a means by which a corrective action could be performed. No mention of existing security arrangements was mentioned and no inkling that security needed to be improved was given. My response was given on the basis that "adequate" security was already in place. Without knowing anything beyond that, your statement that my process is flawed, comes out of the blue, and without anything in example form to support what you said. You state that it's easily susceptible to defeat… For that to happen, an attack would need to be made by somebody having credentials providing sufficient privilege to the schema, the layouts or to other areas of the file including a second attacking file. So, if the operator is such a credentialled person, yes, it's true the UI is not sufficient. To answer your concern (as a belts and braces solution), I would extend my method into one that uses a second "Lock" field in place of the second repetition. It's an extra field and a small price to pay for ultimate security. The "Lock" field therefore takes over the job of "watching". To prevent unauthorized access to that Lock field (which is what I believe you are talking about), it's simply a matter of modifying the operator's privilege set to one that enforces "no access" to the "Lock" field. This means if a genuine error is made, it will require the attendance/attention of a different (more privileged person) to correct the error. Regards Ralph Locking_a_Field_securely.zip
  15. In this situation, one way of locking a single field such as the 'Quantity' field on an Invoice is to change it into a repeating field with 2 repetitions. In effect, this hidden second repetition "watches" over what you do with the first... Redefining the Quantity field also involves using an auto-enter calc. to populate its second repetition with a "copy of the value" that's entered into the first. Then you add field-validation which checks that both repetition values are the same. On an Invoice layout, only the first repetition is visible. Any attempt to change the 'Quantity' is therefore challenged. I've provided an example file to show how this works. As an extension to this idea, it would be possible to provide a "Please let me" button/script to let a user make a "genuine" alteration - the script could operate in a few ways, one way being that it captures the initial value, the userName etc. (for auditing purpose say), then sets the second repetition to match the first, thereby permitting the change to be accepted. Regards, Ralph Locking_a_Field.zip