Leaderboard

Just as an FYI, you don't need a plugin to write to a text file. The FM file script steps can do that for you.

In users' privilege set, select 'Custom privileges…' for Records access, then select 'limited…' for Edit privileges in the relevant table. Use a formula like: Get ( CurrentDate ) - CreationDate < 30 as the calculation. Of course, you need to have a CreationDate field that auto-enters the creation date for this.

A TO is a Table Occurence: the 'tables' you see in the relationships graph in FM. Using your own file, you go to the graph and you add a reference to the 'real' file. Once you've authenticated there you pick each base table from that 'real' file in turn, which will add a TO for that external table into your own file, give them an easy name (doesn't have to be name of the actual base table). Once you have that done, close the "manage database" dialog and now you can target any of those TOs by the name you have given them.

In v.16 you need to use a script or a recursive custom function. In v.18, you can use the While() function. As I said earlier, there is no way to calculate the interest rate directly from the other loan parameters; it must be found by trial and error. A good algorithm for this is the Newton method. Here's a custom function I adapted from the article here: InterestRate (principal ; payment ; periods ; guess) = If ( not ( IsEmpty ( principal) or IsEmpty ( payment ) or IsEmpty ( periods ) ) ; Let ( [ fPMT = payment - payment * (1 + guess)^-periods - guess * principal ; fPMT' = periods * payment * (1 + guess)^(-periods-1) - principal ; nextGuess = guess - fPMT / fPMT' ] ; If ( Abs ( guess - nextGuess ) > .00000000001 ; InterestRate ( principal ; payment ; periods ; nextGuess ) ; nextGuess ) ) ) You need to supply a guess to the function. In most cases, a guess of = payment * periods / principal - 1 will provide a good starting point and allow the function to converge on the wanted solution. However, if you want an excellent guess, then continue reading the article where it describes the approximation method by David Cantrell. This provides results so close to the actual rate that in some scenarios it might be good to use on its own.

ExecuteSQL() is a function like any plugin function so anywhere you can use a plugin call, you can use the ExecuteSQL() function... so I don't follow that part of your explanation. We're not talking about the "Execute SQL" script step (note the space in the name), two different things, perhaps that's where the confusion comes from? And yes: you can reference an external table without having to have an open record.

I cannot reproduce the problem: However, in my experience portal filtering becomes unusably slow when the number of records to filter exceeds a few thousands. Perhaps you're experiencing a memory overflow problem.

why not use FM's native ExecuteSQL() function since you're only doing SELECT? As to the delay: since your query is fairly simple I'll guess it may be because you have an open record in the table you are querying. It's a FM behavior that I demoed at the 2014 Devcon and you can download the demo file here: https://www.soliantconsulting.com/blog/executesql-named-buckets/ With an open record in your session in the target table you're forcing FMS to send you the whole table so that your client can do the query, instead of FMS itself doing the query. So make sure to commit before running the query.

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 )

Agreed; the API is well documented and once you've crafted the core scripts, FM handles it very well natively. But I hear ya; if you have to pick up from where someone left off then you don't always have a choice.

Two things: 1) clearly something is not quite working properly in the process and that would need to be fixed to work properly in the debugger, 2) but even if you fixed it, it wouldn't work as a server-side script. FMS cannot import directly from another hosted FM file. It's a server limitation. You need to produce your found set and export it to a non-FM format (CSV, tab delimited, whatever your preference) and then import from that intermediate file.

I would probably go one step further and put this logic in a script that gets executed as part of the user's workflow when the 'application' is set up or submitted. The IF... ELSE IF logic will be generally easier to follow and much easier to troubleshoot.

Anyone reading this should ask themselves what is the purpose of this exercise: is it user convenience or is it data security? If it's the latter, than a solution relying on portal filtering (or any other layout-level device) will not be adequate.

I don't know where all the numbers come from, but if they are rates, they should be in a separate Rates table so they can be looked up and changed without modifying historical data. Also, too many parenthesis. Much of your logic makes no sense, for example in 'Commercial' if consumption is greater or = to 16 is ok, but then you want to know if it's greater or = to 15 and greater or = to 10 have the same result. Then less than or equal to 9 has a result, but I see no reason for '0'. Because a zero or negative number is less than 9. Anyway this is easier to read and can be streamlined even further: Let([ @C=Consumption; @calc=Case(Type="Residential"; Case(@C>=36;(@C-15-10-10)*21.56+472.5; @C>=26;(@C-15-10)*17.25+300; @C>=16;(@C-15)*15+150; @C>=10;150; @C*15) ; Type="Commercial"; Case(@C>=36;(@C-15-10-10)*21.56+43.13+945; @C>=26;(@C-15-10)*34.5+600; @C>=16;(@C-15)*30+300; @C>=10;300; @C*30) ; Type="Bulk"; Case(@C>=1;@C*200;0) ) //end case ]; //end variables @calc ) I'll assume all the "-15-10-10" are different rate classes or discounts applied, but like I stated earlier, they should be in a Rates table. If you were just doing this to see if the calc works, then you can declare each one of those fields in a variable for a cleaner, easier to read calc.

Well, I suggest you put some restriction on the number of allowed values, because otherwise this gets (even more) complicated. Some background: Your question is a variation of the subset sum problem, which in turn is a special case of the knapsack problem. Both are VERY difficult problems to solve programatically. Even worse, the known solutions are difficult to implement in Filemaker, because its calculation engine has no arrays. Fortunately, with a small number of values, a naive brute-force solution is feasible: enumerate all possible combinations of the given values, calculate the sum of each combination, and compare it to the target sum. The attached demo is designed to deal with up to 7 values. It has 127 records to enumerate the 2^7 -1 = 127 possible combinations, and a repeating calculation field with 7 repetitions to list the values of each combination. You can extend the limit by adding more records and more repetitions, but - as I said - this is a brute-force approach and it will get slower as the number of values increases. SubsetSum.fmp12

What is a pre-determined event? A value in a field, or user navigation (to layout/record/object), or user action (clicking a button?) All of which would require a script to target the desired layout, the method of invoking such script would be by use of a button on a layout (easiest) or a Trigger either on a layout a field, or object. Ideally the window open event should be caused by user intentional action vs at seemingly random times based on data or record or manual layout navigation.

Kevin Frank of FileMakerHacks joins us to talk hacking FileMaker and learning FileMaker. We talk about how the site got started and what a FileMaker hack is. He gives us a bunch of great tips. It was our honor to talk with Kevin. He's got a lot to say. Cajoling FileMaker with Kevin Frank. Episode 11

No, just 1 flavor. 'Advanced' means they can charge more, or it's something special...

Here's another: CheckboxSelectAll2.fmp12

Here's one way you could look at it: CheckboxSelectAll.fmp12

A field formatted as a checkbox set will normally contain a return-separated list of the values checked by the user, in the order they were checked. In order to test if a known value is checked, you should use: not IsEmpty ( FilterValues ( "Your Value" ; YourTable::YourField ) )

$__pk...appears to be a variable. If so you have to set the variable in the script first before you use it in 'Set Field'.

Quite right comment. For the OPs benefit I was showing how to write the code in a simplified form which is easier to read and therefore debug.

Based on this description, I would expect the penalty to be calculated as something like: If ( Get (CurrentDate) > Due Date and not DatePaid or DatePaid > Due Date ; AmountDue *.05 ) However, this still has the flaw of hard-coding the penalty rate into the calculation formula. Ideally, the rate would be auto-entered into another field and referenced from there, so that you can change it without affecting previous records. Note that the calculation must be unstored because it relies on current date. A test of 0 will always be false, not true. OTOH, the last test, AmountDue*.05, will always be true (unless the amount is 0).

Here is your case statement on a line-by-line basis Case ( IsEmpty ( DatePaid ) or DatePaid>Due Date ; AmountDue*.05 ; 0 ; Due_Date<Get (CurrentDate) ; AmountDue*.05 ; 0 ) All I have done is put in some returns to separate the individual cases Notice that case #2 starts with "0". This is not a comparative but will always be true and will yield either 0 or 1 based on Due_Date<Get(CurrentDate) Should your statement actually be... Case ( IsEmpty ( DatePaid ) or DatePaid>Due Date ; AmountDue*.05 ; Due_Date<Get (CurrentDate) ; AmountDue*.05 ; 0 )

Go back to the email you got with your FM18 license details. The links to the downloads will point to the latest full installer so that you don't have to install v1 first and then upgrade. If you don't have that email anymore, contact your FM sales person and they can send it again.

If they didn't change it, then you have two options: import an XML response, or get a JSON response and parse it. I suggest you try the JSON route first: for testing, define a global text field, then use the Insert From URL script step to place the response in this field. The URL needs to be calculated from your address fields - something like: "http://maps.googleapis.com/maps/api/geocode/json?address=" & Substitute ( List ( Street ; City ; State ; Country ) ; [ " " ; "+" ] ; [ ¶ ; "+" ] ) & "&key=YOUR_API_KEY" This should populate the global field with a JSON response like the one shown here. Then you can parse out the latitude using: JSONGetElement ( YourTable::gTestfield ; "results[0]geometry.location.lat" ) and, of course, for longitude change the last key to "lon". Once you have it all working, you can replace the global test field with a script variable. --- Caveat: the response may contain more than one address; you should test for this and, if necessary, let the user select the correct one.

Also, the images on your layouts appear fairly small, yet some images are much larger than they need to be (I found one that is 1644x1380). By resizing your images to the max size your DB uses, you reduce the filesize significantly. Also make sure you use something like 80%-85% compression as the quality of the images doesn't justify any higher. Your max image size doesn't need to be higher than 650x455 pixels at 85% compression. The mentioned image original size was 287kB and went to 98kB at 640x540 with 85% compr. Considering that 50% of your images can loose around 200kB each, you'd be looking at a reduction of around 15MB. If there are duplicate images, you can loose more fat by using a separate image table and using a relationship.

Sorry, I can't do your debugging for you. The only thing I can add to my previous answer is that the following statement: SELECT "Customer Name", SUM("Invoice Total") FROM "Invoices" WHERE "Invoice Sub Total" > 0 AND "InvoiceMonth" = 10 AND "InvoiceYear" = 2019 AND ("Job Status" <> 'Giveaway' OR "Job Status" IS NULL) GROUP BY "Customer Name" works fine for me. Here you can see it applied to your file, after escaping and replacing the hard-coded criteria with arguments:

Depends a little on when you fire this calculation. If it will fire each time a user visits the dashboard then some of it doesn't make sense since last month's numbers will not have changed from one visit to the other. So you don't need to recalculate those numbers over and over through SQL, If you feel you need to use SQL because you are constrained to the Dashboard context. You can calculate these numbers at night in a server-side schedule, at the end of the month using non-SQL functionality. The same applies to the current month's numbers, except if the users expect those to be accurate to the minute.

I haven't tested that. I don't think you need another variable for this. You can use the Let () function for better readability. In fact, you might find this style even more convenient: Let ( [ template = "SELECT FieldName FROM FileMaker_Fields WHERE TableName='<<tableName>>'" ; sqlStatement = Substitute ( template, "<<tableName>>" ; $table ) ; columnSeparator = "," ; rowSeparator = ¶ ; databaseName = $fm_file ] ; BE_FileMakerSQL ( sqlStatement ; columnSeparator ; rowSeparator ; databaseName ) )

Got the following from my Office 365 tenant today...looks like I will be unable to do "basic" (normal) authenitication for IMAP using the 360Works Email plugin to Office 365 mailboxes starting 10/13/2020. I don't see any mention of OAuth in the current documentation for the Mail plugin, so I'm assuming it doesn't yet exist.... Is 360Works working on adding OAuth functionality to the Email plugin? Thanks, John