Custom Functions Discussions

I am using FileMaker Pro 19. This is such a basic question, it's embarrassing. I have been using FileMaker Pro since, I think, around version 7. Every 3 to 5 years I bring it out and make something relatively complex for my personal business use, I then use that custom app for many years. Each time the gears gradually wind up and I am more less back where I was before, able to get what I need. This time, not so much. I have an if then script function... If( (SCAAAPE | PreProcess::UX | Statement | Bank | Card) = "Apple / Goldman-Sachs | Mastercard"; "AGS"; "BECU") The script is located in a button using Perform Script It is the first stage of a con…

Howdy all: For the life of me, I can't figure out how to get this custom function ( https://www.briandunning.com/cf/1943 ) to work. I've attached a rudimentary file where I want to grab the button name and pop it into a global variable. Is the suspect the Get ( ActiveLayoutObjectName ) argument in the calculation, or...? I'm sure it's something simple, just that I'm in Stupid Mode again. : P Thanks, Rich Test.fmp12

I'm looking for a function that will only trim spaces on the right side as I have fields where there may be some spaces padding on the left by design. I'd like to use this function as an auto-enter filter to remove spaces only from the right side of the incoming data.

Ok. My eyes are blood shot and my brain hurts. GRIN! I have been working on a laser settings database. I make all kinds of stuff for our Church Scout Program called Royal Rangers. I do it for not for profit. You guys and gals have helped me many times over the past 20 to 30 years and THANKS again for that. In this filemaker pro 12 file I have a layout #6 that I am trying to get this calculation to work that converts Millimeters per second to inches per minute. I found a site that tells me how to do it and that screen capture is on that same page as my calculation attempts. Can one of you kind folks nudge me back from my headache and show me the error of my…

Following is a custom function I built to send SMS messages out of filemaker using textgrid which is about half the price of Twilio. parameters are CellNumber and Message the account SID, Authorization Token and the number you purchased from textGrid are available to you after signing up with them. As far as I know this works in the United states but Im sure can be modified to work overseas. I tried to post this to briandunning custom functions but was unable to create an account there to do so. Use the insert URL to $$response "httpspost://" & "your account sid" & ":" & "authorization token" & "@api.textgrid.com/2010-04-01/Acc…

Hi, I have this code that is giving me the Host (server) time. It seems that the GMT offset can be included to show the Local time but I'm stumped. Can someone tweak this for me? Many thanks beforehand, Daniel ______________________ Let ( [ts = GetAsTimestamp ( Get(CurrentHostTimestamp)) - $$LOG_DATE_OFFSET; secondsIncluded = If ( PatternCount ( GetAsTimestamp ( Get(CurrentHostTimestamp)) - $$LOG_DATE_OFFSET; ":") = 1; 1;0); start = Position (ts; ":" ; 1 ; 1 ) + 2; length = Length ( ts ); insert = ":00"; end = Right ( ts ; 3 )]; If ( secondsIncluded; Left ( ts ; start ) & insert & end ; ts ) )

Happy holidays, everybody. I'm trying to create a custom function where given "x" month/date of a product's sales date it results in the quarter it was sold, irrespective of year. How can I code this so it only analyzes the month/day and not the year so I don't have to annually input the year? Let ( _Date_ = SaleDate //Date Field ; Case ( _Date_ ≥ GetAsDate ( "9/1/2022" ) and _Date_ < GetAsDate ( "12/1/2022" ) ; "Q1" ; _Date_ ≥ GetAsDate ( "12/1/2022" ) and _Date_ < GetAsDate ( "3/1/2023" ) ; "Q2" ; _Date_ ≥ GetAsDate ( "3/1/2023" ) and _Date_ < GetAsDate ( "6/1/2023" ) ; "Q3" ; _Date_ ≥ GetAsDate ( "6/1/2023" ) and _Date_ …

I'm setting up a budgeting file, with each record being for a specific pay date. The pay dates are the 5th and 20th each month. The bills show up as a relation, so I'd like to just have a script duplicate the record, then if the date is on the 5th, change it to the 20th. That part I've got down. But if the date is the 20th, I need it to change the date to the 5th of the next month. I've done this sort of thing before where I had it add 1 to Month(DATE) [something like that] and if the new month was 13, then set it to 1 and add 1 to the year. But is there an easier way to just add one to the month, and in December, have it roll to January of the next year?

Hi guys... is this a good case for using the While function...? I am a bit stuck... I am trying to loop through a value list to search for the first instance of a value being greater than X eg: for value list "1 2 3 6 7 8 11 15" and X = 10 It should return the position of the first value greater than 10, in this case "7" as it is greater than 10 What is the best way to do this??

Hi all I've worked myself into a "i don't know what I'm doing now" situation. I'm trying to use a briandunning.com custom function found here however I'm lost at the script tigger remarks. I am unable to add the "one line" script onto OnObjectModify: The demo file link no longer works. Can anyone advise on what - if any, and how to make - the script is?

I have searched the web for a Custom Function that solves this to no avail… There are many CF’s that “almost” do what I need but nothing as explained below. OBJECTIVE: Calculate a DATE x days AFTER a given date skipping weekends AND bank holidays. GIVEN: StartDate NumbOfDays Result Desired = Date that skips weekends and bank holidays. Any year’s HOLIDAY dates can be gleaned from pre-existing Custom Function “HolidayListByYear (yearNumber)” written by Jonathan Mickelson at www.briandunning.com (also uses another Custom Function he wrote called “DateByDayOccur”). Banking holidays should always include: …

Someone requested on face book a function to find a gap in sequence. This is my attempt for that with a While Function If I were to add this to a custom function i would Replace X with a parameter - also perhaps suggest to do a unique list and sort list on the parameter. While ( [ x = "1¶2¶3¶6¶7¶8¶12¶13¶16¶17¶18" ; y = "" ; z = "" ; out = "" ; i = 1 ; c = ValueCount ( x ) ] ; i ≤ c ; [ y = GetValue ( x ; i ) ; z = case ( y+1 = GetValue ( x ; i+1 ) or y-1 = GetValue ( x ; i+1 ) ; "" ; "-" & y & "," ) ; out = Case ( i = 1 ; y ; Right (out ;1) = "," ; out & y ; out & z ) ; i = i+1 ] ; Case( right (out;1…

I created a demo file. My question is how to create customer sales total without using ExecuteSQL. Go to the report and click customer report. And you would see my sql statement. Is there a way to do the same thing not using sql? If I export it to iPhone ExecuteSQL may not work....Thanks. KC Customer Sales Report.fmp12

Is there a way to accomplish this? below is the sample number, I need to get at least the closest number. Sizes 136, 91, 88, 84.75, 75, 72, 70, 68, 64, 62, 61, 60, 59, 58, 57, 56.25, 56, 55, 54, 52, 51, 50, 49, 48 How can I get the combination of 228 from the list above? For Example: 70, 56, 52, 50 = 228 or 68, 62, 50, 48 = 228

I am using Filemaker Pro Advanced to keep track of residence in our recovery program. We like to keep a running total of how many days they are here in our program and have it stop when they depart our program right now the calculation looks like this : If(IsEmpty(Start Date) = 0; Get(CurrentDate)) - Start Date + 1 This was done by a previous employee but it doesn't stop the calculation when the leave. What I would like to do his to have it stop once the departure date field has a date in it but I am too new at all this to figure it out. Any one have a calculation out there for this type of script? Thanks, Sandi

For some time I have been cleaning emails for filing in our filemaker database. For this, the users have added to a substitute field all text that they wanted removed from and email, separated by "//". Then I used @comment custom function https://www.briandunning.com/cf/851 to remove the text blocks from the email text. Somehow this stopped working in fm 18. I tried to find out where it errors but to no avail. I was also thinking about putting each block of text into individual records and then creating an array from this or loop through the email using while but I am a little lost on how to do this. Would anyone be able to help please?

Can this be done through Filemaker? The idea is maximize the cut so we can minimize the scrap materials. The logic is the same but I'm having hard time to put it on my database

Hi people! I'm trying to set up multiple conditioned calculated value to a field using Case or If functions but It gets too complicated for my current filemaker skill level. Here's the deal: - I have field SUPPLEMENTATION that should have auto-enter text value based on the calculation which includes other 4 fields ( VITAMINS MORNING; VITAMINS EVENING; MINERALS MORNING; MINERALS EVENING) and multiple conditions - When field VITAMINS MORNING or/and VITAMINS EVENING is not empty, value of field SUPPLEMENTATION is "Swanson" (Condition 1) - When field MINERALS MORNING or/and MINERALS EVENING is not empty, value of field SUPPLEMENTATION is "Now" (Condi…

I have a script that is going through data and counting how many times a word shows up. It is then taking that number and storing it to a global field. My question it when I display that field, example $$Masters_degree_nursing , how do I have it display that number with commas in it? This is how I have it showing the script results. I which I new a better way to make it look like a spreadsheet or something with lines in there, like an Excel spreadsheet.

I have a database hosted in a filemaker server. I need to know who is working in a record. To prevent one person can change one record at the same time as another. Thanks again.

I’m am looking for a calculation or custom function that will compare two list to indicate if any value from ListA appears in ListB regardless of position in the lists. I have tried experimenting with PatternCount without success. The values must be an exact match.

Is there a way to do this? The difference should be as close as possible. I use even number because the number are by pairs. 2 4 6 8 10 Example 18 = 10 and 8 20 = 10 and 10 22 = 12 and 10 36 = 18 and 14

Hello, Apologies for my silly question I am very much a beginner. I have downloaded the BaseElements plugin, specifically to make use of this https://baseelementsplugin.zendesk.com/hc/en-us/articles/204700538-BE-ExportFieldContents. BE_ExportFieldContents, now this supposedly will allow me to export the contents of the container field using a server script. But I am at a total loss on how this will work? How do I call the function? As I can only see it in the calculations? So how would I write a script that calls this function? Again, I'm sorry for the silly question but I'm baffled. Thanks in advance.

Can anyone assist me to convert below script to Filemaker function? Thanks. function calcVi(Q1,Q2) { var Q3,Q4,Q5,Q6,Q7; if ( Q2 >= 2 && Q2 < 4){ Q3 = 0.827 * Math.pow(Q2,2) + 1.632 * Q2 - 0.181; Q4 = 0.3094 * Math.pow(Q2,2) + 0.182 * Q2; Q6 = (Q3 + Q4 - Q1) / Q4 * 100; } if ( Q2 >= 4 && Q2 < 6.1){ Q3 = -2.6758 * Math.pow(Q2,2) + 96.671 * Q2 - 269.664 * Math.sqrt(Q2) + 215.025; Q4 = -7.1955 * Math.pow(Q2,2) + 241.992 * Q2 - 725.478 * Math.sqrt(Q2) + 603.88; Q6 = (Q3 + Q4 - Q1) / Q4 * 100; } if ( Q2 >= …

Hey all, this time I created two functions that generate a BMP file with a code-128 barcode. It's more complicated, despite being shorter than the previous (EAN13), and it also has to generate a variable-length BMP file. There are some hacks, like the generation of a little-endian number for the BMP header, but overall it seems stable. I tried uploading it to Brian Dunning's website but it didn't give me the link. I'll post it here, and if there are problems, please PM me. /** * ===================================== * BarcodeCode128B_BMP_380px ( string ) * * PURPOSE: * Generate a binary BMP file containing a Code-128 Type B barcode * * NOTE: * Em…

Note: This was originally posted in a Legacy forum... I need to count instances (how many) of any 1 item is requested on any invoice for a future 3 week period. I have to add this function to an existing database, but it has not been set up very well. (FmPro 14, cannot upgrade...) Details of database structure: There are 2 Tables: Invoice, and Inventory. Only key field is "ID", which is used to identify the specific piece of gear. (Approx 200 ID "codes" ) Related by Primary Key (Invoice table), Foreign Key (Inventory table). Inventory rarely changes, and current stock is kept in a field in that table. An invoice is created for future meetings li…

Hi everyone, first of all, I am a rookie and have limited knowledge. This is actually my first more complex db I have created or worked with. I have ammended a db from a lynda template which basically controlls shore excursions, booking and passenger activitiy records for river cruise ships. However, I work for various different companies on different vessels and everyone has partly the same and partly different shore excursions on offer with different prices and details . Additionally every operator has some 10 - 15 different itineraries on offer, which are offered with different tour codes and dates. With the high amount of vessels, itneraries,…

Greetings I have spent a fruitless couple of days searching, testing and generally getting frustrated... I’m relatively certain my problem is a simple one, however: I am trying to calculate fuel required for a trip. It’s a simple Time/Speed/Distance calculation. The basic mathematics is in place and calculating accurately, so so-far, so-good. BUT, what I need the result to display as, is a whole number, Rounded UP to the nearest 50/100. So for example, if my TSD calculation returns a result of let’s say 716, I would want the result to display 750. Similarly if the calculation result is 751, I would require 800 to be displayed (an extreme example). This inf…

Name & Parameters: [color:red][big] TypeSumField ( TypeField ; Type ; SumField ; Start ) [/big] Description: This function is designed to provide summary data for a subset of records. For instance, in a table of Invoices and a related table of Line Items, where each Line Item can have a type of Food, Clothing, or Auto, it will sum the totals for each line item of a certain type (e.g. (TypeSum(LineItems::Type; "Auto"; LineItems::Total;0) ) without the need for a filtering relationship or extra fields for each Type. This is my first published attempt at a custom function. I'd like any feedback and comments. Thanks! Sample Input: Resu…

Good afternoon, (I'm not sure this is the right section, but seemed the most appropriate that I can find.) I have three fields ("type", "client", and "expense"). Each field has 10 repetitions. I have a button that will clear out the n'th repetition of all three fields - so the whole line is blanked should the user need to do so. What I would like to do is then remove that empty line, moving all the data from all subsequent fields "up" one. (n-1), thus keeping the list compact and free of blank lines. I did write a loop that did this, but it was very clumsy, and thought that somebody may know of a cleaner way using existing functions and smarter (than my-) …

Hi there... been trying REALLY hard to do this but I can't please guide me. It should be a really simple recursive function: UPPER CASE each line of a value list which starts "~~" Eg: INPUT: ~~header 1 some text some more ~header 2 some new text also text OUTPUT: ~~HEADER 1 some text some more ~HEADER 2 some new text also text

Hey guys, I’m trying to get FM to use the modem as a dialer, in windows its simple but for some reason its disabled in the Mac. Anyone have a solution to get this to work.

Gentlemen, I have two value lists: Category and Subcategory. Category contains the following: Food, Drinks. Subcategory contains: Chips, Soda, Juice. How can I do the following thing: when choosing Food, I want Chips field only be available for selection (no drinks displayed). And vise versa. Thanks!

I Have custom Function BulletList(Text,BulletChar,Start) which is supposed to add the bullet character at the start of each value. I have been scratching my head all day trying to get it to be recursive. Let([ VC=ValueCount(text); Line=BulletChar & " " & GetValue(text;start) ]; Case(start≤VC;line &¶& BulletList(Line;BulletChar;Start+1);) ) Any pointers would be great. I actually don't want to have the "Start" variable but thought I needed a counter to exit the function and a way to increment the getvalue.

Good morning. I have the following custom function in my solution, but would like it modified. It will calculate the elapsed time in years, months and days between two dates. It works fine, however, i want it to not display any zero units for years and months. For example. It will calculate two dates and produce "0y, 0m, 15d" when I would like only "15d". Also, the likes of a two dates where the result would be "2y, 0m, 3d" to just be "2y, 3d". I do want to keep the days, even when they are zero. eg. "1y, 0d". (Even if the two compared dates are the same, "0d" is required.) I am not sure how to add further logic into the last few lines to not inser…

I have a database that is created to calculate commissions for sales staff (Commission Table). I have group of accounts that were assigned to a previous rep and will now be disbursed among the existing sales people. I have created a related table (Special Accounts) to hold this list of accounts. (Customer ID) is the common field between the two tables. My issue is I need to figure out a way gather the sum of sales in the Commission Table for those accounts where Customer ID matches the Special Accounts, but without having to do a specific query on the data - which would take too long for all of these accounts, with all of the reps that the accounts are reassi…

As part of a much larger text calculation (in the style of a merge letter style solution) , I am trying to create a custom function that will substitute a tag/placeholder such as <<a or an>> with either "a" or "an" in the final calculation, which I think will need to based on a pre-defined list of words that would require one or the other. From a grammar perspective, the use of the articles "a" or "an" is irregular, as it is based on if the subsequent word (most of the time at least) has a vowel sound (e.g., both "university" and "undone" start with the letter "u", but each requires a different article because one has a vowel sound when said aloud and the oth…

Hi CFD, longtime user, first time builder! I need to build a list of dates, from a starting date, up to the 1st of the month after Get ( CurrentDate ). Each date will start on the 1st, and will spaced by an interval of so many months. EXAMPLE ƒ serviceSchedule ( Date ( 5 ; 1 ; 2017 ) ; 1 ) //monthly = 5/1/2017, 6/1/2017, 7/1/2017, 8/1/2017, 9/1/2017, 10/1/2017, 11/1/2017, 12/1/2017, 1/1/2018, 2/1/2018, 3/1/2018 ƒ serviceSchedule ( Date ( 5 ; 1 ; 2017 ) ; 3 ) //quarterly = 7/1/2017, 10/1/2017, 1/1/2018 I have the loop start on January of the startDate year, and loop from there. I do get the results but the behavior of the CF is pecu…

I have a CF that builds a list of TimeStamp intervals: 1/6/2018 9:00 AM 1/6/2018 9:10 AM 1/6/2018 9:40 AM 1/6/2018 9:50 AM 1/6/2018 10:40 AM 1/6/2018 10:50 AM 1/6/2018 11:00 AM 1/6/2018 11:40 AM 1/6/2018 11:50 AM 1/6/2018 12:00 PM 1/6/2018 12:10 PM 1/6/2018 12:20 PM 1/6/2018 12:30 PM 1/6/2018 12:40 PM 1/6/2018 12:50 PM I want to analyze this list and generate an index sequence (resetting the index back to 1 if there is a break in sequence) 1/6/2018 9:00 AM = 1 1/6/2018 9:10 AM = 2 1/6/2018 9:40 AM = 1 1/6/2018 9:50 AM = 2 1/6/2018 10:40 AM = 1 1/6/2018 10:50 AM = 2 1/6/2018 11:00 AM = 3 1/6/2018 11:40 AM = 1 1/6/2018 11…

I have a list of timetable line items with a start time and a duration. The classes can vary in length and the available time slots are 30mins. What I am trying to do is create a graphic view of this using conditional formatting. I have a separate table with records for each time slot. For my method to work the graphic table needs to relate to the timetable table and the problem is the timetable only has a record for the start of the class. I thought I could create a field in the timetable which lists the time slots booked via a recursive custom function. My attempts are failing and this is my code. Let([ start=(Timetable check all::Start); //start time du…

Hi everyone, How can we run a script using custom functions? By either employing plugins or native FileMaker methods? I have checked plugins like Scriptmaster and BaseElements and they work pretty well, but their problem is that the called script got queued in scripts queue and wait for current running scripts to finish and then the called script via plugin would run. I need a solution, to pause the running script, then run the recently called script, go back to parent script and wait for user to resume it. Thanks in advanced for your time. Regards,

Hello All, Please find attached two sample files that Im working. In Materials_DB1 when I create a new record my lot numbers are repeated, while in Materials_DB2 it works fine. is there something wrong with my other file? the monthly serial that Im using is from Daniel (Raybaudi ) Materials_DB1 Username is Admin PW: No Password Thank you so much in advance Materials_DB1.fmp12 Materials_DB_2.fmp12

Hi, Here is a custom function that I found on Briandunning.com. The problem is when I try to create a new function with this code, Filemaker refuses it saying The "here" above is situated before the parentheses after "WordWrap" and before "UnprocessedText". Would someone be kind enough to tell what I'm missing here to get this up and running? Let ([ TruncatedText = Left ( Text ; Size + 1 ) ; CountOfSpaces = PatternCount ( TruncatedText ; " " ) ; CountOfReturns = PatternCount ( TruncatedText ; "¶" ) ; PositionOfLastSpace = If ( CountOfReturns > 0 ; Position ( TruncatedText ; "¶" ; 1 ; 1 ) ; Position ( TruncatedText ; " " ; 1 ; CountOfSpaces ) ); Ad…

Hi all I am writing this post because I would like to share a custom function I created for generating EAN-13 barcodes. As almost every other (paid) solution involves plugins, scripts, fonts or other garbage, I decided to create something much cleaner. Everything the file needs is two custom functions, one that calls the other. The first, "GenerateEAN13_Raw" creates the binary string starting from the actual number, and the second, "GenerateEAN13_BMP_380px" takes the same number and generates a BMP file (that can be stored in a container) with the actual rendered barcode (sharp and clear). It took me many hours to create this, so I hope this will be useful …

I often see that FileMaker native functions use square brackets to define lists, like let ( [ ~var1 = 1 ; ~var2 = 2 ] ~var1 + ~var2 ) Or JSON functions, that actually seem to be "variadic" in the sense that they take a variable number of arguments: JSONSetElement ( "{}" ; [ "id" ; "FB4" ; JSONString ] ; [ "name" ; "Vanilla Cake" ; JSONString ] ; [ "price" ; 17.5 ; JSONNumber ] ; [ "stock" ; 12 ; JSONNumber ] ; [ "category" ; "Cakes" ; JSONString ] ; [ "special" ; true ; JSONBoolean ] ) ] Now, I've been searching for a way to do custom variadic functions, but it seems impossible. And the official documentation doesn't mention the squ…

Hello, If I have two lists : ListA Apples 20 Oranges 15 Pears 120 ListB Apples 13 Oranges 30 Nectarines 70 ... how can I end up to Apples 33 Oranges 45 Pears 120 Nectarines 70, with a custom function? Thank you!

I'm having trouble getting my head around this for some reason... In my solution my client has employees that confirm info, qualify a lead and then send an email out to the sales team. Recently there has been some bickering about who's lead is who's... I'm looking to instead of sending a mass email ([email protected]) i need to send out leads to individual sales people on a rotation. So if there are 5 sales persons it would send the first one to the first sales person, etc then the 6th lead go to the first sales person, etc. Upon closing down for the day, the next day would pick up right where it left off as far as who to send the next one to. I'm assu…

Hi, I am looking for a custom function which should give me square meter area of bubble wrap if I give weight, length, width, hight. This is just to calculate how much bubble wrap will be required for any packet. Thanks in advance for your help. Regards, TA

As most people here probably know, the Let ( ) function can be used to define a Local variable. As such, it is possible to build a custom function that defines such a variable, and it is further possible to set said variable to a value including itself. An example would be the following custom function, ErrorList, consisting of the following calculation: Let ( $ErrorList = List ( $ErrorList ; Get ( LastError ) ) ; "" ) If a Set Variable script step sets the same variable as a custom function like the one above, e.g. Set Variable [ $ErrorList ; Value: ErrorList ] …the script step will run appropriately, so long as the contradictory variable—in this case…

I'm working on a time clock solution and I could use an elegant way of calculating the day of the week based on a user preference of when they start their work week. Some employers start their work week on Sunday, some on Monday, etc. The built in day of week function is based on Sunday being the first day. I created a preference where the user sets the day of week they want their pay periods to start on, for example 2 for Monday. My goal is be able to take a timestamp, hand it to a custom function and get back the "adjusted" day of week. So a timestamp on Monday would return 1, Tuesday would return 2, etc. If they set the work week to start on Tuesday, then a …

I have two fields, one called Price and the other called Insurance. I need the Insurance field's calculation to be based off the price field obviously. According to UPS their formula this year for calculating insurance cost is thus: – Over $100.00, for each $100.00 (or portion of $100.00) of the total value declared $0.90 – Minimum $2.70 I can't for the life of me figure out the formula to calculate insurance based on the price and applying the minimum. Up to 100.00 is free. After 100.00 the cost is .90 per 100.00 or portion of 100.00 with a minimum charge of 2.70.. So basically <=100 - Free $100.00 - $300.00 = 2.7…

Hi, I am new to Filemaker and would need some help in the following issue: I would like to determine the fully qualified field name (<table name>::<field name>) assigned to the layout object (text field e.g.) that has the focus. Important condition, that the active layout has objects (text fields) from more than one table and the function/procedure should be able to determine the fully qualified field name for each of these objects. Any help kindly appreciated.

Have a system that creates schedule inspections for months in advance. Trying to refactor my functions and calculations to make it recursive so each date that is shifted to avoid holiday and off days are also tested to make sure that the suggested date does not fall on a holiday or off day. here is my CF that I am using to test for holiday. /* * ===================================== * BusinessHolidays ( theDate ; return ) * * RETURNS: * A ¶-delimited list of the dates or names of observed holidays in the year * * PARAMETERS: * theDate for extracting the year * return 0-8 for dates for names minor major * * DEPENDENCIES: ValuePosition * * NO…

Hi Guys, I have been looking for an easy way to debug CF's, and stumbled upon CF Explorer, but that was developed for FMv7 and hasn't been updated afaik. The original website isn't there any more. Are there any tools/plugins that can be used to debug CF's? Similar to the standard debugger? Thanks!

I use a formula to round a number to 1 decimal point and put brackets around it before plugging it into a text document. This works fine for most numbers but it fails for numbers ending in zero. They are instead returned by the function without any decimal, 1 nothing instead of 1.0. That's a bug as far as I can see. If I specify 1 decimal point and get none from the function, I consider the code broken. input: numbers with variable decimal points, e.g. 2.345 and 1.0 formula: "(" & Round ( final grade ; 1 ) & ")" output: 2.3 (correct) and 1 (incorrect) expected: 2.3 and 1.0 How can I work around this? I'm running FM Pro 12 Adv…

I've been working on a series of custom functions for building SQL queries. As I tailor these Custom Functions to meet many different situations and combine them in many different ways as well as making some recursive, I am running into situations where I can send a field reference and other times when I need to send a literal name of a field. What I mean by that is for instance is I am either sending ~SQLfield ( Table::field ) referencing the field or ~SQLfield ( "Table::field" ) using literal text. If I send as a field reference such as ~SQLfield ( Table::field ) The following works Let ([ ~field = GetValue ( Substitute ( GetFieldName ( ~fieldRef ); "::" ; …

Name & Parameters: [color:red][big] in between "btwn" ( text ; first ; fo ; second ; so ) [/big] Description: I use this function a lot to find text, in between things. the parameter "text" is the whole text that you're searching in. "first" and "fo" identifies the first thing, that you're looking after. "first" is the text and "fo" is the occurrence. i.e. if you wanted to start at the 5th time a field says "name: ", first = "name: " and fo = 5. if you wanted 5th from last, fo = -5 You're second boundary is relative to the first boundary (not relative to the whole text); it's as if anything before the first boundary, doesn't exist. You iden…

Is there a known calculation or "Get" function (I can't find one) that will identify if the Windows OS screen DPI setting is set to "Large Size" or 120 DPI (125%)?

Hey Guys I've wrote this function but just can't get it to work. The plan is to inject some content (template) and for it to iterate through each word looking for a formula, i.e. {{If([[CarMake]] = "Volvo"; "Volvo" ; "Other" )}} and evaluate it. I think the calculation I have is working fine, but I can't quite manage to work out how to loop through each word in the content. I've tried to reverse engineer Ray Cologens Mail Merge formula as that custom function kind of did what I needed, but I didn't need all of it. Thanks in advance. MergeText(Template) Let([ start_pos = Position(Template; "{{"; 1; 1); end_pos = Position(Template; "}}"; …

I hope this is the right forum to post this. Using FMP exclusively on Mac OS. I want to be able to have a document on the HD and drop it on the DB somewhere (container field or?) to create a direct link to it so it can be opened (clicking it to open) from within the DB. I like to do the same thing for folders. Is this possible? And if so how? If it is please remember, in your answer, that I am a FMP beginner.

Simple question (I doubt the solution is as simple). QUESTION : Is it possible to do turn data structured like this; Into this;

I’m trying to make a recursive function that counts to 10 and stops. Right now it just keeps counting. What am I doing wrong? MyCounter ( Number): Let ( [ $i = $i + 1 ]; Case ( $i < 10 ; MyCounter ( $i ) ; $i ) )

Greetings. New to the forum, using FMP14 on a Mac running Mavericks, FMP novice, aviation industry. Right... I think the best way to get to where I need to be is provide a little background: I'm working in what would politely be termed a 3rd World area -Papua in Indonesia, to be precise. There is not a lot in the way of structured organisation at a professional level here. I have developed (over the last 4 years or so) a database of navigational waypoints which we use here daily. I have sourced the data from a fairly diverse range of sources and now have a database that is really quite useful, after having undergone several structural changes as I have learned where …

We have an inventory application that allows users to add items to the database one at a time. These are not brand new records but rather records which correspond to pallets where you have to change the quantity in a particular location. The office would like them to switch to table view and modify several at one time. So we put a button on the form which switches it to table view but want some way to protect existing data so they don't mess up any other records. For example some record and error checking when making a change on a field before it is changed completely. What is a logistical way for handling editing data in such a way? Thanks

Hi, Once again I seek the expertise of this forum to prevent driving myself mad. Last year, Comment assisted me with a formula to achieve this same result but it was in the contact of an If/EndIf in a loop - Omitting records. I need to achieve this same thing but in Find mode. A table with four fields relating to a medical treatment environment: - Episodes:DateOpened (date field to record when a client was admitted) - Episodes:DateClosed (date field to record when a client was discharged. If this field is blank, then the client is still in the clinic) - Episodes:SearchDateFrom (global date field for the beginning of the search window) - Episodes:Searc…

Hi there Under Pro 13, QuickTime was available under Insert. Now with Pro 14, it is gone. This makes my life miserable. Is there a way to run wav file in an other way?

Hi. I need help wrapping my brain around this problem. Assuming the following result from a SQL query: product1,A,B product1,C,D product2,U,V product2,W,X product2,Y,Z product3,K,L How would one go about transforming it into this using a CF? product1,A,B,C,D product2,U,V,W,X,Y,Z product3,K,L Here each product has only one row and all remaining field data for the other rows with the same product in the first result is concatenatied into this one row. Or even into this { "label": "product1", "data": [ {"start": "A","end":"B"}, {"start": "C","end":"D"} ] }, ...etc This is actually the final JSON format I need.

Hi, I've been using Geoffrey Gerhard's FormatNumberAsText custom function (http://www.briandunning.com/cf/871) for quite a while and have found it very handy. It accepts a number as input and outputs a text string formatted to a specified number of decimals (among other useful things). In my solution, I've always specified 8 digits for the number of decimals and had no problem, but a recent project I've needed to specify 10 digits. With that precision, the function sometimes returns a number in scientific notation rather than decimal - for example 0..0e+107440 when I was hoping for 0.0000107440. I've looked at the function to see if there's anything I could cha…

Hi all i have a problem, i have one DB, in this db i have a vehicle and month washing. If possible to create one field to insert a different counter if change a veicle and the month?

okay so I tried to make a custom function but failed. I have two lists and I want to make a single list but remove any duplicates. Ideas? If I can just get started, I would like to make this cf by myself but I do not know how to start. I need recursive, right? Take one list and loop it and compare to second list and then what? And happy early new year!

How would you capture the first and last date in a found set of records and then use that as a label on a report? User case: person does a date range find in a set of records i.e. 2015/05/01...2015/12/28 How do you convert that info into a text label for the reports that says "Totals from: May 1, 2015 to Dec 12, 2015"? If there is a custom function out there that would do this please point me to it. Thanks!

Posting this with permission from SeedCode. I have seen similar methods that require an additional function to pass the parameters in, but it doesn't seem to me to be an improvement. I've also seen methods that require more complex structuring of the parameter. What I like about this method is that once you create the function, it is so simple to use. I call a script, and for the script parameter, pass in as many name/value pairs as required, e.g. "id=" & IDfield & "; name=" & nameField & "; type=widget" // etc. At the top of the script, I generally start with: Set Variable( $param ; Get( ScriptParameter )) ... and then parse o…

From a production run I am trying to pull specific sequence based on the size of the run for quality control testing: Example Function Parameters: RunSelect ( quantity ; selection ) RunSelect ( 200 ; 2 ) The goal is to enumerate the list from 1 - 200 and then randomly choose a sequence number: Based on a random first 5 or last 5 so the result would be a random of 1-5 and between 196-200 Result: 3¶196 If the selection size was only 1 then it should choose randomly from the front or back of stack then choose a random from the that first or last set of five. From a mathematical standpoint are these two essenti…

Hello, I need help creating a VLOOKUP formula for the following situation. I have two tables on sheet. I want a VLOOKUP formula that will compare the data in COlUMN A to the data in COLUMN N. If the data matches exactly, I want measurements (COLUMN O) to be entered into COLUMN G (TABLE 1) and the Weight (COLUMN P) to be entered into Column F (TABLE 1). Can someone help me out with this? Please check out my attachment. Thank you!

I have a Send email script where I pass in a four parameters via a list in this order: email Address, Subject, Attachment Location, Body. Since the email has carriage returns using Set Variable;$parameters; Get(scriptparameter) Set Variable $body; GetValue($parameters;4) //will only yield the first line of the body parameter. Instead, I've use a substitute on $parameters to turn email, subject, and attachment into "" Now I am left with 3 blank lines in body. Is there a delete line character or a way to remove the leading carriage returns from text? This is where I'm at:

Hi. I am looking for a function that allows me to determine if two date ranges overlap. Within a function I can check if a single date sits within a range of an upper and lower date (see below). It would be used to check hospital attendance for any given client over a reporting period. e.g. Client X was in a hospital bed from 30/4/2014 to 15/7/2015, and we want to see if this client was in situ during June 2015, so the function would be checking for a boolean result of two date ranges overlapping 30/4/2015...15/7/2015. Let ( [ LowerDate = DateCheck + LowerLimit ; HigherDate = DateCheck + HigherLimit ] ; If ( DateCheck ≥ LowerDate and DateCheck < HigherDa…

is there a way setup a button to delete the first line of text in a field? I have a field on my layout that I parse address info from into the corresponding fields in my database. after I parse the text I'd like to click a button and delete the line of text from my copypaste field I just want to remove the first line of text without having to manually highlight the text then delete it. I'ts actually going to be removing the top 6-10 lines but I figure I can have it repeate or just press the button a couple times if I need to remove more than one line. I tried a Trim function and replace but the text varies each time so it's not always a set number of character…

Hi, so i am new to filemaker and kind of new to programming. I was good with c# around 5 years ago but i have seemed to forgot most of it. Anyway, so im trying to build a database for myself. Let me try to explain what my problem is. So, i currently have a database where i have editied and changed up the 'Content Management' template on filemaker pro advanced v13. I have 3 products on the form (record). Each product has a [contract end date]. So, 3 [contract end dates]. One for Electric, one for Gas and one for Telecoms. As you can see in the image above. Right now when i populate the fields and go to list view, i get this... Each (record) or business, has 3 contr…

I've been fiddling with this all night and just cannot grasp it.... Problem: I need a CF that displays the contents of a field from the last record in which the field contained data. My thinking tells me that this could be done with Case ( not IsEmpty (GetNthRecord(Field;Get(RecordNumber) ; Field ; GetNthRecord ( Field ; Get (RecordNumber) -1 ) I think the concept is close to what is needed, but it's not working. Am I missing something? Bob

Hi Here is the function which will give the value position in a list. ValueCount ( Left ( $ItemList ;Position ( "¶"& $ItemList & "¶" ; "¶" & $SearchString & "¶" ; 1 ; 1 ) + Length ( $SearchString )) ) Example, $ItemList = "1¶2¶14¶5¶222¶12¶22¶42¶11¶23¶44" $SearchString = "22" Result = 7

I know there is a custom function that is equivalent to Excel's VLOOKUP, or at least to find exact matches. The Excel function allows for it's third parameter to be FALSE (find exact match) or TRUE (find closest match). I have a table of Timestamped records that need location positions. I have a second table of GPS track location positions that are Timestamped every 10 seconds. I need the function to be able to find the closest matching Timestamp and grab the location from the GPS table. Is there a solution for this? I'm using Filemaker Pro 13 Advanced. Thanks.

I am trying to install a custom function from http://www.briandunning.com and can't see why I am getting an error message. The message is "An operator (e.g. +,-, *, …) is expected here." The function is : Let( [NumbersOnly = Filter(Phone; "0123456789"); NewNumber = Right(NumbersOnly; Length(NumbersOnly) - 1); NewFormat = Right(Format; Length(Format) - 1)]; Case( not IsEmpty(Format); Case( Left(Format; 1) = "#"; Left(NumbersOnly; 1) & PhoneFormat(NewNumber; NewFormat); Left(Format; 1) & PhoneFormat(NumbersOnly; NewFormat) ) ) ) The error occurs on the opening parentheses here "PhoneFormat(NewNumber;)" I have this same function working perfectly …

I have a function with generates acronyms, handily found here. It needs refining to NOT give the first letter if the field has only one word. In that instance i just want the single word. Acronym(type) Case( WordCount (type) > 1; Upper ( Left (type; 1)) & Acronym ( RightWords ( type; WordCount( type ) - 1)); Upper ( Left ( type; 1)) ) Assistance always appreciated. Request for Information = RFI Advice=Advice

Hi Have a field "roomname", each with a roomarea. For example tutorial 1 tutorial 2 Classroom Lecture theatre Store office What I would like to do is create a calculation which shows the area when it is a particular "type" of room. For instance to create a field "teaching space area", I could use Case( patterncount(roomname,"tutor", or patterncount(roomname,"class", or patterncount(roomname,"lecture", Roomarea) Then I can do a totals , counts and size breakdowns on these spaces. In an inherited spreadsheet the "list of names" might be quite extensive and never consistent and it would be a lot less cumbersome to offer a list of names in a custom …

Hi all, I want to write couple of complex statements like following : switch (day % 31) { case 0: month = (day / 31); day = 31; break; default: month = (day / 31) + 1; day = (day % 31); break; } //above code is in java but I coudn't find any way in FM to make me able to do such. All I know in FM is a very simple case something like : result = Case (test1;"result here";test2;"result 2 here";"def…

Hi All, I have written one Custom Function To get the list of Product IDs from the current found set. Custom Function Name: GetProductIDs Parameter name : FountCount GetProductIDs ( FountCount ) = --------------------------------------------------------------------------------------------------------------------------------------------------------------- If ( FountCount > 0 ; GetProductIDs( FountCount - 1 ) & If ( FountCount = 1 ; ""; "¶") & GetNthRecord ( Products::Product_ID ; FountCount) ; "" ) ------------------------------------------------------------------------------------------------------------------------------------------------…

There is probably a simple solution to this problem that I'm not seeing but I've read through my entire 900 page FileMaker Pro Missing Manual and I can't find the appropriate answer; I'm using FMP 13 Advanced and I have some basic experience writing scripts, etc. Here's The Goal: To start each day with a screen report of tasks to complete for my client files. I want a report that says 'today you must take care of the following items..." To keep it simple, I have a client table & a transaction table. For each transaction that I handle for my client there is at least 25-35 documents to complete. Certain documents are completed at the time of sale and other …

Good Afternoon Everyone, I am junior developer that believes i have run into my first custom function problem. I am looking to filter a portal of of dates and to do so I would like to use a drop down list of date range something like this 6/3/2011- 6/3/2012 6/3/2012 - 6/3/2013 6/3/2013- 11/20/2014 However this filter needs to be dynamic based on a members join date. In this case it is 6/3/2011. The date ranges are 1 year based on that initial joining date till the final year is is less than a complete year and instead ends at the current date. I hope to be able to accomplish all this in a calculation field that uses a recursive FM custom function to kee…

Hi I need help in creating a custom function to calculate a salary increases based on the Starting Monthly Salary. Here's my scenario; I have 2 Tables Employee --< Employee Pay The EMPLOYEE table will contain a Field with the Starting Salary; this will be the base value of the employee. Anytime the employee receives an increase, that will be added to the EMPLOYEE PAY table. In our organization, increases can come as a Flat amount or at times a percentage increase. Is it possible to create a Custom Function for this? What I had in mind was to have a Increase Type field in the Employee Pay table, and depending on the selection. Also, the calculation wil…

Good morning. I have found the following conditional summary function on Brian Dunning's site: http://www.briandunning.com/cf/1367 For the most part it works OK, but I am having issues with it. I have contacted the author (Koji Takeuchi) who has kindly offered to assist however is a busy man and I don't want to disturb him again. Attached is a roster type solution, whereby a company has a number of engineers and a number of clients. Each engineer is assigned to a particular client by default, but each month any given engineer may attend a client site. The idea of the Conditional Summary is to add up the total hours assigned to each engineer (in the top, cli…

Somewhere I downloaded a sample and it has a folder called custom functions. Inside it are fields with extension of fmfn but when I try to open them, this new Mac can not identify them. Can someone explain how I can use them and how to create them?

Today is my birthday so my treat to myself is playing with several custom function concepts that I've wanted to perfect. An fp7 file attached shows example data I'm working with: Rules: I want the value immediately following the 'select' to the end of its line The items may not be in order No consistency in number of items in the group Groups always separated by two carriage returns (this is the only real consistency) No characters, spaces or carriage returns prior to the text string nor after the end of it The 'select' can have colon or not or space after colon or not so it can't be depended upon or it might not have a colon at all Sample data: Name:Churchill El…

this is a bit out side the norm of "scheduling" but I am trying to derive a set of dates that match this logic and trying to some way make it as a black box approach as possible a recursive custom function because i need to possibly use it in a calculation in virtual list for a series for records. I have a table of known holidays with a flag that determines its a "major" holiday were no work is done and lesser holidays where some work can be done. My date cycle is a set of 4 inspections which i could pass in as a list, or pass a single Date (the actual treatment date) theDate - 3 theDate - 1 theDate theDate + 1 7/1/14 7/3/14 7/4/14 7/5/14 Should a MAJOR holida…

Hi, given a list of this 30 values 1 1 18 1 18 30 1 18 30 39 1 18 30 39 45 1 18 30 39 45 46 1 18 30 39 45 41 1 18 30 39 45 46 41 1 18 30 39 46 1 18 30 39 41 1 18 30 39 46 41 1 18 30 45 1 18 30 45 46 1 18 30 45 41 1 18 30 45 46 41 1 18 30 46 1 18 30 41 15 18 30 46 41 15 18 39 15 18 39 45 15 18 39 45 46 15 18 39 45 41 15 18 39 45 46 41 15 18 39 46 15 18 39 41 15 18 39 46 41 15 18 45 15 18 45 46 15 18 45 41 15 18 45 46 41 I would like to be able to get list of values with fixed words length, in this case 5 words values: 1 18 30 39 45 1 18 30 39 46 1 18 30 39 41 1 18 30 45 46 1 18 30 45 41 15 18 30 46 41 15 18 39 45 46 15…

I have a library of ~25,000 photographs, each in a (linked) container, 1 photo per record. The photos have standardized ID tags of "Site Abbreviation" + "Timestamp". These text tags are unique because photos are never taken less than 1 second apart: 215MAIN-2014-05-02_13-24-52 152JBAR-2013-01-02_02-21-13 152JBAR-2013-01-02_02-21-12 I would like to generate the shortest-possible alphanumeric unique reference code for these photos using a custom function. I plan to embed (watermark) the code into each photo so that someone can easily locate any particular photo in the database by doing a Find on the unique ref. code. My ideal output would look something l…

Hi. I am trying to incorporate the 4 custom functions from filemakerstandards.org that are set up to suppress script triggers: http://filemakerstandards.org/display/bp/Suppressible+Triggered+Scripts but am having a bit of difficulty and inconsistent results. Several of my layouts contain an onLayoutEnter trigger that calls a script. At the top of this script, I've put: If [not TriggersAreActive] Exit Script [] End If On a script that contains a go to layout step, and where I want to suppress the onLayoutEnter script trigger, I've placed: Set Variable [$!; Value:TriggersDisable] (then the parts of the script that go to the layout) Set …

Attached is a sample of the issue. I have a list of IDs (gathered by the new summary option of ListOf in v13). I need to take this list (yellow) and convert them to their description value, produce a unique set of results, sort them and put them in a concatenated string as: Apples, Cherries, Grapes I can use two value lists (one with the IDs and the other with the values ... neither of those fields are ever empty) and I can find the corresponding value to a single ID. And I can even take a text list and sort them against the value list using FilterValues(). And of course turning a list of values into a string is easy also. But how to handle many values, re…

I'm having trouble getting my brain to work with the concept of recursive functions and how to build them. (Although, that could be the allergy meds . . .) I have some script steps that loop through a date range and increment a counter if the day in the current iteration of the loop is a weekday. If I'm grasping the process correctly, I should: Check to see if we've reached the end of recursion Do the increment of the counter If not the end, call the function and increment the parameter. In my case, that should look something like this: Case ( startDay = EndDay; If (DayOfWeek (startDay) = 1 or DayOfWeek (startDay) = 7; counter; counter + 1); myDateFunction …

I am trying to write a recursive custom function which looks for any match between two lists and simply returns a 1 if there is a match or a 0 if no match exists. A list may contain 1 or more values. Here's what I have so far: Function: DivisionAccess ( rec_division ; my_division ) Let ([ thelist = my_division ; value = ValueCount ( thelist ) ; access = If ( rec_division = GetValue ( thelist ; value ) ; 1 ; DivisionAccess ( rec_division ; my_division - 1 ) ) /*access = Case ( rec_division = GetValue ( thelist ; value ) ; 1 ; DivisionAccess ( rec_division ; my_division -…

I've been trying to write a Custom Function that will give me a list of unique combinations between two occurrences of the same list. The nearest I've come is with this Function, using the following list as an example 1,2,3,4 ListCombinations ( listofValues ; separator ) Let([ listLength = ValueCount(listOfValues); lastValue = GetValue(listOfValues; listLength); lesserList = Case( listLength > 1; ListCombinations(LeftValues(listOfValues; listLength -1 ); separator); "") ]; Case( listLength = 0; ""; listLength = 1; RightValues(listOfValues; 1); /* Ensures ¶ at end */ lastValue & "¶" & lesserList & /* already terminated by ¶ */ Substitute(lesserLi…