Calculation Engine (Define Fields)

I need help creating a validation for a text field to ensure user enters data that matches an existing record's field in another file. Am failing to create a validation calculation or do i need a script? Many thanx.

I have really tried to figure out this calculation by subtracting the birthday from the current date, I guess that didn't work. I just need to have a countdown to a contacts birthday. Please how do I go about this?

This works on local fields but returns "?" on external fields: GetFieldName(Evaluate(Get(ActiveFieldName)))

Hi I have been struggling with this for a while. I have a start time and a duration and would like a field which lists the hours within those parameters. For instance; start 9am duration 3hr Result field 9am 10am 11am 12pm My recursive custom function skills are rather poor. cheers John

So I'm pretty new to writing recursive functions and I want to create a function that will contain a list of tables in my solution that contain a specific text phrase. I can use PatternCount and the Get ( TableNames ) function to see that there are 6 tables in my solution that match my search criteria but from there I get stuck. The calculation PatternCount ( Get ( TableNames ) ; "myFile" ) ; "myTextPhrase" ) Returns a value of '6'. How would I write a calculation to return the values of the tables that match the text phrase?

I have a table with multiple records lets say 3 for this example. I am trying to display this information within another table using the <<tableX::Data>> display method and this works great for displaying the first record in the table. Is there a way to display the second or third record using the <<xxx>> method?

We have a numeric field which has an auto-enter calculation defined that replaces existing value to replace a point by a comma. Settings in inspector of portal field: go to next object using tab, return and enter, input method 'automatic', touch keyboard type 'system default'. We use filemaker 13 hosted by filemaker server. On all computers the auto-enter works like a charm except for one computer I've been searching and searching and searching and noticed the following: When I remove the auto-enter and use a script to substitute the point, something very strange happens on this specific computer: The script doesn't modify the point in the field when I…

Say I have a database with 3 tables; A, B, and C. Table A is the parent table to table B, and table B is the parent child to table C. So, A -> B -> C If I wanted to evaluate from Table A whether a child record from Table B has a sub-child record in Table C and then capture the primary key value from Table B via a portal, how would I do this? On the surface it seems fairly trivial to write a calculation that that says ‘Case ( not isValid ( a>b>c::id ) ; a>b::id )’. The obvious problem is that calculation will only evaluate the first related record from Table B that is displayed in the portal. Encapsulating the entire Case statement inside of a Li…

L.S., I guess this should be simple but I seem only to be successful in part. From I website I get data which in stead of <tag> returns &lt;tag&gt; I wish to replace those &lt; en &gt; However a standard substitute does not work. Escaping the & with \& does not work either. Does someone have a solution ?

If i have a value list (approximately 2000 values) What is an efficient way to go record by record selecting a text field and see if i can make a match form the value list. Example.... Go to Record 1 select field contents (ex. DOG) go through my value list and see if I can find a value that has the word DOG in it. If i do find a match with the word DOG copy that value (even if there is more to the value than just DOG) and set another text field in record 1 with that value from the list. Example Go to Record 1.... Select field and set variable. Variable is equal to DOG Search through my value list and find a match All Dogs. Go back to Record one and set t…

Hi I created a field called "days of the week" where I can choose thru check boxes the days a course is taking place. These days belong to a valuelist with monday, tuesdays, wednesday,etc..  I'm trying to make a schedule based on such field but when I try to see if for example "Monday" is part of the text of "days of the week" it doesn't return anything  I've tried patterncount, Filtervalues, valuecount and nothing seems to work...  How can I evaluate if Monday is within such field, let's say the fields "days of the week" has monday, tuesday and saturday. How can I find Monday in such text field???  Thanks in advance

Hi I have a database that keeps track of the different characters we have in each day and is further broken down by units. The is a button called 'All Units', that allows you to select date ranges and also which units to include in the report. I have found a 'bug' so to speak that when you click on it and say select only the main unit in the report, it will only show up main unit, but the total count for the day will be higher cos it will still include other units if they are also using characters on that day. I am very basic on Filemaker, but I am guessing it is the calculation Xunique characters for date, as it is still adding everything up even when you only select o…

Hi there, also caught up in a close to similar problem: I would like to separate a contact number field into three separate fields, i have created the three separation fields as follows; Country_Code, Area_Code and Direct_Dial The contact number field has data in different formats for various records such as; +264812333249 +264 81 2444239 00264 81 2444239 etc,.... I tried to understand the solution on this topic for my situation but failed. is there even a possible solution for my situation? Any help please - many thanx, Miss A!

Does anyone know of a slick way to produce a list of either layout names or layout ids based on a selected table occurrence. In practice the user will select a table occurrence from a drop down. Then a script would be able to navigate to a layout that displays data from that selected occurrence. I know that I can manually create a join table that holds occurrences and their respective layout names (or ids), but I'm wondering if there is a way to do it on the fly. Perhaps there is something that the ExecuteSQL() function could do? Something along the lines of (and I know that this is not a valid statement): ExecuteSQL ( “SELECT * FROM LayoutID WHERE TableName=’" & [t…

Many years ago I wrote an article for Clarion Magazine on creating a simple registration code generator and validator using Clarion. As I'm getting closer to thinking about building a few smaller solutions and distributing them as runtimes, I wanted to translate the code to Filemaker. It is a very basic code scheme and can certainly use some enhancements. I plan one putting a lot more work into it be using it. I thought that it might be a good start for anyone else looking into it. I put the file in a public folder on my Google Drive the link is: https://drive.google.com/open?id=0ByyiDIpmElpyTWExdmlGdkRmb1k&authuser=0

Hi All, I have an old database for products that has the size measurement in a single field. I need to be able to parse out the numbers and units of measure and bring that data into a new database that has four separate fields, Height, Width, Hunit & Wunit (i.e. inches, mm, cm, foot). Of course the old size field has quite a variation of entry formats: i.e. 1-1/2" x 6", 1 1/2 x 6", 1.5" x 6", 38.1 x 157 mm, 25 Module (797 mm height), 4 x 8 ft... As you can see I'm also dealing with decimals and fractions as well just to make things interesting. Anybody out there have a script or custom function that could do this? Thanks for all the help again!

Hi, I am having a problem with my calculation of expiry date. I wanted to do base on date of expired date for 5 years and then calculate the expiry date for 5 years and 2 years. How do I do it ? Thanks Dedy I have database in filemaker link the file https://www.dropbox.com/s/ont1i7z02z2qw5a/Untitled.fmp12

I have a table that has a container on each line. I'd like to drag a .pdf to the container and have filemaker GO TO that specific line that the container is on within the table. Currently the (blue) selector is randomly on one line or another and whenever I initiate a script with a container filemaker seems to grab the information from whatever line is selected instead of the line the container is on. Thoughts?

This download link is broken. Can someone please repost it? How do I use the auto-enter calculation "TextFormatRemove ( Self )" if I am already doing a lookup on the same field?

So, I have this layout where I can edit a Service Order, that won't save automatic, I want to have a green text if the record is commited and a red text if the record is modified and not commited. Tried having two text overlaping, one set to hide if Get ( RecordOpenState ) = 0 and the other to hide if that = 2... It only shows as if the record was always saved even when I modify it. Am I doing something wrong? Thanks! And that is weird, if I set a conditional formating to turn the text red when Get ( RecordOpenState ) = 2 it works fine and goes back white when saved (Get ( RecordOpenState ) = 0)...

Hello everyone, I have been working on this for many weeks and unfortunately the newest road block has me stumped. Thank you in advance for any help you can give me. I have 4 text fields in my Event/Guest table: GuestNames, FoodRequest, SitWith and SpecialAccess. For the first time, I am importing this information from an excel spreadsheet downloaded from a vendor. Unfortunately, all 4 questions and their answers are combined into one field, so the imported cell looks like this: If you have purchased tickets for others, please list their names for check in and name tag purposes.: John Doe, Mary Smith | Are there any food allergies?: No | Who would you l…

Hello all, Â I am having two tables, Customers and Orders. Â My layout has :Â -List view of Customers -Leading grand summary part in which I want to show summary of total orders. Â When I show all records, I get correct figure of total orders of all customers. But when I filter records, the summary field present in leading grant summary of total orders does not change. Â Is there any way so that I can view filtered data in summary filed that is located in other table? for more info, attached screenshot. Â Any help will be appreciated. Thanks. Â

I've got a bunch of student records, which include nationality and a progression decision. I would like to see the progression rate by nationality.  I've attached a screenshot of what I'm currently getting on a layout with sub-summaries sorted by nationality.   The # of students and the # of students progressed seem to be working. These are both summary fields, with the progressed number being the total of a calculation that is 1 if they progress and 0 if they don't. For the %, I have a numeric calculation field where I divide the two summary fields: (s_countStudentsProgressed / s_countStudents)  As you can see, the percentage I'm getting is that to…

How to I calculate to REMOVE either the Left number of characters or the Left number of words from a field Thank you

How would I extract text from this text block in a FileMaker field: Billing Information ---------------------- First Name : Jacky Last Name : Taylor Address : 178 Gazellenkamp City : Stellmar State/Province : MO1 Zip/Postal Code : 7783 Country : US Email :[email protected] As a result I would like to have the: First name in a separate field, Last name in a separate field, email in a separate field, etc I have trie with 'Position', 'MiddleWords' etc, but cannot get it working. Thanks for any help.

I am using the MailIt plugin to pull in emails from an IMAP folder. I have the email in FM13, but I want to remove the thread (keeping only the actual response). Example: --------- Sounds great! See you then! On Apr 1, 2015, at 12:33 PM, User <[email protected]> wrote: ___ ITS Service Ticket 0316 ___ Can we meet at 10AM today? --------- I would like everything before the "On Apr 1".... Here is what I have now (which removes the thread but not the "On Apr 1...": Left ( MailIt_Messages::Body; Position( MailIt_Messages::Body; "___"; 1; 1 ) -3 ) Any ideas? Thank you, Brian

Hello all. I want a script or custom function to calculate: whether a specific day is xth day of month? For e.g. If date field is 2nd (xth) Monday (any day) of month - Result should be true otherwise it should result into false. If there would be any custom function, it will be great!! Thanks.

Hi, i'm not sure if this is the normal practice for Custom Functions. I have made a change to my CF and expected the change to be reflected in the calculation fields that use the CF. However, all previously defined calculation fields remain stuck to the "older" version of the CF, although the CF may have been radically changed. To force the newly updated CF to be used, I have to edit the calculation field (in define fields) and make a change to force a recalculation. This seems to be wrong behaviour, it is as if the calculation field copies the CF available at time of field definition/creation (or changes to it). Is there some way to force a recalculation to…

Is there a fast way to calculate all the invoicebalance listed base on the found records? here is my current process Enter FindMode SetField ( invoice layout::Invoice Balance > 0 ) Perform Find Set Variable: Name: $currentBalance Value: Let ( [ ~list = ListValues ( invoice layout::Invoice Balance ) ; ~total = TotalList ( ~list ) ]; #AssignGlobal ( # ( "currentBalance" ; If ( IsEmpty ( ~total ) or ~total ≤ 0 ; 0 ; ~total ) ) ) ) But this process takes a while to finish if i have 10 record in the found records

I need a little help... a cancer research colleague who is new to FileMaker needs some advice. Could recommend the best approach to tackle this? I'm a bit stumped/stuck. -- We need to calculate time between hospitalizations, let's call that H. For instance: Patient “1” was first hospitalized from 1/10/13 till 3/1/13 then hospitalized from 2/5/14 till 3/1/14 Patient “2” - from 3/2/13 to 3/8/13; 6/3/13 to 7/1/13; 9/1/14 to 9/15/14 Patient “3” - only 4/2/14 to 4/10/14 each patient will have different number hospitalization occurrences but in the range 1-10 We need to calculate mean time between hospitalizations and the standard deviation for patients w…

I have 4 fields called sibling 1, 2, 3, 4 that contains different languages. I want to create a 5th field to evaluate the 4 language fields and to say "Yes" or "True" they are all the same language, or to say "No" "False" they are not all the same language. An "IF" calculation works for comparing 2 fields but I can't get it to work for 4 fields. Any ideas?

How do I pull certain specified data using filemaker calculation engine For example see below, I'm trying to pull the word Cessna, from line 4 and Model 150L from line 5? Thanks! Start of Aircraft Description Aircraft Description Serial Number 15072812 Status Valid Manufacturer Name CESSNA Certificate Issue Date 10/26/2010 Model 150L Expiration Date 10/31/2016 Type Aircraft Fixed Wing Single-Engine Type Engine Reciprocating Pending Number Change None Dealer No Date Change Authorized None Mode S Code (base 8 / oct) 50150223 MFR Year 1971 Mode S Code (base 16 / hex) A0D093 Type Registration Individual Fractional Owner NO Start of Registered Owner

I'm looking for a way that when a user enters find mode, a script is performed and also after entering text in field and hitting enter another script is performed. Whats the best way to implement this, or is there an Always On ability for scripts? Thanks Matt

I'm trying to build an FAA N number integration for my filemaker database. Basically after entering an N number in the box and hitting a button filemaker goes to the fas website, and pulls all the relevant data to my database. This all works fine but with the code I've written there are white spaces before and after the text. Please see sample file attached. Second, I will hide the web view and text box from the interface, not sure if we need to keep that Third, if an employee enters an n number then hits Faa data bull button before the site has finished working the data won't pull correctly. So I was looking for a way to inactivate the gas pull button until the si…

I have two text fields, text1 and text2. Text2 is a product summary, consisting of the first paragraph from text1; text1 is a product description and varies from record to record. I set up the following to automatically populate text2: text2=LeftWords ( text1 ; Position ( text1 ; "all words left of the first paragraph symbol" ; 1 ; 1 ) ) The problem I'm having is what goes where I typed above "all words left of the first paragraph symbol". How to tell FileMaker Pro to find and recognize text control values? For example, many word processors would recognize ^p^ as a paragraph marker. I tried various combinations with and without "" from this page: http:…

Hi there, I'm having a issue setting a global date field when performing script on server. Here's some background: I have a script that is passing a date field from a local instance of FMP to a script that is running on the server (need to run it on server for performance reasons) The issue seems to come from the fact that the local system is set to dd/mm/yyy while the shared server we use is set to mm/dd/yyy - unfortunately we don't have access to the server admin. To work around the issue I'm trying to convert the Local global date to a number (this is working fine) Then passing the number as a script parameter to the server (this is also confirmed …

I have several separate number fields that were entered with values like this: 4.23 13.7 .52 STEP 1: I need them to be padded with trailing zeros like this: 4.2300 13.7000 .5200 STEP 2: and then I need them to be joined as a text string like this: 4.2300|13.7000|.5200 I can handle writing the calculation for step 2, but I am trying to determine the simplest code to use for step 1. Any suggestions? Thanks.

Having a problem getting my head around this. I have 4 time slots that can be used to book interviews in.to our meeting room, which is used for other things by other people other than for interviewing. I am builing a system so that users can allocate a timeslot to a candidate for an interview, but first I would like to query the calendar to make sure that the slots arent effected by other events in the calendar OK. These are the timeslots. 9:00 - 1030 10:30-12:00 13:00-14:30 14:30-16:00 So im thinking i have the following fields Date Timeslotonestart Timeslotoneend Timeslottwostart timeslottwoend timeslotthreestart timeslotthreeend …

Hi there, I have a system which uses the data separation model. The interface is on the local computer, and the database hosted on filemaker server. Within a crew table, I have the fields FirstName and LastName. A FullName calculation then joins these together, which is unstored. Several crew can be on shift at anyone time, managed through an OnDuty join table. A summary field gets a list of all the On Duty crew for each shift, and then a separate calculation field removes the carriage returns and replaces them with commas, named OnDutyCrewCSV. When running on the local system, this works perfectly. However, Ive just uploaded the database file to the s…

Hi, Have a slight problem. I'm trying to get a total of a set of fields . I have in my fields; H3 Incoming' H3 Aqueous H3 Gaseous H3 VLLW H3 Stock All which have different figures in. I made a new field called H3 Total and selected Summary with 'Total of' but It will only let me select on of the above, rather the the group. Is there a way I can total all the above to a final H3 Total? Thank you very much for your help.

I know this is a rookie questions because I can't yet cogently explain the relative benefits of stored vs unstored calculations. I may be getting ahead of myself but I have a more basic question to resolve. A database I am producing for my cabinet shop has a gazillion calculations. For the most part this is simple arithmetic but there is a lot of it. We have over 100 possible cabinet combinations and each one of them is built with distinctly different math. Each cabinet is pulled from a library of possible cabinets. The final output shows up on iPads in the shop. What I am curious about is the relative efficacy of generating this math as a series of calculati…

Hi, I have a repeating text field listing services. I need to make a report with all the values set in the repeating field. So, the calculate field will be a text field with coma seperated values. I tried several way without result, any help will be very much appreciated

is there a calculation to set a fields font color?

Using Web Viewer, Is there a way to show or calculate when a website has finished loading in a web viewer? I have a script that pulls the text from the web viewer and inserts it into a text field within filemaker but would like a visual clue or Green Light for my employees once the website has finished loading. Thoughts?

I have a database with the folowing fields Start time, End time, Date, and Room. I am triying to creat a field with a validation that will let me know if another record has a room with the same date same strat otime and end time. How do a I do this with validation and choose the calculation option. Thanks

Is there a way to upon dropping a .pdf document into a container within my filemaker file, select all or pull the text from that .pdf and send it to a text box?

I'd like to run a script that when opening filemaker determines whether the machine is a mac or iPhone and Goes To either the iPhone main layout or the regular mac main layout How would I do this?

This is an FYI / answered-question posted as a service to future searchers who may encounter this issue: I had a problem where FM Pro 13 ( though this would apply to any version of Filemaker on the Mac) would not recognize keyboard modifier keys (option, Shift, command, control) when a Get (ActiveKeyboardModifierKeys) script step was called. I was quite puzzled until I happened across this StackExchange thread on Mac OS X Secure Keyboard Entry. This is an OS-level feature that can prevent any app from receiving the full range of keyboard input from the OS. In my case, 1Password's Safari extension had enabled OS X Secure Keyboard Entry, thus preventing Filemake…

Hi there, I am trying to extract a date from an imported timestamp but not having any success. Here's what I have : A timestamp field where I import a timestamp from an external source. The timestamp is formatted as follows: SaleDate = 2015-03-13 07:50:16 I am trying to set a variable using SetVariable [$Date; Value:GetAsDate ( Sales::SaleDate )] But all I get is ? How exactly do I convert that to a date only field? I tried LeftWords to extract the date part only, but filemaker still doesn't recognise that as a valid date. my system is set to dd/mm/yyyy So I guess I will have to use a number of text functions to rebuild the date in the corr…

I wonder how to determine if a field is containing some text, any text. I can see the function IsEmpty determines if the field is without text but I want to perform a script that moves to a only records that are empty so I need to bypass records that already have entries. Thanks

I would like to apply a script only if one of my fields called "BeforeP" contains the words "your search" They are always the first two words in the field if they are present but I would like to find a way to execute another script if those two words are present. Thank for any help.

Hi, I am doing some analysis of posts in a danish forum, and have arranged my data in a filemaker database. Filemaker is way easier for creating dummy-variables and manipulating data, than SPSS(in which i am doing the statistical analysis). My question is, whether it is possible to make an if-statement that returns 1 or 0, if a field contain a keyword in a text message. Keyword: Apple Fx: "I have long been looking at this apple product. But it seems to expensive to me" and the if-statement should then return "1" I have been trying this: if(post = "apple" ; 1 ; 0) But i consequently get 0, even if the post has an instance of "apple".. Is there a wa…

I have 3 fields: START DATE — user selects date from calendar RUN — user enters number of days over which activity will occur END DATE — Calculation based on first two fields. This works fine BUT we want the user to have the option of entering the END DATE directly and have the RUN calculated. I changed both RUN and END DATE to Calculated Values but with no luck: changing RUN still changes END DATE (which is good) but when END DATE is changed, RUN doesn't change and END DATE consequently reverts to original value.

Hi all, I have a manual input field, and I plan to replace it to calculation field instead. The field is show as below: Invoice-136-036-15 136 being the category 036 being the last record 15 being the year invoice was created. Is there a way to obtain the "036" and replace the XXX to display as Invoice-136-037-15 once a new record is created? "Invoice-136-" & "XXX" & "-" & Right(Year ( Get ( CurrentDate ) );2) Appreciate your advices.

A date field in my database is formatted as 2015-03-01 in the layout mode. When exporting to a csv the date is written 01/03/2015. Why and how do I change it?

Hi, I wonder if there is an easy way to repeat the calculated totals of sub-summary lines, say, at the end of the report. I have an invoice like this: A-Items Item A.123 $1 item A.124 $2 Item A.125 $1 Total Items A $4 B-Items Item B.012 $2 Total Items B $2 C-Items Item C.111 $5 Item C.112 $5 Total Items C $10 Grand Total $16 But, before the grand total, I would like to include a couple of extra lines. I would like to repeat all the sub-summary lines of the report once more, like this Total Items A $4 Total Items B $2 Total Items C $10 Grand Total $16 Is this possible without reinventing the wheel? Thanks a…

I'd like to calculate the future value of an amount by an interest rate. Wikipedia defines this function (as future value of a present sum) here: https://en.wikipedia.org/wiki/Time_value_of_money When I do this in FM, I get a crazy result, so I obviously didn't properly transcribe it. I have this: present_value * (1 + interest_rate) ^ periods Thanks, Stefan

Hi, Just a newbie starter here and was hoping for some help setting up a particular field to change its value by sets of date ranges. For example: I have a table with 3 fields, Serial_ID, Season (S15 thru S18), and a long name field... for example S16 would be 2016. Based on the current date, I would like to change the Season field. Any help would be appreciated, thanks. 8/2/2014 to 8/1/2015 = S15 8/2/2015 to 8/1/2016 = S16 8/2/2016 to 8/1/2017 = S17 8/2/2017 to 8/1/2018 = S18

I have to recreate the 'repeat event' functionality you would find in a calendar app such as Apple's 'calendar' etc. I have been looking around from a modular solution but haven't found anything. Before I begin does anyone have a suggestion,

Given this example... Table1 NumberFieldA NumberFieldB Table2 cFieldC [ sum ( Table1::NumberFieldA ) ] cFieldD [ sum ( Table1::NumberFieldB )] What is faster, better or more correct? cTotal [ cFieldD - cFieldC ] or cTotal [ sum ( Table1::NumberFieldB ) - sum ( Table1::NumberFieldA ) ] Thank you.

I'm having trouble getting the below text in a send mail script being shown as bold and italic. The relevant part of the calculation is below: TextStyleAdd ("Please would you send me an updated ETDL Complementary Certificate (hard and electronic copy) for the candidate as follows:" & "¶¶" & "Issue Date:" & " " & Course::Course Start Date & "¶¶" & "Expiry Date:" & " " & Date ( Month (Course::Course Start Date) ; Day (Course::Course Start Date) ; Year (Course::Course Start Date) + 2); Bold + Italic) I'm sure it's down to one of two things. Either the above calculation is incorrect or it could be that the email generated is in plain te…

I have copied the following formula from excel into my calculation field: DEGREES(ATAN2(COS(RADIANS(S2)),COS(RADIANS(U2))*SIN(RADIANS(S2))) Since FM doesn't recognize the function "ATAN2", I added a custom function as follows: Let ( [ a = If ( x > 0 ; Atan( y / x ) ; If ( x < 0 ; Pi - Atan( -y / x ) ; Pi / 2 * Sign ( y ) ) ); result = Mod( a; 2 * Pi) ]; result ) based on the following source: http://www.briandunning.com/cf/502 but unfortunately the function doesn't seem to be working properly, or at least the same way as it does in excel. Can anyone provide the custom function script equivalent to the "ATAN2" function …

I have a rather old inspection db that was built with repeating fields for result entry. This db has been in use since the very early 90s, perhaps a bit before, so I don't hold much against the db admin that set this up. The did the best with what they had! I've known for some time that I need to convert these repeating fields into related records, and now is the time. But - I've hit a snag. I have found plenty of guidance on how to do the basic export/import as related records. Pretty simple. My challenge is: for each of the 27 repeating fields, there has been an inspection type assigned to that partiular field. For example, if we enter a comment about …

Is there a way to have a calculation field set the result of the calculation in the specific repetition field number? I have not been able to determine if there is a function to do what I want done.

Hello everyone, when i use Get(Current Date) function its show right date format as based on system format. But set field to the "text" field its show the wrong format.. I want to set the same date format to the text field: Example: My system format is "dd/mm/yyyy".. when i put this to date to text field, Its show as "mm/dd/yyyy" Is that make sense and How do i resolve this issue

Hello I am using firefox as my browser but I would like to invoke a facebook search for a Church. I have a lot of these to do. How do I use filemaker to make a facebook search happen I will be using a calc i n a file called comAddress which calcs the name, address and city and state. Thanks

I have a portal that shows the line items in an invoice. The field "unit price" which is a number field with a calculated value, is not consistently showing a value; sometimes it works, sometimes not. Once it works for a given portal line, the following lines for that portal, in that record, will work. The next new invoice record will often show the same erratic behavoir in the portal. The calculation involves a join to a related table that contains the prices for the items that are entered in the portal. The join is clearly ok, since values often show up, but not reliably. I'm totally perplexed as to what is causing the intermittent behavoir of the calculated value.

Hi, I am sure my if statement should execute as 'true', but it is not. I am on a table called SALESINVOICES. I want to check how many SALESINVOICELINES (ie, the products attached to the invoice) qualify for a special. My 'If' statement is: $CorS = "C" and ( SalesInvoices_SALESINVOICELINES|CompanyID|fqualifiestoptenspecial::s unique no products count customer ID = SalesInvoices_PRODUCTS|toptenproducts::z_FoundCount ) where SalesInvoices_SALESINVOICELINES|CompanyID|fqualifiestoptenspecial::s unique no products count customer ID is a summary field counting the total of lines that have flagged as qualifying for the special, and SalesInvoices_PRODUCTS|topt…

I import a text file that has a product name field with content like this: 16.9oz. Oil/Water Set/2 Blue-Widget/Red-Widget™ 7CY-QUARTER-A/F-Ajax© Michael@Home/Black Artwork **these are non-sensical on purpose I'm trying to make all of these lower case, stripping out all of the funky characters and replacing them with single hyphens 16-9oz-oil-water-set-2 blue-widget-red-widget 7cy-quarter-a-f-ajax michael-home-black-artwork I've studied this page all afternoon, and deep into the night. http://help.filemaker.com/app/answers/detail/a_id/4888/~/tip%3A-removing-non-numeric-characters My eyeballs are crossed. Does anybody know how to do this?

Hello everyone. How can I set the calculated value of field C to the number of records having field A equal to the value inserted to field A and field B equal to the value inserted to field B? For instance with the following situation record 1 => A = "house", B = "pay" record 2 => A = "house", B = "win" record 3 => A = "car", B = "pay" record 4 => A = "house", B = "pay" when I create a new record having A = "house", B = "pay" C should automatically be 2 (since record 1 and 4 have the same A, B. Thanks

Hi everyone. I have a field called documents rendered as checkboxes (values are taken from a value list). Now I need to perform some calculation (basically to set the value of an other field) according to the checked values (number and values themselves). Which function should I use to get the list of checked values? For instance in the situation below: [ ] Book [ ] Magazine [ X ] Journal [ X ] Diary [ ] Paper I wish to get 2 and to get Journal and Diary. Thanks.

Hi All, Having a problem and just can't get my head around it. I have a Field called 'Department' with a drop down box. In this field is different departments ie; Chemistry, Physics and so on.. What I'm trying to figure out is, I want something that when I select a department , lets say Chemistry for example. Is there a way for an abbreviation for 'Chemistry', ie; 'CHEM' to show in another field ? Thank you.

I have a table called log, in this table I have several fields that are imported from a different table. There are two fields (A and that are numerical. A will have three different values 1,2,3 and B 10 different values (1-10). I wanted to "recode" the numerical values into different fields in the same table (C and D). For example if A has the number 1, then auto-enter apples in C, if A =2 then auto enter oranges in C, if A is =3 then auto enter peaches in C. The same For B here there will be 10 values each represent a specific item (1=red, 2=blue,3=black, 4=orange.....). I have tried with the case function but I have not been succesful... any help?

I'm new to filemaker Pro 13, well to Filemaker full stop. I entered a calculation field that having put the date for the next MOT [Vehicle check]... it will compare that date to the current date and then tell me how many days I have left until the MOT needs to be renewed. I can then use this in a portal on my Dashboard layout that gives an overview of things in the business. The trouble is the calculation field doesn't automatically recalculate unless I enter and exit the "date field called "MOTDue", but obviously I want it to update as one day changes to the next or at least when I open the program.... but probably the former. Here's the calculation wrapped …

Hi, I have the following calculation in one of my database fields. The test will check if a specific field has a value of 3 or above, and if so marks Passed, otherwise marks failed. It works fine as long as the result of the field isnt a two digit number that starts with 1 or 2. (11, 21, 19, 25 etc) . The calculation below seems to only be evaluating the first number, not the total number. If ( ( Initial Test Buffer PSID ≥ "3"); "Passed" ; "Failed") another with the same issue, but 2 rather than 3: If ( ( Initial Test Relief Valve Opened at psid ≥ "2"); "Passed" ; "Failed") I would love to hear feedback to how I can fix that. Thanks so much!

Newbie Let function question..... I am using the Let function for the first time. My goal is to end up with a local variable "$DaysOld" (which equals the "current date" minus "date of birth"). My Let function below is returning the answer that I want but how do I assign that answer to a local variable? If ( METADATA::desc ≠ "birthday" ; "" ; Let ( [ ~dob = GetAsDate ( METADATA::value ) ; ~curdate = Get (CurrentDate) ] ; ~curdate - ~dob ) )

I have a field called list in which I have varying number of words. For finding specific websites I need to but a hypen between each of the words in the field with a calc. Ex. "Blue carpets are hard to clean" Calculation to make this like below "Blue-carpets-are-hard-to-clean" thanks to you

I am in need of a temporary bandaid during a migration while we consider changing a process. I want a calculation that takes a list of fields (which are containers holding icons) and puts them into a single global container calculation repetition so I can use rep1 for 'Add' and rep2 for 'Edit' etc. globally. I've used similar with regular Admin and Preferences data but not like this. Upon startup, icon_TRIGGER (a global number in the one-record Admin table) will be set with 1. This script step can also trigger this calculation to re-evaluate. The refresh will fill the global containers at startup for use during the User session. It is temporary ... I know it c…

Hi The final stage in a particular script is 'replace field contents' (it's 'replace with current contents' but I've also tried it with a calculated result just to see if the problem persists). At this stage I don't have 'perform without dialog' checked, so the 'replace field contents dialog' comes up as usual. So I click replace, the dialog box disappears, but nothing else happens (the fields all remain empty). But then, if I immediately select 'replace field contents' from the menu and click replace, everything happens as it should. Is this a bug or am I missing something? Many thanks for any help (first time post so apologies if it's in the wrong place) Darius

I'm not sure i'm posting this in the right forum, but hopefully someone can help me out. Short description of what I'm trying to achieve: I'm in a band, the paychecks come to me, and I divide up the pay and give to each band member. It's on me to keep a record of the amounts so that I can 1099 them at the end of the year, and not be stuck with all the taxes. I'm trying to create a bare bones simple DB that I can enter each gig with a gross pay field, and then the name of each band member, and what they were paid. The goal is for speed and efficiency, so my idea was to manually enter the date, venue name, and gross pay, then use a repeating field to enter the rest…

When I place a new field on a layout, the "specify field" list is in full alphabetical order. When I click on an existing field, to change to another field name, the list is only partially alphabetical. It appears that the bulk of my fields are sorted correctly, but then after some date (not sure when) the list began to populate chronoligically, starting after the last z named field, with all new fields at the bottom of the list. VERY frustrating when sorting through a long list of fields. Anyone else expereince this? Is there a way to change this?

I'm completely flummoxed. I have a customers table and an orders table. I would like a field in the customers table which will show only the most recent order quantity from the field orders::qty. Ideally, I would like to do this without a script. Thank you!

Hello, Â Puzzled on why the result is not what I was expecting on date/time calculation. 4 fields: start date, end date, start date, start time... the following calculation (per FM website) Â "((Rental_Date_End - Rental_Date_Start)*86400) + Rental_End_Time - Rental_Start_Time" Â should return 90000 (calc. result is configured as number presently) which equates to 86400 seconds in a day + 3600 secondsin an hour. Â My result is 86500. (see attached). Â Is there a gotcha that I'm overlooking? Also, if there is an issue, once corrected, how would one turn the seconds into a "x days, y hours" type of output. Â Thanks as always! Â Jack Â

I'm looking to create a calc that takes a number say age field = 36 and reverse calculates it to get a date of birth for that age, say 1/1/1971. All of the month and date fields will stay 1/1/ only the year field will end. Thoughts? Matt

Hi I have been working with my database and I have run into a problem. I can copy a whole web page which has been the way I collect the large text. I cannot scrap normally because there is a snare with some encryption scheme in the source code. I have a copied a screen copy of the web page as below: I place in a field called "webpageText" I want to capture ONLY the text in RED below and send it to a field called " portionText" It is basically the same on each web page The common denominator on all the pages is the start text is " Ads Company Info" the ending text is "Map" I found a script by Set Field [ YourTable::MyINFO ; Trim ( Sub…

Field populating determined by the current month FIELD value. How to? Hello all. I'm looking to populate a "Budget months" field automatically for all records and have it update automatically, month to month, relational to the current month. I have a "current month field" in place that always reflects the current month. The "current month field" is a text field". For all twelve months of the year, I would like to have the "Budget months" field auto populate similar to the following: Current Month / Budget months March / 8 April / 1 May / 4 As you see, each month would have it's own corresponding number permanently assigned, that would not change. I need to hav…

Could this be done using a Let $SelectedSearchColumn = first_name and $$first_name = "descending" For example, can I do something like this? ("$" & ~varName) = "descending" This is my attempt...which does not work. Let ( [ ~varName = "first_name" ] ; $SelectedSearchColumn = ~varName and ("$$" & ~varName) = "descending" )

I have a lot of records which end in either .org/ .com/ .us/ .net/ How can I write a statement that determines if any of those are present? The way I have been doing it is to write 4 separate statements. I am asking how to write it correctly if there are a lot of different texts strings that I am trying to detect. In my own words It would be something like this If my field ends with ".org/ " ".com/ "" .us/ " " .net/ " Perform script But they are not always 4 characters. Thats the problem. PLease help if this is not hard for you. Thanks

I'm am trying to set up a calculation field that will calculate the difference between the current and previous record of the same field, such as the difference between the number of people who attended this event vs the number who attended the previous event. How do I specify a field in this record vs the same field in the previous or for that matter, any two records? Any suggestions? Thanks in Advance. Brad

Hello, I am not sure if this is the correct section to post this but its as close as I think it is. I have a field that I would like to have the data validate. There are options to select the field to be strictly numeric and from a value list. I have a field that can be numeric or have data entered from the value list. We do not want any other data (text) unless its numeric to be entered into the dropdown field. Is there a way to validate the Data so it is numeric OR from a value list that is assigned to that field?? I am guessing I would have to validate by calculation but I do not know how the calculation would look exactly. Thanks

Hi Folks, I've almost got the List function working, but can't quite jump a few hurdles. I have a cInstrument field on the Pupils table. The calculation is List (InstrumentLesson::Instrument;InstrumentLesson::AB Grade ) This only works for the first related record. e.g. Viola 5 Whereas: List (InstrumentLesson::Instrument ) lists all the related records. e.g. Viola Kazzoo Trombone What I'd actually like is something like: Viola 5 Kazoo 2 Trombone 4 Any ideas where I'm bodging this time please? Many thanks, Mike

Hello everybody, I am trying to display some calculated fields on a dashboard that will get information from related tables.  I am having some difficulty in trying to get this to work properly.  Any help will be greatly appreciated.  I have attached an image of my table layout.  I have a field in one of my tables, project_db, that is called project_status. I have the following options for this field with the use of a value list:  Quote Awarded-Won Awarded-Lost Verbal Order Invoice  I would like to display, on my dashboard layout, the number of records that have "Quote", "Verbal", "Order", "Invoice, etc., selected.  Is there a way to do this?  …

So I want to make a value list which is the first four letters of a customer's last name. Why? because I want the guys to type those four letters to find a customer code. The customer table will have a customerID but this will be used to type first character and match then type second character and match for filtering the list of customers to choose from in portal. If I have a value list of the four characters for all the customers then I can put a portal filter on the Dashboard portal to customers which could be like this I think PatternCount( characters typed ; four character value list ) or similar, right? Thank you.

I have a list of 10,000 words in a single global text field. Each word is separated by a carriage return. How can I grab the first 100 words in the list so I can set them as a global variable?

Hello, I am a FM newbie and have been scouring the web for an answer to this problem: I want to eliminate the text within parentheses in a string, but I want to eliminate all instances: ORIGINAL STRING: This is a test of the (emergency) broadcast system. (It is only a test). Remain calm. DESIRED STRING: This is a test of the broadcast system. Remain calm. Can anyone help? Also, is it possible to tie the function or script to a field so that when the ORIGINAL string is put into the field, the field does the conversion automatically? thanks very much in advance, SB

Friends, I have had it for today... can't seem to get it right... I'm writing an Excel sheet using the MBS plugin. The plugin let's me set FORMATS so I can underline or center cells etc. For every FORMAT I declare a variabel i.e. that gets a value from the plugin $$Format1 = 89 $$Format2 = 90 I pull data from FMPro and 'filling' up an Excel Template with field values. Some of these field's need formatting. Since all data is dynamic, my script needs to handle this accordingly. I 'build' the excel sheet row-by-row, therefore pulling data from FMPro, row-by-row. A VARIABLE is handeling the data in the LOOP...The variable content looks like: "7" &am…

Hi all I'm modifying an existing db I created and replacing buttons with key commands. I know this isn't the most effective way, but the client has asked for the db to be mainly controlled via the keyboard because of situations where use of a mouse isn't applicable and where they haven't replaced their machines with touchscreens. As a result I've had to include a "key" at the bottom of each screen detailing what key commands do what. Of course, when an active field is in use the key commands won't be effective. As a result I've created a calculation ("command status") to show the key commands for that layout or to merely state that x field is being edited. Basically t…

Hi there, I am struggling to build a calculation for percentage increase on records of prices between two years, records are marked with a value list field by year and season, HS(High Season) and LS(Low Season) 2015 HS 2015 LS 2016 HS 2016 LS For each record there a re different price option fields, 1st Price, 2nd Price and 3rd Price. I want a calculation that checks if the 1st price field is not empty then calculates percentage increase for first price for the year 2015 HS record and 1st Price 2016 HS record, if it is empty then does calculation on the 2nd price for same season of different years(2015 & 2016) I understand there is fun in filemaker …

I have a script in which the clipboard is already loaded with information from a field. The problem I am having is that I need to copy and paste field into the "find" mode in the same field. I was wondering if there is anyway to do a set field within find mode to automate the find. I would like to set the find from the current field phone and in setting the field have the find field set with the contents of the field "phone". If I were only trying to find if there are a few copies of that field, i would automate the copy and pasting of the field into the same field during find mode. But since I have information already copied to the clipboard that would negate that .…

Hello I have been succeeding at scraping my web URL and getting what I need. I ran into difficulty that I have been working on but I can't figure out how to do something on this web URL I am looking to web scrape the Youtube URL and the vimeo http://www.usachurches.org/church/the-miracle-center-san-antonio.htm and twitter URL from another page http://www.usachurches.org/church/new-life-covenant-church-se.htm When I go to the twitter field where the url should go I get tml> <!-- HTML5 Boilerplate --> <!--[if lt IE 7]> <html class="no-js lt-ie9 lt-ie8 lt-ie7 I have tried everyway to get the areas enclosed I want to grab but…