Calculation Engine (Define Fields)

Hey.. I have two fields, date_created and date_fixed.. How can I make a calculation that says the number of days that went between those two? If I for example choose 2012-09-27 in created field and 2012-09-30 in fixed field, then the calc should have been 3 days. Any suggestions?

I have the following fields nameFirst nameMiddle nameLast nameSuffix streetNumber streetName streetOther suite city state zip country an example of the result I need is Devon G. Brown, PhD. 124 Broadway NW, Suite B Denver, CO 80204 USA but sometimes not all information exists such as nameMiddle, streetNumber or suite and I want the best way to list it so that those periods, commas, carriage returns or spaces do not list by themselves or the name 'suite' does not list if there is no suite. I began to test for not IsEmpty all the way but nothing I try comes out right. I even tried using List because I remember a post which did that but that h…

I have an indexed calculation that is concatenating the first and last names of a contact together. The two fields, first name and last name, are looked up from a contacts table when the related value id_contact value is inserted via a script. The problem I'm having is that for some reason when the value is inserted via the script, only the last name field is looked up. The first name field stays blank. When I insert the value by hand, both fields are looked up. What can cause this behavior?

Hi Folks, I thought I'd pretty much worked out how to generate a ranking field for pupils I teach. The kids nearly all take a baseline test when they join the school. This is a national test with pretty accurate results (though false negatives are possible). what I'd like to do is generate their percentile - ranking order (out of 100) based on all the kids at my school who've taken the test. I thought I would: Filter for records that have valid test scores. Count these records. Set $TotalTestScores Sort them. Work out pupils place in that ranking order. Set $RankTestScores Calculate ($RankTestScores / $TotalTestScores) * 100 But - most of that I can't do i…

I am trying to export a selection of records. I export them into a csv file. One of the fields is made up of several other concatenated together. e.g. field1 & " " & TextStyleAdd ( field2; Bold ) & Case( IsEmpty(field2); ""; "") & "¶" & TextStyleAdd(field3;SmallCaps) & "¶" & field4 & "¶" & TextStyleAdd ( field5; Italic ) & Case( IsEmpty(field5); ""; " ") & " " & Case( IsEmpty(field5); ""; "¶¶") & field6 & Case( IsEmpty(field6); ""; "¶") & Score_100 & Case( IsEmpty( field7) ; " "; TextStyleAdd ( "/ "; Subscript )) & " " & Year( Date) & Case( IsEmpty(Date); ""; TextStyleAd…

Hello Everyone, I am using FM Pro 13 I have a table containing a list of ‘Purchase Order Numbers’ supplied by a Retailer together with a ‘Date Used field’. These numbers are used as a value list in a drop down box in a field in a portal (Purchase Orders) on a Project Layout. When a purchase is required a new Portal row is started and a Purchase Order Number entered from the drop down list. This is followed by a Date Used field. I would like the Date Used field on the Portal to auto enter the date into the Date Used field in the Purchase Order Numbers table. The idea being that the drop down list would include the Purchase Order Number AND the Date Used so …

I often use Nisus writer pro for a lot of word processing. Interestingly, they have wonderful wildcard functions to find certain information. so for example if you had a sentence like "Maybe I can find 4859043 people there." and that sentence was to be used in many examples like: "Maybe I can find 6854 people there." "Maybe I can find 45443 people there." Then if you were to do a find/ replace the numbers become a wildcard function called "anyword" And if I were to write the find It would be like this.. "Maybe I can find ANYWORD people there. And it would select the information I am looking for. I guess it would be some type of variable function…

Dear Friends,   I want to get a filter table list records by time frame selection. I have tried to to do so by layout selector on sort listing the time frame values list i.e. by week /by month/by year/ . I am unable to do so. In sort list key it only showing the item name only.  Thanks  Here are some pics for refrences

Hello Gurus, Can you please help me with my cross tab report. In my database, I want to get the sum of each activities in column. I've been experimenting for four days now, but I cant get it into work. I would appreciate if anyone in the forum can share their spare time opening my attached sample file. Thank you so much. Time.zip

This one is flummoxing me. We have due dates that are calculated two or three months in the future. So, two months from January 15, 2015 calculates to March 15, 2015, and that's easy enough to do. However, say we are doing three months from January 31, 2015. Obviously, April 31, 2015 does not exist, so we need this to go to the *last* day of April, April 30, 2015. If I use the Date function, and plug in Date($month+3;$day;$year) for January 31, 2015, FM returns May 1, 2015, which totally makes sense, but unfortunately doesn't work for what we need. The only solution I can think of is to kludge my way through, where I look at $newmonth=$month+x and $day, and th…

I'm using FileMakerPro Adv 13 Considering There are records like these below 1 Orange 2 Apple 3 Strawberry 4 Apple 5 Lemon 6 Apple 7 Orange 8 Melon I have a value lists called "TypeOfFruits" then If I execute this function Substitute(Substitute ( ValueListItems ( Get ( FileName ); "TypeOfFruits"); "¶"; "n") ;" ";"") I get this output OrangenApplenStrawberrynLemonnMelon It is shown on a page just like this Orange Apple Strawberry Lemon Melon If I want to count all of them and want an output like this, how can I change my function above? (sorted by the number of count) 3...Apple 2...Orange 1...Strawberry 1...Lemon 1..…

I have been using ISO Filemaker Magazines Permissions Template for going on 4 years now without issue. However, recently I have started to notice some very odd inconsistencies with permission checking. If you're not familiar with this Template, it compares a users set of "Permissions" (in this case, a list of text strings representing the users permissions) against a Script Parameter that contains the permission to check. I have started to encounter instances of this check failing even though the global field clearly contains the Users permission. The check is performed by checking if the value of the Script Parameter is contained within a Global field that contains …

Hi all This is sort a continuation of this idea (for which I received a great deal of help): http://fmforums.com/forum/topic/91455-time-elapsed-calculation-causing-slowness/ The above finds the time between two timestamps, ignoring any time outside mon-fri 9-5. I'm not sure if the above example will help but there it is just in case. What I now need is the ability to acquire a new timestamp result by adding hours to the original timestamp, however, as with above, ignoring out of office hours. e.g. If I wanted to add 7 hours to 15:00 02/01/2015 [Friday] then the result would be 14:00 05/01/2015 [Monday] I really can't get my head around how I …

Hi... Brief history. I used to be very proficient in FM... built databases is V9 in about 2010. Now I am rebuilding all my databases and making them more proficient in V13 for 2015. Yes, they rocked for 5 years! Anyway, need help with a simple code. It is slowly coming back to me... Case (DayName ( SOME DATE ) = "Monday" ; "MON"; "Tuesday" ;"TUE"; "Wednesday" ; "WED"; "Thursday" ; "THUR"; "Friday" ; "FRI"; "Saturday" ; "SAT"; "Sunday" ; "SUN" ; "HELP") I constantly am returned "HELP". What am I missing?? My goal was to abbreviate Day of the week, and Months etc to get them to fit in a small amount of space. Thanks!!

Sometimes i would like to remove a comma from a field sometimes i might want to remove the word "of" in a field What is the simplest way to do this?

I have another example of a file I need to parse. All Saints' Episcopal Church, Brighton Heights, PA this is the way it looks But If I change the ending of it I can do what I need to do. http://www.episcopalchurch.org/parish/all-saints-episcopal-church-brighton-heights-pa the beginning is always the same http://www.episcopalchurch.org/parish/ all saints' episcopal church brighton heights, pa needs to change to all-saints-episcopal-church-brighton-heights-pa I need to put in the '-' and take out the (') if there is one and the comma after the city. Then I will be able to import the whole batch and get this work done. Thanks

I have been scraping the web for information. When I do a copy of the entire page I can get it pasted into a field. The only problem I have is how to remove the Blank white space before the text starts ex. this is where the text starts the line before this is what I want to get rid of then there is only to remove commas etc. I don't know how to remove the commas but this is the hard part. before "this is where the text starts" there is a whole line of white space. I don't know how to get rid of it.

Hi I have a question about grabbing a particular line in a paragraph? I would like to know how to grab or parse Line 7 in a text paragraph.. I pledge alligiance to the flag of the United States of America and to the republic in which it stands one Nation under God indivisible with Liberty and Justice for All I would like to get the line 7 "one nation under God" into my field "line 7" I would like to get the line 4 "United States of America" into my field "line 4" How can this be done? Thanks

I am trying to use the Goya FileExists function to produce a boolean result. I use my database on 2 machines and they are synced using sugarsync. The point of the boolean calculation is to permit a button to be appear if a PDF file with a given name exists. I am using the Get(DocumentsPath) to provide the first half of the path. I get the feeling that the goya function is not recognising the syntax of the result of the Get(DocumentsPath) calculation. Any ideas?

Hello! Please help a newbie! I’ve got a FMPro13 database which covers UK music charts. There are 2 related tables: [Dates] and [Runs]. They look as follows: [Dates] contains field as below: <idDates> index field, autonumber <Date1> date field, a date of publishing the chart [Runs] contains fields as below: <idRuns> index field, autonumber <Place> number field, position on the chart <idDates_fk> number field, a foreign key which links both tables <Title> text field, title of a single <Name> text field, name of the artists I've created a layout based on [Dates] table - titled …

I have a music artist contacts database with fields for social media URLs. On the side of the layout I have image icons (Twitter, Facebook, etc..) that are buttons with corresponding scripts to "Open URL" typed in the fields. My desire is, if the URL field is empty, the button script can manage a dialog that reads (ex.) "Instagram URL does not exist" and then the user clicks "Ok" then the script ends. Likewise, if the field contains a valid URL then the script executes as normal, which is the basic "Open URL" function. Thanks for your help!!!

I have two tables, matters and individuals, which are related by an individual's name. That is, a user can select in the matter layout for a given matter any existing individual to assign to the matter, via a drop-down box value list. This works great, unless the individual doesn't exist yet. Previously I had required the user to go to the layout for individuals, enter the new individual, and then go back to the matter layout and select the newly entered individual. I therefore improved this workflow by having a button in the matter layout, which when selected, permits a user via a popover to enter a new individual and his/her relevant information in the individ…

Note that this is just an example that does not make sense. It's just to illustrate my problem. Field "A" is Let ([c=Conditions]; Case ( Evaluate(GetValue(c;1))=1; List(GetValue(c;2);GetValue(c;3)); Evaluate(GetValue(c;4))=1; List(GetValue(c;5);GetValue(c;6)); Evaluate(GetValue(c;7))=1; List(GetValue(c;8);GetValue(c;9)); "" ) //fin case ) It's pretty ugly and the project's real implementation uses a custom function but the result is the same. Field "Conditions" is a plain text field which contains conditions to be evaluated by the field "A". Each condition has 3 lines (the condition and 2 other numbers). When a condition is true then Field "A" retrieves the …

Hi I am pretty sure there might be a way to get the url of a web page into another filemaker field. Could someone advise me on how that might be done? Thanks

I have need to use the open URL command in a script to open info on specific phone numbers So if I choose the open URL and go to specify, I select the "phone" Field. which contains for numbers in the following format. (333) 444-5555 for example when I paste that type of file in the Address bar of my browser I get right to what I am looking for. But with the Open URL function I get www.(333) 444-5555.com which will not open. Is there a way to drop the www and the .com in the formula so it will just paste in the (333) 444-5555 only?

Apologies if this is a simple question but i'm not having much luck finding a solution. It's late so it may just be my brain not working. I have a table full of website categories called "Categories). Here are some two example records: Parcent Category Category ID (123) Category Name (Tablets & Mobile) Parent Category (BLANK as N/A) Parent Category ID (BLANK as N/A) Child Category Category ID (456) Category Name (Ipads) Parent Category (Tablets & Mobile) Parent Category ID (123) Currently both the Parent Category & Parent Category ID fields are entered manually (Parent Category uses a drop down list). However I am trying to get th…

I have a table (A) with a field in it with the address as a string. There are 210,000+ records In another table (, I have 3 fields that make up part of the basis of the address as per above (state, postcode, suburb). This is an official list of postcodes and suburbs What I would like to do is do some sort of pattern count that would identify where one of the suburbs appeared in the legacy field in Table A and replace the three separate fields (state, postcode, suburb) with the appropriate information. i.e Table A Legacy Text = 28 Whatever Street, Brisbane QLD 4000 Suburb = {TBC} State = {TBC} Postcode = {TBC} Table B Suburb State Postcode …

We currently have two FM Pro licenses. I'm the primary database guy, and so wouldn't mind upgrading to Advanced to get various functionality, primarily custom functions. However, if I create a custom function, will the other license (my admin) be able to use them in her Pro version? I'd prefer not to upgrade both of us to Advanced.

Hi, I would like to make a calculation that substitutes a non-breaking space for an ordinary space but only between the last two words at the right end of the line of text. Would someone be able to give me some pointers in the right direction ?

I've got a repeating field as a calculation and need to put a value into a specific repetition value/location, but I don't want to have to run a script every time to accomplish this. For example, I have two fields, NumberField and RepeatingField[10] When NumberField = 5 I need a 5 to show up in RepeatingField[5] Can I do this in a calculation? If not, what's the best way to do this? This seems to trivial to me, but I'm looking for a elegant solution.

From the FM help I would like to put a base64encoded text into a list as a whole and not multiple items of 76 characters. How would I do that knowing that I also will have to base64decode that list item later.

I am creating a serial table. The purpose is similar to incrementing an invoice number which must start over at the first of the year. I do not want to use script (even server-side script) because it puts the dependency upon a process and I want it automatic. I want to use a serial table (I think) because it protects from collisions (record-locking or two users claiming same serial). I've attached what I've come up with so far. I am wondering if there is simpler way or if anyone sees any potential unidentified problems with this method. Thank you for considering this file. :-) I have been unable to break it in multi-user. When I create the record and leave …

For my cabinetshop we often need to calculate the thickness of tile backslashes. We build everything in metric but we communicate with our customers in imperial dimensions. The various options for tile thickness are selected from a drop down value list. For these values to be viable in a calculation they must be entered as a decimal. If the inputed value starts out as a fraction the end result is gibberish. The problem we have is our customers cannot sometimes relate to decimal equivalencies. They don't readily understand that .375 is the same as 3/8 inch. The attached file shows the results if we start with fractions or if we start with decimals. Is t…

I've googled far and wide and have not been able to find anything that gives clues to solving this issue. I'm trying to write a calculation which returns the number of "night time hours" for a given Punch record, which can start and/or end inside of or outside of pre-determined "night time" hours. Our data: A) A Punch record has two timestamp fields: ClockIn, and ClockOut. Any valid timestamps can be entered. Typically, the time stamps will be on the same day, different times, but, can also span from one day until the next day, bridging the midnight hour. We know the time that we want to set as the beginning of "night time": For this exercise, let's set it at 9:00 PM…

I have old excel files with figures I need to figure out. They look like this Date - Item# - number in stock These are inventory takes over periods of time. I have imported them all (over 360 files) into FM and I need to find out per year the amount sold per item. I have tried sub-summary with item# and I have found the year records I want. But how do I subtract the starting amount from the ending amount in this report? Do I need calculation with another relationship? The lowest date of each item would be the beginning inventory and the last date the ending. I sorted by Item number and then date but I still do not know what to do next.

Hi there, I'm using the web viewer to display product images that are stored on our web server. I didn't want to bloat our filemaker file and since all our images are already on the web I though using the web viewer would be the fastest way to show images. I'm using a calculation field that autg enerates some html which includes the image path and name. (see code below) "data:text/html, <html> <body> <img src='http://www.mysite.com.au/media/import/" & image & "' style='width:100%; height:100%'/> </body> </html>" That part works - I can see the image when it's present on the web server. One issue I'm having is that f…

I know date formats has been an ongoing complexity of FileMaker for years.... but here's a new problem (to me)... ----- The setup ----- File was built in Australia - using Australian formats. Orders::event_date = date field, with 19/09/2014 (Australian Format) in it Script on startup = Use System Formats [on] ----- Opening on a US System ----- When I open on a system using US formats, I see that: Orders::event_date = 09/19/2014 (US Format). All good. And: Evaluate("Orders::event_date") = 09/19/2014 (US Format). All good. ----- BUT... The problem... ----- Evaluate(""we will arrive on " & Orders::event_date & ", at"") results …

Hi all  I hope someone can stop me from tearing my hair out. I am building a database for an optometry practice. I am trying to build a recall  system within the database.  I have a field Recall_Name, Recall_Due and Recall_Date.  The idea is the user selects the recall name(e.g. Regular review) and sets the time in days, weeks or months.  the date the recall is due then shows in Recall_Date field. Recall_Name is a text field  Recall_Due is a text field Recall_Date.is a calculation field. (See attached screen shot)  It all works very well except for 2 time scales.  Both 1 month and 1 year recall show up blank in the Recall_Date field.  This is …

Dear All, I was reading what's on the forums for a calculation that can be used to workout the number of weekdays between 2 dates. My problem is that the only day off i have in the week is friday. Can you advice me how to modify the calculation below to work with 6 day work weeks with fridays off rather than saturday and sunday? It would also be greatly appreciated if someone can desifer this number string for me, 0012345501234544012343340123223401111234010012340, what does it mean? Many thanks for your time and help 5 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223401111234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) +…

I am currently trying to set up a report to do the following: Set up in the footer (preferably) or sub-summary, my page count info. On the right Have the total page count, of the complete printout show as i.e. "Page 1 of total 150 page Monthly Report" This I can do with scripting On the left Have the total page count of the sorted sub-group as i.e. "Page 1 of 3 of Daily Report 2014.11.30" I can not get the 3 info I would like to also merge the <<date for individual report>> if possible but not necessary. ​My main goal is to get the total for each daily report. Thank you

Hi, there I am trying to hide a portal under certain conditions and it works fine with one condition but the moment I am applying more then one it does hide anything at all anymore. Here's what I did. T16l_projects_NOTES||id_note|::Note_Type ≠ "Product Testing" or T16l_projects_NOTES||id_note|::Note_Type ≠ " Assessment" or T16l_projects_NOTES||id_note|::Note_Type ≠ "Trial" It all comes down to a drop down list of notes while the portal is only required for this 3 options anyone able to help. highly appreciated.

Hello, I’m creating a solution where I need to secure the main file, preventing copies to other computers. First with FM 11, I was using MAC Address and other security steps. But with FM 12, we have available the new function called Get(PersistentID), with purpose of this is to “return a text representing a unique identifier of the computer or device, like 78569d0bd40b898a64e7d08ccdea8220”. Now my questions: If I format the computer and reinstall a new or different FM 12 (not the same serial), there will be a different ID? The Persistent ID how is obtained? Throw hardware (like Mac address) or software (OS serial, FM Serial). Thanks in advance

I have a table of Services which has a date field called ServiceEndDate. This date is only filled in if a service contract is temporary. I want to make a calculation which says "Your service will expire on " & ServiceEndDate. Here is what I think If no service start date then it means the service was one-time custom service. If the service has no end date but a start date, then it is ongoing service. So my try is this Case ( IsEmpty ( ServiceEndDate ) ; "Service Contract in place" ; not IsEmpty ( ServiceEndDate ) ; "Your Service will expire on " & ServiceEndDate ; "Custom service" ) Problem is that if both dates are empty it says "Service Contr…

I would appreciate a little help as my head is getting sore from banging it! I have a 4 tables Students - pkStudentID, also contains name address etc Classes - pkClassID, also date etc SC_JoinTable - fkStudentID, fkClassID, fkCarnetID, payment method, payment amount so far they are working ok, and I can have students attending many classes, as well as each class with multiple students. Where I get stuck is how to structure my last table. Carnets - pkCarnetID, fkStudentID A carnet is a bulk purchase of Classes, in this instance of 6 classes (although that may change in the future) What I want to do is have a CarnetStatusField that switches to "inactive"…

Hello I have an email field in a Clients table that I would like to ensure has a valid email address to avoid any issues with typos and such I'm not quite sure how I would go about doing this Also, the field needs to be able to be empty as well

Hi, I cannot figure out how to calcuate a total_length field that can be stored so I can use it in another calculations. I need to find a percentage of something within a length of transect I walked. The transect is broken into multiple units. Each unit has its own number and date. There are multiple records in my database with different units/dates, but I need to calculate a stored data field for the entire unit length of my individual transects so I can determine the total percentage of substrates, etc, along only that transect- not others. The first thing I tried to do was when I created the cumulative length field, I clicked "summary" and then "fraction of…

Is there a method to determine date format. 1. Machine region is set to 'Canada': yyyy-mm-dd 2. I figure I would set variable = Date ( 1 ; 1-1 ; 2014 ), which displays '2013-12-31' 3. Now I need to get that as a string but no luck I always get: '12/31/2014' I am off track? End goal: a. determine the cursor position ( m, d or y ) in date field, which I have solved I am now just trying to have my script region independent. b. avoid a custom function

Hi all, I'm trying to make my code, more efficient. As far as I understand, global variables take memory that filemaker might use for other tasks. So, if this statement is correct, how would I free that memory? Should I set my variables to "" after using them? Any other way to do it? Thanks Regards. Mariano

im trying to get my solution to generate pricing off of three separate fields and I'm stuck. ex. If ( Package = "top 120" and Number of TVs = "1" and DVR = "no" ; "30" ; "" , If ( Package = "top 120" and Number of TVs = "2" and DVR = "no" ; "37" ; "" )) that returns "too many parameters" ex. If ( Package = "top 120" and Number of TVs = "1" and DVR = "no" ; "30" ; "") , If ( Package = "top 120" and Number of TVs = "2" and DVR = "no" ; "37" ; "" ) that returns "an operator is expected" if done properly there would be 24 different "prices" generated from the combination of three fields. 3 different packages, # or tv's 1-4 and with or without …

Hi Guys, I have built relational crm package. I have thousands of contacts and each contact can have multiple addresses which are stored in a related file. On a print layout, I want to display the current address without the use of a portal/filter as it messes my clean layout, so I think I can fairly easily achieve this by creating calculated fields in the contacts table using executesql to bring in only the current address or email addresses etc. My question is, if I introduce calculated fields (they would be unstored), is the calculation triggered only if I have that field on a layout I am using or go into the backend, or is the field always on and calculating i…

I have created a ledger with a layout for writing checks. I would like to have a calculation that would take the number entered for the dollar amount and convert it to text so that I would not have to type in the text for the number every time  I write a check. I found the calculation below on the internet, but I can not get it to work. I am getting the error message in the screenshot when I try to save the calculation. I have no idea where I have gone wrong. Any help would be appreciated. Thanks!  Choose(Int(Mod(Debit Column;10^12) / 10^11); ""; "One Hundred "; "Two Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six Hundred "; "Seven Hundred "; "Eig…

I have (still) a Members database. There is a field in Members:: called Photo. Photo is a container field. I want to produce a report whereby I show just members who have a NotIsempty(Photo) find. But, in setting up the FIND, the Photo field is grayed out?! Huh? Is there a work around for this? (I know I could setup a boolean field "PhotoYesNo" and then Find on it.... but am I missing something native?) Thanks Ron

Hello, I am having trouble putting a calculation together. I have 2 tables: 1. Main 2. chemotherapy. They are related by key fields: main:pk_main--->chemotherapy::fk_main Chemotherapy has a field called "chemotype" I am able to list all chemo types in a calculation on the main table as: Main: chemolist = list (chemotherapy::chemotype). This works fine and gives me a list of all of the related records to the main but I want to list only a few of the chemo types, specifically the ones that correpesond with another field in the chemotherapy table called "status" I want to list the chemo types with the status as "stopped" only. Not all of the chemo.…

Hi I just a came across Get (ScreenScaleFactor) but can't find any documentation for this function. It's not listed in the internal FM help, nor on the main FM help website, or at least I can't find it. Anyone got some docs or ideas? Thanks

Hi All I have been asked to add a page number to a three page layout in my database but the layout seems to be corrupt and only displays each of the three pages as page 1 when the page number object is added to the footer. If I duplicate the layout the same issue is seen but the issue is resolved if I create a new layout with the same layout objects copied over. Unfortunately I don't know how to get all the database scripts that reference the corrupt layout to reference the new (non corrupt) layout and I do not know how to fix the corrupt layout (I have tried to recover the database to see if it was fixed). Is there an easy way to create a new layout, copy…

Hello everyone. I just joined this forum as a complete newbie and have a question regarding inserting a URL into a record. I'm using Filemaker as a database to keep track of color mixing formulas. So far this has worked just fine, but I'm thinking it would be a cool feature if I could insert a URL for each record that links to a picture of the finished product I'm using the mixed colors on. I tried doing this by creating a button with a "Go to URL" command. This works great, but it creates the same link on every record in my database. I want to use a unique URL for each and some won't have a link at all. I'm probably missing something obvious (very new to Fi…

I have a simple check field with the letters A-G Depending on what the user clicks, I would like to simplify the results annotated as a range: ie. A-D,E,F A,B,E-G A,B,D etc Any thoughts, CF's? NOTE: just added an attachment to work with and noticed a sort has to be applied.

Hi All, I have an imported field from excel labelled as "username" e.g. username contains the following string: andy johnson (ajohn) at times, some fields are as follows: tpage () "ajohn", "tpage" are actually unique login IDs found from another table in another database. is there any method(s) to grab these login IDs for me to match my other table? Thanks heaps!

First post so bear with me of this isn't clear. I am putting together a DB that will have between 20-30,000 parts and I will have several classification fields from general to more specific. Lets use cars for example. Maybe with the following classifications. Type, # doors, color, gas mileage,etc. What I am trying to accomplish is to be able to search on any 1 or combination of the fields to add them to lets say a BOM. So I want to be able to see all white cars if that is my only search criteria, as well as being able to find sedans, 4 doors, white, 15-20 mpg if needed. I am wondering if a multi-key field is the best way to do this or if there is a better w…

Hi, I am working (better trying or playing around) with FM for some 5 years now. I am not a pro or anywhere near there but in course have come up with some db's serving my purpose well. However, comes the time comes the limits.... I am currently working on a simple invoicing module. (have set up a separate very small db for trial and testing as I do not want to spoil the Starting Point 4 solution template which I have amended to my requirements to an extensive stage already) 4 Fields, namely Current Date: Date Field (system time - have to refresh the window as no real time clock counting solution available for FM) Due Date: Date Field - Date when the inv…

Hi folks, When kids audition for scholarships, they perform on 1-3 instruments. I currently take the average of these three fields in a calculation field. Average ( Perf 1 grade ; Perf 2 grade ; Perf 3 grade ) * 3 (the *3 is just to give this score a higher importance compared with other test we run) I could really do with applying a penalty for kids who only perform on one or two instruments. How do I check for null fields and then subtract accordingly? Cheers! Mike

I have a calculation I use to know how much rent to bill for the next month (thank you Comment on that one). I am switching my rental rates from daily to monthly, so I need it to work like this: Rent: $1,000 MoveOut: 12/15/2014 It's November, so next month is December. December has 31 days, thus the rent is $32.26 per day ($1,000 / 31), times 15 days equals $483.87. I can't seem to figure out how to count the number of days in the next month... Also if the move out is anytime after December, for example, January 5th, then the rate would be $1,000 since it's a full month's rent. Any help would be GREATLY appreciated!

I have data within a field which consists of measurements that I want to extract. for example ProductSpecification Fork TS Air 27.5", Remote Lockout Fork TS Air 30 27.5", Remote Lockout, 100mm Fork TS Air 30 27.5", Remote Lockout, 120mm Fork TS Air 30 27.5", Remote Lockout, 140mm Doing a simple PatternCount is fine for say "100mm" but how can I do this for an entire range (between 0 and 500mm). For example PatternCount ( ProductSpecification; >500mm ) Thanks

I've noticed that I can't seem to use a wildcard for quick find with numbers. Is there a way to do this?

Hi there, I'm getting what seems to be some strange behaviour when I try to use the following calculation in the "Hide object When" option: Products::type ≠ "Spare Parts" or Products::type ≠ "Repairs" The Goal is to show the object if type contains either "Spare Parts" OR "Repairs" and hide it for any other option. If I use either of the two options on their own the hide object works, When I use them together it permanently hides the object.ie: it is hidden even when type DOES equal "Spare Parts" or "Repairs" Am I missing something really obvious here?

I have fields in the same table and each of those fields have numerical values ranging from 0 to 100. Assume the fields are j, e, f, f, w, i, l, s, o and n. Each of these contain values from 0 to 100 and I would like to exclude only the 6 top values to use in grading. Please be precised on how I can accomplish this and not just stuff like, use Max (x;y;...) and another this and that function. It will be much helpful if you be explicit since that's the ultimate end (to be helpful) ! Thanks in advance for those that are already putting in their time for this!

I have two files a filmaker file and a text file This is a sample of the text file · 9 First Congregational Ucc 41 Main St Hatfield, MA (413) 247-9540 · 10 First Churches ABC 129 Main St Northampton, MA (413) 584-9392 · 11 Chabad at the Five Company 194 Amity St Amherst, MA (413) 835-0085 I would like to parse the addresses into their respective fields which I have worked out already. But I need to find out how to format the import so that the ENTIRE address can populate a field called "parse" as we see it above There is a number before each address which I would like to send to another field called "number" I have enclose…

Having a bit of difficulty getting my head around this, appreciate any assistance, suggestions or guidance. I am making multiple calculations, as various fields have different deadlines, so this is not an "all in one" solution. I have 2 types of deadlines I am having difficulty with. Then I have one weird anomaly situation, that maybe I will be able to easily resolve once I have a solution for the first 2. Some deadlines are based on years. So "2 years" from X date or "6 years" from X date (inclusive, so the first day count). Other deadlines are based on months, so "6 months" from X date or "3 months" from X date (inclusive, so first day counts). I have leap y…

Hi guys - Miss A! here, need help..... How do i define a calculation for a date field to always display the date of the friday for the current or the previous week. The calculation i have, actually from previous week is working only for that day its created, for example today i had to change it again to: Let(fri=Get(CurrentDate) - Mod(Get(CurrentDate) +5 ; 4); fri - 4) for the friday from last week 17.10.2014 Let(fri=Get(CurrentDate) - Mod(Get(CurrentDate) +5 ; 4); fri + 3) for the friday ending 24.10.2014 I want this value to change automatically as soon as new week begins, also at the moment, the calculation will display wrong date if i try to a…

I have a large solution that was recently converted from FM Server 11 to FM Server 13. In general, it's working great. However, I just found one layout which locks up & beach balls whenever I switch to a certain Tab on the layout. When this happens, the cursor turns to a beach ball for 5-10 seconds at a time. The Tab that causes problems has several Text blocks that include a merge field: <<cFoundCount>> Which refers to a Calculated field for the table which is defined as cFoundCount = Unstored, from Data = Get(FoundCount) Storage options : Do not store calculation results. As a test, I tried changing the text blocks to instead refer to …

I have portal with related records.In portal row record I need find specific text (patterncount) from a field and sum up that records cost field. How do i do that? Do I need to do a loop every portal row and use patterncount to check correct record and sum that record cost field($costs = $costs + table::cost)? Thank you for your help.

Hi, I tried to make a simple calculation to total up outstanding bills or return some text if all paid. Invoice dates are only created on receiving payment. It comes back with all bills paid even though there are a number of records with empty Date Invoice fields. Does this look at all related records and if it finds any Date Invoice field with data it returns the text? Any help appreciated. Thanks

I have a situation where I have an entire line of text in a list but there are different numbers in front of the line of text ex. ( I have 4 lines of text below representing a line of text in a field called (dialog) 443. this is the first time 06. when did you call? 11. Is that still possible 1098. Do you still think this is true? Need to change to into the same field his is the first time when did you call? Is that still possible Do you still think this is true? How can I do this? It is always a varying number followed by a period in the beginning of the line of text.

What is the best way to navigate to a corresponding record within a table by using the OnObjectModify - Perform script on a container. Currently the Go to Layout takes me to the first record on my list instead of the record the container pertains to. Thoughts Thanks Matt

getsummary() not working Ok, this should be simple.. I have a report with several sub-summary parts. one part is sub-summay by _AccountID. on this part is 3 summary fields. sumisinrange14 sumisinrnage13 sumisinrange12 I need to add these together. so I added a calculation field GetSummary (sumisinrange14 ;_AccountID) this should show the sumisinrange14 field. but it is blank. the sort order is Accountnumber _accountID productgroupPeraccount2 so the sort is there but the getsummary feld is blank

I have a list of people & addresses with a Toggle Checkbox next to each name. Ticking the Checkbox marks them for Label Printing, this all works fine. What I'm after is a way to count the number of Checkboxes as they are checked, a running total. Such as tick one item, a separate field shows the number 1, tick two, it shows 2 and so on. I've tried the Count Function but cannot get it to work. With Thanks

I have several products made up of several chemicals which are made up of several elements combined together. I need to made a grid (please see at the end) Main Chemical 1 consist of Element 1, Element 2, Element 3 Main Chemical 2 consist of Element 2, Element 4 Main Chemical 3 consist of Element 3, Element 5, Element 6 Â Product 1 has Chemical 1, Chemical 2 Product 2 has Chemical 1, Chemical 3 Product 3 has Chemical 2 Â The layout for Chemical Table contains a portal for the related table Elements to input all the elements that made up the Chemical. The layout Command has the Products Table The layout Products has a portal for the related table Chemicals to …

I want to place this in a field (product description): If ( IsEmpty ( Varer::LABEL ) ; "Best. nr.: " & "<a href="mailto:[email protected]?subject=CD-info&amp;body=Please%20send%20me%20more%20information%20about%20TROY%201298."><span style="color: rgb(0, 0, 255);"><u>" & Varer::VARENUMMER % "</u></span></a>"; I'm told that "The specified field doesn't exist" (mailto:[email protected]). I guess I need to add a field for that, but where and how? The output from this field are (together with other fields) exported to a tab delimmited file and imported in OsCommerce. P.S. Using "mailto" in a a text isn't a good idea, …

I have 2 machines, the one remotely using my FM13 Advanced copy never had any issues. The computer died, and I moved FM13 to a new machine with Windows 8 and Outlook 2013. Every time the SendMail script runs it crashes FM on that machine. It opens Outlook, and as soon as you hit Send on the email, FM sticks constantly with the "thinking" spinning circle until it crashes. I setup a test file which simple had a SendMail blank script, same problem. Any idea on what the problem could be? Any help would be greatly appreciated! ***Update, Outlook 2013 is 32bit.

I need to display the number of items in a found set (I have disabled the standard toolbar for users) and display the current record number that is being viewed. Basically, recreating what the tool bar does. I tried setting the auto-enter of a filed to be "Get ( RecordNumber )" but this is not working. How do I do this? Thanks! Jason

Hello I'm still fairly new to FM I'm building a database for my business. In the Clients table, I have a details view that shows me all of the information about them, including a portal that shows all of the policies that the client has in the Policies table. What I would like to do is have the Policies Portal hidden if the Client does not have a policy record in the Policies table. I'm not quite sure how to go about doing this. Any help would be appreciated

Hi, I am trying to automate entry into the field "Already billed" to be used as part of another calculation. I need to either sum a field of all related records or lookup a field of the last related record. Im sure this must be quite easy, I just don't know how to do it. Any help would be appreciated.

hello I have a field in which i paste 5 paragraph returns from another source.... I would like to know how to extract the whole line at once and deliver that line to a field Example Bananas Isle 4 produce, Second Store section 1 mr. Smith My paste is always 5 paragraphs I would like to get just paragraph return 4 and 5 section 1 and paste it into field "section" Mr. Smith and paste it into field "personel" How to I parse an entire line or paragraph return?

Can you please help me make this syntax work. There probably is more than one thing broken with it... Do any of the characters in the allowedSubset need to be escaped or anything like that? Let ( allowedSubset = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890`~!@#$%^&*()_+=-[];',{}|"<>?" ; fieldArt = (Filter ( myFirstField ; allowedSubset )) ; fieldTtl = (Filter ( mySecondField ; allowedSubset )) ; mergedString = fieldArt & "_" & fieldTtl; Substitute ( mergedString ; ["å";"a"] ; ["é";"e"] ; ["ü";"u"] ) )

I'm trying to have a field format its content as all caps and assign the font / size as I indicate in the Inspector panel. I also need to remove all non-alphanumeric characters from the input. Generally speaking it works ok other than despite "Synchronized with field's font" being selected for the field, input pasted from a pdf string for instance retains its source formatting (or at least does not apply field's formatting). I though of maybe stripping the formatting through calculation rather than to rely on Inspector settings. ...I get weird results though, input appears twice in the field. Clearly I messed something up in this function.... any help? TextFormatRem…

Howdy, all: I'm trying to aggregate totals--which would normally be a simple affair using a scripted sub-summary report--but of course nothing is ever simple around here. Let's say I have field named Color and the found record values are: Record 1: Blue Record 2: Blue Record 3: Red Record 4: Green Record 5: Green With the necessary count/summary calcs and running a scripted sub-summary report, the desired result in Preview mode would end up being: Blue: 2 Red : 1 Green: 2 Fine, except that for their reasons, users want to stay in Browse mode so there goes the scripted sub-summary report. Isn't there a way using SQL that will perform the cal…

I have a report. on this report is a sub summary part,sorted by product group. On this PART is a summary field called totalofmonths it is defined as summary[12] = total of months. It is a repeating field with 12 repeats to show amounts for each month. The months field is a calculation defined as Case( Get( CalculationRepetitionNumber ) = Month(Extend( ShipDate )) ; Extend( ItemPrice by ProductGroup2 ) ) What I need now is to add to that part , the quarterly numbers. so I need to add the repititions from totalofmonths.. I need repitition 1, 2 and 3 only, then 4,5,6 only etc... I can not figure out how to do this. it seems it should be so simple but beyond me at …

Hi All, This is my first post on the forum, but I've been hammering my head off the table the last few days trying to get this working. System: Patient database Rough Concept: The data on each patient can change with each presentation (i.e. they may have stopped a particular medication etc.) I have created a table (admission DB) that is unique for each admission and auto populates with certain data from another table (Patient DB) about the patient once their PatientID is entered (Name, Sex, martial status, DOB etc.). I created an investigation ordering system (Another table called Investigations DB) which is related to the admission table via PatientID. …

Hi How can i filter a txt from this "2100-619903.psd" to this "619903". It should always remove the .psd and all the characters until "-" if there are a "2100-" there could also be txt without "2100-". The 4 numbers before the "-" could be all kind of characters, and there could be "-" in other places 2100-619903-8.psd Regards Frank

hi, We have a drop down value list text field that sales reps can select the month / year they believe a job will be completed on and can be billed in. It looks like this: ...10/2014, 11/2014, 12/2014, 01/2015, 02/2015...etc. We call it the "Estimate Billing Date". I have made a cross tab report with the sales rep's names in the rows. The columns are like this: Previous Months (to catch anything possible behind on being billed), Current Month (label to be dynamic and actually state the current MM/YYYY , Next Month...etc for up to 4 months out. I need to be able to create a calculation that can take the value in the "Estimate Billing Date" drop down list and e…

Hello All, I'm very new to the forum and this is my first post, I'm a Lab Pharmaceutical technician and we purchased Lab reagent for our Projects. we purchased a filemaker to monitor the expiration date of our reagent. Almost everyday I'm reading the forum and browsing the net, I found some that is suited to our needs, but there is one thing that I cant make it work, to display all the total numbers of our reagent that is expiring for the present month and also to display their names using portal via relationship. (see tab for expiring next month on the Dashboard) please help me to accomplish this. I tried playing of the relationship but cant get to work. Please fin…

Hi community, this is my very 1st post as I just registered and I am a newbie to FM, so please bear with me. I am creating an inventory database for a small bar. When for instance products like (1 litre wine bottles) comes into stock I enter the items into stock and it added the amount of bottles that was purchased fine. Now when it is time to sell wine to a customer for instance, it is sold as glasses of let's say (X) amount from a bottle, but not the whole bottle is used since it is divided into several glasses of wine that in total are equal to the 1 litre. How do I calculate the (X) amount that was sold from a bottle until it's equal to 1 litre,therefore the stock is …

Hi Gurus, I want to ask your help, I have a Sort Selection key with a drop down values (Category-Material Name,Supplier,Manufacturer,Code) What I want is, in my List View when I click my Sort Selection Key and I choose Code, I want to get the number of count of all my Materials having the same Code. I have made a calculation (Countby_Code ; Summary ; Total of ID_Constant (running with restart),when sorted by Material::Code) but it showing all the total of records that I have. Any help is highly appreciated. Thank you so much.

I need to get the total amount of the invoice but when i add summary field it slows down the database. i have alternative but it sometimes slow here is the script. Performfind SetVariable $total::Value:0 Loop Go to first record SetVariable $total::value::$total+amount Go to next record End Loop

I record the creation account name, creation date, modification account name, and modification date in all my data tables. I've noticed that in one table that has only one record and has all global fields, the modification info never changes. (I have not set up the creation and modification fields as global fields, but all other fields in the table are global, and the table holds only one record.) Any ideas why this would be? It works perfectly in other normal data tables. ~Courtney

Hey guys, Miss A! here again...... I need help trying to find way about the # wildcard, i would like to find records that begin with 3 digits and any more characters, example for the following fields, Transfer Cape Town für MB 800 030 service 140 Service Transfer MB 700 Only return 030 Service and 140 Service Thanx alot.

Have a Portal containing Players names (Maximum of 6). The Status field shows whether the Player is "Active or In-Active". I'm trying to get the Family Status field to show "In-Active" if all the Players are "In-Active". Currently I can only get it working if the 1st Player in the Portal is "In-Active". Â Using this calculation "Case (Players::Player Status = "In-Active" ; "In-Active" ). Â Many Thanks!

All this field's negativity...maybe that's why I'm having a problem coding a calc for it. I have a text box warning field I want to use to alert a user that if the PrintChoice field contains either the phrases "Last Visited" or "Status" and the radio-button'd SortChoice field doesn't have either the Ascending or Descending button chosen, it'll "light up". Where I'm having a problem is parsing the PrintChoice field. I tried using: PatternCount ( PrintChoice ; "LastVisited" ) = 0 or PatternCount ( PrintChoice ; "Status" ) = 0 ...with... IsEmpty ( SortChoice ) ...but the calc started getting clunky fast from there, logic-wise. Surely (don't say it!),…

Hi, I am trying to have a calculation show the name of a contact if they are a client on a certain project. The database is has 4 tables. Contacts>--------ProjectContacts---------<Projects ⌄ ⌄ | | Types---------------------- ProjectContacts holds records for which contacts worked on each project. Types stores the different types of contacts. I am very new to filemaker and tried to butcher someones script into what I wanted but have failed. In fact my efforts were so bad filemaker wouldn't even let me click ok. Serves me right! If ( // Test ProjectContacts::Id_type=1 ; //Typ…