Calculation Engine (Define Fields)

forgive me if this question has been answered elsewhere. I'll try to be brief: I have a table "events", and another table "line items". Users are complaining that event invoices are slow. There are many potential areas that I could look at for speeding it up, but right now I'm looking at the grand total. Historically I have used this calculation field in line items table: Total of Total Cost + If( Line Items to Events to Payment Types:: d.c. Tax Exempt ≠ "yes"; Total of Food Bev Tax Amount+ Total of Misc Tax Amount+ Total of Parking Tax Amount ) however, I recently learned about the sum () aggregate function, so I was wondering if creating this calculation fie…

MS Access has 'group by' and it easily shows the number of records after grouped by particular field. e.g. [DATA ( 5 records ) ] Type Quantity 001 1 001 1 002 3 003 1 003 2 MS Access shows 3 (The number of records after grouped by 'Type') and result below in a second. 001 2 002 3 003 3 As far as I know, I have to make another table to have unique record(by 'Type) , and to count quantity. This is too much work compared to MS Access. In another way, I have to Make List view and add'sub-summary when sorted by' to it, and sort every time In addition, it won't tell me 3 (The number of records a…

Hello, Can Someone help me i have a merge code lets say i have 3 fields firstname, middlename and last name and i have a substitute field to put this 3 field Substitute ( Self ; [ "<<firstname>>" ; table::firstname ] ; [ "<<middlename>>" ; table::middlename ] ; [ "<<lastname>>" ; table::lastname ] ; ) and i put this on this substitute field for example My name is <<firstname>> <<middlename>> <<lastname>> My problem is if the <<middlename>> is empty it leaves a space is there a way to remove the space without actually removing the <<middlename>> on the center?

So I'm not sure where exactly to put this as it deals with a Custom Function, but hopefully this is the right place. I've been using the excellent #AssignVariables Custom Function over from FilemakerStandards.org for about a year and half with no issues. Today I tried to implement a script that is taking an enumerated value from a parameter passed on an OnTabSwitch ScriptTrigger and for some reason it is failing, and I have no idea why. The parameter that is being passed is: "$target=" & Quote ( GetValue ( Get ( TriggerTargetPanel ) ; 2 ) ) The subsequent script then uses the #AssignVariables CF to assign $variable with the contents of the Script Parameter. …

Hi there, I need to separate an address field into two parts based on the occurrence of a "/" character. e.g. Flat 1/3 Happy St needs to be separated so that the Flat 1 is in its own field and 3 Happy St does too. There are ways of doing this with PHP but I can't find a way to do it with Filemaker calculations.

Hi there, I am trying to build a calculation that formats up to 6 course dates as a single block of text. For a one day course the format would be something like Mon, 23 June For a two day course the format would be something like Mon-Tues, 23-24 June and so on The dates are not always consecutive so for instance a group of four dates might look like this: Mon-Tues 23-24 June and Thur-Fri 26-27 June. So there is an "and" separating the 2 blocks of 2 days. A six day course could have 5 blocks e.g. 2 dates together followed by 4 separate dates. There are at least 50 possible formatting combinations for 6 or less dates. I don't want to have…

Hi all I think I have this custom function in every database I use. Case ( Start < Number ; GetNthRecord ( field ; Start ) & ¶ & AppendNth ( field; Start + 1 ; Number ); Start = Number ; GetNthRecord ( field ; Start ) ) It is just standard practice for me to use it. I mostly use it to concatenate data from Portals into a single field for display. As it only concatenates one field, I usually create a calc of all the fields I want to display and then use AppendNth ( relate::field ; 1 ; Count ( relate::recid ) ) Is there a better way? It just occurred to me t…

I'm new to filemaker pro advanced 13 and encountered some problems with the case function. I have to following function: Case ( Product ID = Order Book_Test::ProductID ; Case ( Order Book_Test::Eformat = "Date&Price" ; Case ( Order Book_Test::Date = ( Get (CurrentDate)); Count ( Order Book_Test::Eformat)))) my problem is that filemaker stops at the first true Case. However, I need filemaker to validate all Cases and based on this result to execute the Count function. How can i solve this issue? Thanks Sam

Hi I'm trying to get my head around the logic of a calculation, and have gone through several iterations of what was wanted. Initially, they just wanted a Y or N, Y if the latest date of a field from a table for the current record was within the last 12 months, otherwise a N. Then they said, actually, can we have a corresponding field for the latest date instead of Y, and N if the date is over 12 months ago or there is no date returned. So here is my logic: Let( [vDate1 = ExecuteSQL( "SELECT MAX( d_Date_Completed ) FROM ACTIVITY WHERE a_kf_Client = ?"; ""; ""; a__kp_CLIENT ); vDate2 = Date ( Month( Get ( CurrentDate ) ) ; Day(Get ( CurrentDate ) ) ; Ye…

Hi all I'm attempting to write a simple database to help me keep track of reports at work. I work in the film biz and I'm going to be implementing a way to track my camera logs on my ipad instead of filling out paper sheets. I first thought that using the serial number feature would suffice, but I believe this to be a little more advanced than what that feature is capable of. For my example I will call this report "Report A". I have a field named "media label" in a table called "report entries", and upon the creation of a 'report entry' I would like the following: Report A: The media label is created using the following info: An abbreviation of the sh…

I have a field called version. Some of the values contain multiple decimals for example 10.12.73.10 I want to trim them down to only contain 1 decimal so in other words 10.12.73.10 would become 10.12 another example would be 7456.342.4728.123 would become 7456.342 how do i do that? Thanks again! -Erik

Hello everyone. Whats the best way to completely "lock" a post from being modified? I have understood you could set a script trigger to every field that wont let you write in it, but is there any easier way do to it? Appreciate answers.

I'm trying to stamp every record update with the Date and User name when a Commit has been done. The idea is that every table would have a single Text field where only the latest User/Date stamp would go. When another user updates the record, it gets over written. This seemed straight forward at first but a few things have complicated it. I'm using a tabbed UI to display the contents of several related tables. Using a COMMIT button (with a script behind it) in every tab is silly as the user would be more inclined to click the tabs and forget to click the COMMIT button. So instead of buttons, I was hoping to find a way to detect a Commit (since there are so ma…

How can i make a field be required to be filled if another field in the same table has data in it. In other words. I have a date field that if i have a date listed in there i want to have a compliance field be required to be filled. If the date field is not filled it can be empty. Thanks again filemakers! -Erik

Hi all Is there a way to multiply a data record? For example, I need to make 6 copies of a certain data record. And I would like to do this as fast as possible as I have to multiply a lot of data records. Something like a button in the entry mask where I could chose how many times i would like to multiply the data record would be great. Your input is more than welcome;)

Hi everyone, I have database where my employees enter data, but i don't want them to change a field if it is not empty, as this field is key for searches and other stuff. Database name: historical field name : event type : text options : indexed Layout field name : event control style : pop-up menu When the record is created the user selects the event from a pop-up menu, but the record has other fields, and the users modify this information several times after the creation of the record, but if the modify this key field all searches go bad. What can i do so the users can't modify the field event if is…

Hello, I have a field defined as UTP. Here the data contained in the field can be either or number or a value defined by "<0.05". I have the field set as a number field. Only problem is I want to have this field default to 2 decimal places with a leading zero if the data is put in as for example ".01". When I define the field as a decimal it works but it will not display a record with "<0.05" inputted in the field. Is there a way to fix this? Maybe with a calculation that displays the value in the field. Thanks

Hi all I need to know if an algebraic equation is linear or fractional. ( fractional equation: an equation containing the unknown in the denominator of one or more terms ) 3 * ( x - 1 ) + 5 = 0 // linear 3 / ( x - 1 ) + 5 = 0 // fractional How do you think to solve this problem with Filemaker?

Going nuts trying to work this out, zip file is self explanatory. Any help please. Finances - 1.zip

Hi I have created a database that monitors extras needed for scenes. I have managed to generate a report that will work out the totals of adults and the totals of children, but I am struggling to get it to work so it adds up the numbers for just a certain unit. If I search for a unit say 2nd unit, it will still only show up the two units combined so adds main and 2nd unit. I have tried several things but cannot seem to get it to work. The report I am trying to get it to work on is the one liner copy. I would like to be able to add up say the totals for adults and then kids then both combined just for that unit and show that, so I can work out the costs for each unit s…

Hi, I am a beginner here and I really fill it.!! I have a table of events with a related table of requirements. I can have requirements from 0 up to any number as required In the table of events I have a calculated field (result is a container) which has there options "approved" "pending" "failed". The 3 icons comes from preference table and are global fields. The failed situation is based on the event creation date (entry field) + 30 days condition. the pending situation is based on the related requirement existing - if no related requirement exist the situation is Approved. The approved situation is based on the requirement completion date (entry field) from th…

On the main layout I have "Get ( AccountName )" filling a field of who is logged in. The calculation is simply Get ( AccountName ). How could I get that same field to say essentially "Welcome back, Admin". I have tried "Welcome (Get ( AccountName ))" but the calculation doesn't like that... Help...

Hi All, I have a backup script that I have set to run if the elapsed time since the last backup is equal to greater than 24 hours but it will not run: If[user_Data::Time_Elapsed_since_Backup ≥ "23:59:59"] Perform Script ["Backup"] End If Elapsed_Time_Since_Backup is a calculation field set to Time What can I not see?

Hello, Is there a way to use Get(LayoutName) in a Calculation Field? Cause when I tried it, I get a null value. Is there any other alternatives? Thank you!

Is there a function that select all pictures? lets say i select all picture and upload it to a record

Hi I've imported names and addresses from a spreadsheet in this format (all in a single FM field) John Smith [email protected] Can anyone suggest the calculations that will separate this into: John Smith [email protected] Thanks! Philip

Hello! I apologize if this post is not in accordance with forum custom; I'm brand new here! This calculation isn't working and I can't figure out why. Case ((IsEmpty(TimeReturned) and MusicDue="no" );"Current";(IsEmpty(TimeReturned) and IsEmpty(Library Inventory::Paid) and MusicDue="yes");"Due";TimeReturned > 0;"Returned";Library Inventory::Paid > 0;"Paid") I just added the final portion - LibraryInventory::Paid>0;"Paid" The first three results work everytime - Current, Due, and Returned. But when the qualifications are met for "Paid," it still reads "Due." Any thoughts? Thanks, Matt Swiss

Hi there, We recently moved our FM13 database from our LAN to a WAN - and have noticed some very significant slow downs. I think most of the issues are coming from non-stored calculation fields referencing related fields. I'm hoping that I can switch these to number fields and use auto-enter calculations, the thing is I need them to update if the value in any of the related fields is updated. I'm guessing I need to use some script triggers in the related records, but I'm not exactly sure HOW to to get the auto-enter to recalculate. I'll explain a bit more. I have 3 tables: Products Purchase Orders Purchase Order line items in Purchase orde…

Hello Is there a way to enter a password first before editing the field for example i entered $100 in the field and i want change $50 before we can change it we must enter a password of the manager

I have a simple accounting solution that I enter my check book transactions in. I am beside myself trying to figure out how to show my account balance at the end of each day. I acquire the current balance with this formula; Beginning Balance-Summary Expenses + Summary Deposits. My issue is capturing the balance daily. I have tried numerous calculations, all to no avail. I believe I need to write a script to accomplish this. Any suggestions would be greatly appreciated.

Hello Board, I'm stuck, again. And again i call for your help. I am building a dashboard, i have an 'Hours' table, a 'Day' table, a 'Weeks' table and now i'm trying to build a 'Months' table; all aggregating data so i can chart and summarise it by these divisions. I'm totally stuck on this one and I can't make it work. I'm sure i'm missing something but i just don't know... What I need is a MonthYear number to Relate records with that date. So, 5/2014 for this month. I can generate this from my 'Today' field which is auto inputted into the Dashboard table. But how do i get last month, 4/2014? And 3/2014, 2/2014, 1/2014, 12/2013? I have 12 records in the Mo…

is there a way to get the last text on the field and put another text it for example: the field contains the statement is true and correct. after i click a button it insert a text the statement is true and correct. (Mailed a copy of this)

I have this calculation that begins with Product Name then list attributes about the product under the name. I have indent set at -145 for first line and 145 Left. Works well except when product name is longer than one line. The attached file shows the calculation, my soft returns are not working after the first time. thanks

I have a date field when i type 6/8/40 it becomes 6/8/2040 is there a fix this instead of 2040 it should be 1940

I'm doing an XML import of data. In the date field of the XML it's formated as YYYY-MM-DD. we want the date to be formatted in Filemaker at System Short.. In doing this we get the ?. Why can't Filemaker understand the date and reformat? How can I go about changing the format on import or right after import? Thanks

I have a return separated list of values (from a drivers license) where each line has a 3-character designator such as: DACFrank DCSSample DAGSample Street DAK90210 What I need to be able to do is to identify the line I want using the designator and grabbing the values to the right of it. In other words, using the list above, I only want the street (DAG) returned. What's the best way to do this?

I have following data set Unique ID Product ID submode Region Sales 1 x x1 US 10 2 x x1 EU 20 3 x x2 EU 30 4 x x2 US 40 5 y y1 JPY 50 6 y y1 US 60 …

I was wondering whether someone might be able to help me come up with a script, a calculation, a complex function, or whatever is necessary to summarize the number of descendant tags for each record in my tags table? I’m relatively new to FMP, and I’ve been using it to organize my notes for historical research. The basic tables in my DB are a notes table, a sources table, and a tags tables. The tags table has a title field, an id field, and a parent_tag id field, and there is a parent_tag table occurrence based on the tags table and related to it via its id (tags::parent_id = parent_tags::id). I have already created a simple calculation that Counts the childre…

Hello, thanks for your interest. Looking for assistance in fixing my calculation to properly format currency. Â This calculation is a note on a report, noting if their line item is discounted or not, only populating if there IS a discount. Â I've attached images of what I have and of what Im looking for. Maybe with proper currency formatting of calculation, I can trim the extra lines of adding the "$" . Â THANK YOU for any help with this one!

Hi - I use this calculation to display the value just if the variable have a value. The problem is when the variable doesn't have a value the calculation add empty spaces to the result. Case ( Length ( $i1 ) > 0 ; $i1 ) & ¶ & Case ( Length ( $i2 ) > 0 ; $i2 ) & ¶ & Case ( Length ( $i3 ) > 0 ; $i3 ) & ¶ & Case ( Length ( $i4 ) > 0 ; $i4 ) & ¶ & Case ( Length ( $i5 ) > 0 ; $i5 ) & ¶ & If there is a better way to accomplish this I will appreciate your help. Thanks

If I have a field which contains: MP1-01, TV-01, TV-02, TV-03, TV-04 it sometimes, because it breaks at the end of the page, looks as this: MP1-01, TV-01, TV-02, TV-03, TV- 04 It seems there was a shift/key combination on Windows which inserted a space which stopped the break; I forget what it is called now so I can't even look it up. Is there a way of doing the same thing with the dash? It would need to be turned into a word character which isn't used much but this is for display so ... Whatever that key-combination, use Substitute ( field ; "-" ; " - " ) ... where the spaces are actually that key combination. Ah well, it's late. Oh, actually it'…

Trying to search for records with Effective Dates of May 2013 through May 2014. Heres the function that I have so far that isn't working. Let ( d = Get ( CurrentDate ) ; Date ( Month ( d ) ; 1 ; Year ( d ) ) & "..." & Date ( Month ( d ) + 3 ; 0 ; Year ( d ))) Any help greatly appreciated. I'm thinking a function that specifies the year first as being this year (current date) through last year (current date - 1) and then specifies get current date for the month (May) Thoughts?

I am trying to concatenate unique child record for parent record...i was able to concatenate all child records for a given parent record using Substitute ( List ( child::record ) ; ¶ ; ", " )....but cannot figure out how do i remove the duplicate values for example: Car company has multiple models..but each model can only car, mid-suv, large-suv, truck etc.....now when i am populating information for Ford, i only want to have car, mid-suv and truck once not for all the model...and same for Hyundai can someone please guide about how to remove the extra child record during list thanks

Hi! How can I use the substitute function to remove the ¬ character in a text field? Carriage returns can be replaced with "" but it seems that those not. It is driving me crazy... Thanks is advance!

My table is sorted as follows (Month) then (Year) Then (Effective Date) I've attached a picture as a reference. Â When I enter the layout, I'd like filemaker to automatically scroll to the current Month of the Current Year as right now it shows the entire year and starts at the top. Â Any thoughts on how to do this? Â Thanks! Â Â

I have an imported web form I need to retain everything on the right up to a "-" symbol. Everything else needs to be removed. How to I write that in a text calc? ex. three blind mice - see how they run would return three blind mice Could someone help me? While I am at it I would like to know how to parce the text both ways…. I would like to know how to remove everything up to the "-" and I would like to know how to remove everything after the "-"

Hi I am trying to figure out a complicated maths field for my dads storage yard the prices are based on a percentage sliding scale on an excel worksheet, as he has many size's of containers He has a base price of £10 never changes then its 1% per day based on the storage price there's about 50 different prices that change from month to month field 1, Storage_Price field 2, Days field 3, Storage_Fees so it should be Storage_Price "£10" x Days "28" = Storage_Fees "£2.80" field 4, Base_Fee field 5, Total_Due + Base_Fee "£10" = Total_Due "£22.80" i hope that's clear as im kinda confused just reading it back myself so the stora…

Hi sorry ive read lots of date calculations but just cant seem to find the answer im looking for i almost think this should be fairly easy but im just not grasping it Please help OK i have created 3 fields Field 1, Record created (auto entered) Field 2, Days (entered manually) Field 3, New_Date (record created + how many days entered to give a new calendar date) Many thanks in advance

Hi, I am completely new to Filemaker. I work on a database for timesheets to calculate hours worked by employee to calculate their wages. But the wages are paid not per calendar month, but a payroll month. Payroll month starts on a Sunday before last Thursday of the previous month and ends on a Saturday before last Thursday of the month. How do I calculate a payroll month for a timesheet date? Thanks

I have a requirement from a client to create a consecutive ID number that starts from 0 each day, and increments on record creation. Final solution would look like: VAL-YYMMDD-### (VAL-140506-003) I have the first part worked out VAL-YYMMDD, but I am having a problem thinking through how I would have the number start from 000 each day. I was wondering if MAX might get me something but I just can't come up with a way to construct it. Any thoughts would be greatly appreciated.

Can any one help me with a formula that does the following: If pupils are born on or before 31/8/97 then they are group "A" If pupils are born between 1/9/97 and 31/8/99 group "B" If pupils are born between 1/9/99 and 31/12/00 group "C" If pupils are born on or after 1/1/01 group "D" Where date of birth is DoB and group is Age Grade Many thanks

I am having problems with safari but that is the default browser my filemaker script step points to. How can I change the browser to chrome or firefox ?

I have a field called Sizes, which is a check box. The options are Studio, One / One, Two / One, Two / Two, Three / Two. I'm trying to make a calculation field, export_sizes, which would be in this format: One Bed, Two Bed, Three Bed I don't care about studios. But if a record has sizes one/one and three/two, it should be calculated at "One Bed, Three Bed". If a record has sizes two/one and two/two, is should be calculated at "Two Bed". This should be simple, but I can't seem to make sense of it... Any help would be greatly appreciated.

Hello fellow Filemakers I have been trying to come up with a dynamic count calculation for a number of portals all relating to the one table in vs 11. These portals are navigated to via tabs so I do have a reference for the count relationship by way of the tab's unique object name. Now this is very easy if I create a count calculation field for each portal but not so easy if I try to create the one count calculation using Case. It all works if I have one level of tabs but I have nested tabs and I can't figure out how to solve this one. So this is an example calculation for the field "Count". Case ( GetLayoutObjectAttribute ( "Tab A1" ; "IsFrontTab…

Hello, I was trying to solve my problem using this thread: http://fmforums.com/forum/topic/32190-formatting-decimal-numbers-to-fractions/ But I can't for the life of me figure out how to modify this calculation without the constraints specified in this thread. I just want my decimal to display as a fraction...no constraints. Is this possible?

We just recently imported all data from our old system into filemaker and have found an issue. Our old system stored the zip code as a number but displayed it as text. So the zip code would show up as 00802, but was stored as 802. Now that everything is in filemaker, i want to be able to automatically add the leading zeros so i can print mailings out. Im not quite sure how to accomplish this as the number of zeros to add would be different. For example: 802 i would have to add 2 zeros to make it 00802 8802 i would have to add 1 zero to make it 08802 Is there any type of calculation to accomplish this? Thanks

Hi Board, I have viewed a few YouTube videos with people running calcs to parse text. As they are building the calc, a preview of the result shows up in the bottom of the calc definition window - so you can see how close you are to you're requirement. They seem to be older versions, 7, 8, 9etc. Does v13 have this feature? Where can i find it? Thanks, Harry

I will be inputting time sheets for a two year period. How do I check on he effective pay rate for the date of the time sheet. I have two Tables - employees and time sheets

Hi All, Im still learning filemaker on daily basis, and have mostly completed everything. However I need help with one thing. I have a field where I want to enter data as "25,34,23,12". Is it possible that filemaker autosums these numbers and replaces it with the total, in this case 94. Thanks for your help Yash

I am trying to make a field required is another field in the same table is completed by a user. Example: I have a field called "Custom Change Item". If a user inputs text into this field, I would like for another field called "Custom Change Cost" to be required. I believe this can be accomplished through a validation calculation but I cannot figure it out. Thank you for any assistance anyone can provide.

Hi I would like to find out if there is a way to run a script from a list by random means In other words if I have the following scripts: script 1 script 2 script 3 script 4 script 5 script 6 script 7 I would like to have a mains script called "Random script" and it would run a random script from the list. So when I activate the script it plays Script 6 but the next time by random from this list of scripts it plays script 4 Can this be done and how Thanks

Hi All, I am trying to export a filemaker file with many records of people but I want to use it to create a nice video back ground. for example the final text file needs to look like this. But it can be created in a text field in filemaker and used from there Lets say I am using names of basketball teams for the sake of simplicity I am repeating 5 records..... I would like a text field to use the found set I select and turn it into a graphic page in a text field in filemaker This would be to use different colors and fonts. like this Dallas Baptist University Patriots • Shepherd University Rams • Villanova University Wildcats • Fresno State Bull…

I'm embarrassed to post this but trust me, I've exhausted searching the internet for strategies. Surely, with Filemaker Pro 12 there must be a way to create a function that looks to an existing text field and searches for a specific string?!? 'Products' would contain none or some or all of A B C D E , in a random order. I would like to create a calculation that searches for 'B' and returns a text expression (there is no "contains" function). I can't think it should be so difficult, I must be using the wrong terminology? ('B' is just a representation of a longer name, ie. "cookies" or "twinkies" could be the products ...) Thanks! Lance

Ok, i know that this should be easy but it is driving me crazy to figure out how to total one field every time another field has a number added. I have a field called MilesRan, this needs to be the field that keeps changing every time this field "CompletedMiles" changes. Each record will have the number of miles ran. I want the "MilesRan" field to total every time this happens. So if I have 5 records with the following number inputted: 5 10 6 4 3 9 8 So now "MilesRan" should show 45 miles when I get to record five. I have tried the following: Sum (CompletedMiles) "but I know that this is not takin into consideration that each record is getting a …

Ok, I have a solution where there has been no serial incremented ID of fields tracked, as the solution is using UUID for most parent child relationships. I have a calculation where I need to return the first and last values of child relationship as seen from the parent relationship, but I have to do it from the child table. To get the first record of a child set of records is easy, as the creation order of the records dictates the first record, however without some type of serially incremented ID, there is no way to reverse that order (at least as far as I know). I can't use timestamp as the records are created all at once in a loop very quickly, so most of the time…

I'm trying to create a report for a solution, and have hit a bit of a roadblock when creating a report.  I have 3 tables:  1. Statistic Categories     -Each record in the table is a particular statistic code  2. Incidents     -Contains individual incidents. Each incident is coded with a statistic (using a value list, containing values from Statistic Categories table)     -Only 1 statistic code is assigned per incident  3. Reports     -Reports are related to a particular incident, and contain an incident report for the incident.     -Each incident may contain multiple reports   I am using a report format a…

I am maintaining a system which has been written by several prople over several years. A few days ago I needed to modify a routine which set global variables for each of about 40 columns in a record (so context could be switched and still access those fields). I needed to add about 15 more columns, and that sort of repetitive work doesn't appeal to me. Thus I came up with the following: Evaluate( "Let([" & Let(q=Get(LayoutTableName);ExecuteSQL(" select '$$'||fieldname||'="&q&"::'||fieldname from filemaker_fields where tablename = '"&q&"' and fieldclass = 'Normal' and fieldtype not like 'global%' order by fieldname ";"";";¶";""))&"];0)"…

Does anyone have a function that can drop the last 3 '0's of a number and replace it with a letter? For example: $45,346 changed to $45K $1,234.411 changed to $1,234M Thanks in advance.

Hi all, Â I have an email template table where users of my solution can create data driven email templates which used to pull data simply from the same table. Â My problem now is that the data is in a separate table and there are multiple records to pull data from as merge fields. Â I'm pulling data from a contacts table where a few of the related contacts are flagged as 'primary' contacts. Â I want to pull in merge variables but get filemaker to identify the primary contact records and pull data from those only, or from the first primary contact in the sort order. Â Attached is a screenshot of my merge variable calc using the substitute function to replace…

Here's an idea for a function in FileMaker: when you're importing names that might have slight spelling differences with the already existing ones, one might want to create a function that would compare an imported name and surname with all the available, correct ones. Then it would enter the closest match from the database at a cut off: i.e. 80% to 100% similar. Does that ring a bell to anyone? Do you guys think that would be a worthwhile function?

I have been given responsibility for making sense out of between 750-1000 records that contain the legal description of land parcels containing oil & gas mineral leases. The listings currently all have a single field containing that information. I need to parse that single field into 6 new fields. The original single field I am starting from looks something like this: Section 36-3N-10W or Section 1-13S-2E or Section 23-17N-9W. These describe the "Section, Township & Range" of each piece of land. The separators between Section-Township-Range are always plain hyphens with no spaces. The first position is always the Section Number. The middle position is alw…

I am trying to rename some images so I can remove characters that are causing trouble when they are used on the web. For example ( ° and & ) are causing trouble. Is there a good way to clear out all characters that may be problematic when used in image file name on a website?

Hi, I have a database of records divided into account numbers - so for example account number 5000 might have 20 records and account number 5001 might have 30 records. I need a way of having a running count sorted by the account number. This is easy in newer versions of filemaker by using a summary running count field and a sorted by field. The problem is this needs to work in FM6 which has the running count but not the sorted by options. Any ideas? Iaxe

How can I grab the file extension from a file that is placed into a container field? Example of file names in my containers: sales.report.2014.pdf company.web-logo.svg poster1.01-36.jpeg t-shirt_2.06-hr1.jpg My ultimate goal is to rename them and I want to make sure that the correct extension gets added to the new name.

I have a built in table in my database that has a quality of life questionnaire. There are 20 questions (20 fields) and eery 5 fields is considered one group. For example group 1 will determine the QOL score for physical wellbeing. Once the patient fills out the questionnaire I want a calculation field to determine the QOL score. I can do every calculation for it on filemaker except one. Once the score is calculated, it must be divided by the number of non empty fields in each group. Foe example, the patient may fill out questions 1-3 in first section and question 5 omitting 4. Once the score is calculated it must be divided by 4 since they left question 4 blank…

I am having trouble with field calculation in Filemaker 13. The result of the calculation is two letters with conditional formatting determining the bar colour on a gantt layout. This unstored field calculation was working as expected then suddenly ceased to generate the expected result. Let([ ~hourfm = If( IsEmpty(Book_Time_From ) ; 1 ; Hour( Book_Time_From ) - 7 ) ; //gantt starts at 1 meaning 0800 hours ~hourto = If( IsEmpty(Book_Time_To ) ; 12 ; Hour( Book_Time_To ) - 7 ) ; // gant finishes at 12 meaning 1900 hours ~category = If( IsEmpty( Book_Category ) ; "Re" ; Left( Book_Category ; 2 ) ); // if not set make a 'Request' ~current_date = xgantt_gd_S…

We have a "Jobs" db that is keyed off of a "job number" field. There can be multiple records for one job if there are chage orders/additions to the job. Each record includes a "labor hours" field. I am looking to calculate how many field hours we have left for all the open jobs. I already have a report that does the subtotal of hours available, hours used, and hours remaining when sorted by "job number" and that works great. I am trying to create a calculation to find the total of hours remaining with the twist that i want to exclude any jobs that have a sub-total of "hours remaining" that is negative. I know I could do a simple calculation of available hour…

Hi, I have small issue. I have created to 2 tables. Payments and PaymentLine. Payment is parent and PaymentLine is child. Payment has 2 fields. ID and Date where as PaymentLine has fields amount category note and check no. I want to create a list of all Payments Records they occurred in one month and total amount. I created a Summary Field in PaymentLine table and made it total of Amounts. But i perform search using a Perform Find. I don't get the total of the month. Instead i get total of all the records. What i am missing? 08-Apr-2014.zip

I have a calculation field ("Action") in which I need to look at two other fields ("ID" and ID2") and determine if they are both blank (return "ADD") or 'not blank and match' (return "UPDATE") or only one field is blank (return "UPDATE") ... the CASE function is not working properly ... the calculation is not discerning between two fields being blank and two fields being non-blank and matching ... is there another function I should be using? This seems to be a fairly basic query .. ? Thanks in advance! L.

I am a new user to FM but am a user of MS Access. I am tying to change a file over from MS Access by writing a new DB using FM 12 pro. I have imported a product table a supplier table and a product type table(Chem Code). TheCem Code table is related to the product table so that each product belongs to one "type". I have also created an order table and a line item table in FM. I have been successful in relating the products ordered through the line item table but am unable to get the "type" to auto enter from the Chem code table at the same time. I need to do this so that I can then group on product type later. In MS Access I could do this by using queries my relati…

I want to make this field (PO# Field) a calculation field to show results based on fields: Facility field and Equipment field. For example if Facility = "Mercy Hospital" and Equipment = "mattress", result should be: PO2222, but if Facility= "St John Hospital" and Equipment= "mattress", result should be PO4444. Question is: what would be the syntax for the calculation on the PO# field?  Please see Picture attached  Using FMP 13 on PC

Hi, I am trying to do something that seemingly should be very simple, but I cannot figure out how to accomplish it. I have a field that I am trying to have calculate a number based on certain properties of other fields. This field is called "groupID" and should display either "1", "2", or "3". Where I'm running into trouble is in defining the "1" or "2". Basically what the equation is in layman's terms is that when the total number of non-blank records in the "DonationMethod" field in the "Donors" Table is ≤ 45, I want it to display "1" in the "groupID" field; if the total number of non-blank records in the "DonationMethod" field in the "Donors" Table is > 45, I wan…

This is probably an easy design concept, but my dense brain isn't seeing it. I'm working on a timecard system, and I'm running into a problem where I have co-dependent fields. In my hours table, I have a field which calculates the amount of VTO (Variable Time Off - like vacation time) that is used if the total hours entered on a workday is less than 8. However, the system can only fill the deficit in hours (8 - hours worked) if you have enough VTO available, so the calculation first checks the value of the VTO_Available field from the related VTO_Month table. It looks like this: Let ( [ Worked = TotalHours; Deficit = 8 - TotalHours ]; Case ( Worked >= 8…

Hi I was just wondering if anyone has ever tried to get the keywords or tags automatically from a Youtube video? I would like to see if this could be done? Any answers or guidance is appreciated. Thanks

I have a task database and on every record the person's name doing the task is recorded. It used to be a calculation field (first and last name) but i changed it to a auto-enter calculation text field so it can be modifiable. However, I kept the calculation field and here's why. At the end of every month a reported is printed out, using a layout i made. What I want to change is, not have the name be recorded if the it was a weekend, or a holiday. Even though the task is not being done is still has to be logged and a value list with the option "School Holiday" or "Weekend" has to be selected. I created a script to have the name omitted but i think it's sloppy and I'm s…

I have a sales order database with a serialized field that auto-generates a sales order Number. The sales order table contains about 25k records and there are about 15 records That were created with an asterisk preceding the sales order number, they are still in Proper sequence but just contains the asterisk at the beginning. The field is a key field And the issue has propagated to my other related tables. Has anyone ever seen this issue? Database hosted on FM 11 server, Mixed clients 10 & 11 all windows. Thank You Anthony

Hello everyone. I'm trying to migrate contact data from a really messy custom Goldmine solution (nightmare) to a new FileMaker system. The data is pretty messed up. Very little consistency. Multiple fields storing the same type of information. Sometimes it's in one field, sometimes another. Sometimes there's a single value in a field I need to process, sometimes multiple values. Sometimes I need to evaluate several fields before deciding what to do. I'm migrating about 7,000 organization/company records and about 40,000 related contact records. I'm doing my best to whittle down the number of records that will need to be reviewed manually after import. We are,…

I have a simple database which uses 4 fields as shown below: I have Trailing Subtotals set for each column (except Agent). The problem is that the Totals column does not produce the correct total - it's very close but NOT correct. For example, Pay1 may show $500, Pay2 may show $350, and Totals may show $843.46 instead of $850. I have a Microsoft Access program which does the same calculations correctly. Suggestions greatly appreciated. Note, I'm far from an expert when it comes to Filemaker. Agent Pay1 Pay2 Totals

Hello How do set up a field validation for the following scenario: Field A - Drop-down list with "Good" and "Poor" Field B - Comment text field Can I force the user to enter a comment in Field B if Field A is "Poor"? I am pulling my hair out, I must be missing something in the calculation to validate the field . Thanks for the help

Hi I have a large number of topics in a value list. I have a field called "topics" using that value list and I want to know if there is any way to script finding what topic I scroll to in the list when I let go with the mouse. Scroll to the item and let it perform a find for that topic.?

<p><span style="font-size:14px;">If I have a field which contains:</span></p> <p>&nbsp;</p> <p style="font-size:11px;font-family:Arial;"><span style="font-size:14px;">MP1-01, TV-01, TV-02, TV-03, TV-04</span></p> <p style="font-size:11px;font-family:Arial;">&nbsp;</p> <p style="font-size:11px;font-family:Arial;"><span style="font-size:14px;">it sometimes, because it breaks at the end of the page, looks as this:</span></p> <p style="font-size:11px;font-family:Arial;">&nbsp;</p> <p style="font-size:11px;font-family:Arial;"><span style=…

Hi all, I have a table A with a parent tables B, C - I have a portal created for B&C on A's layout - I want to get some recent activities to be shown in find mode on a portal (ie sorted on recent dates on top) - Issue is > how do i get a collection of few fields in to one field and get this represented on the portal? -- such as a formula in one field which is a collection of some text fields and date - ( this will look like a paragraph of text starting with a date) Could some one help? Haf

Hello all, I am new to both FMP 13 and developing. I operate a not-for-profit travel clinic and wish to automate writing prescriptions with data called from a form that I have developed. The paediatric dosing depend upon weight of the child, and the total doses dispensed, for the duration of the trip. I have a Case statement, which in its individual parts works well, but when put together only returns the first part. I have spent unknown hours looking at this but cannot determine what the issue(s) are. I would be very thankful for any advice. Radio buttons were used for “accept” or “decline” by the parent, and calling the prescription (Rx) body, instructions for …

I have a table, that has row with several fields: Insureds Name, Effective date, Premium etc. The row currently are sorted by effective date. Looking at creating a script that goes to a specific portal row date that matches the effective date. Any ideas?

I have a table named 'Projects' and a table named 'Tasks'. Each task is related to a certain project. Each task is assigned a priority in the 'priority' field in the 'tasks' table, it can have one of three values which are 'high', 'medium' or 'low'. 1. I want to find how many tasks are assigned a priority of high for example. 2. Then i want to display this data on another layout, so it will say something like 'There are 3 tasks with a high priority'

Hello, I am currently using FMP 12 and I have been searching everywhere for how to calculate the median number of a field for a set of records. I really would like the median to be a summary field where I can sort some records and pull a median. Mean is not good for my use. Here is what I am trying to do: 1. I have a database that calculates the wait time of each patient for an appointment and I have to pull out wait times to give to the hospital. 2.A mean is not good as there can be outlyers depending on their diagnosis 3. Important fields are Patients:Wait times, Patients:doctor_assigned 4. I want to run reports for each doctor (i am currently using a fi…

I have a text field called TYPE with an attached value list with three alphabetical values (A,B,C). I intend to make a pie-chart to display the % of A,B,C; I reckon I should create a calculation field which takes the data from TYPE and calculates the number of times A, B or C appear. How can I do it? Edit: I added a sample DB for clarity and reference, basically the field I want to interact with is: "Autorita" which can be one of three values (Cassazione; Corte d'Appello; Tribunale); in the sampleDB I made 4 records: 3 of which have "Cassazione" in it and one of which has "Tribunale". What I need, on the "Stats" layout is to make a pie-chart that display a pi…

I use the calc below to format phone numbers. However I can't work out why the "+" sign does not display when the format is recalculated. I now get for example "61 3 9573 4822" when what I want is "+ 61 3 9573 4822". Can anyone set me right on this one?

Working on a calculation for my portal that says show portals when Field (Effective date) = (Get CurrentDate) (plus the next 10 days) so the portals would show if the effective date was any day between now and 10 days out. Thoughts?

Hi All, I'm using FMStarting Point and I'm looking to grey out a box in and read "absent" in the box on a report I have made on a given date and subject populated from the "Communication and Notes" section of a contact. For example if the if the subject in the "Communication and Notes" is selected as "absent" and the date is 23/07/2014 then I need the box on the report to read "absent" and be greyed out that day. But because I print the report maybe 5 days in advance I need the calculation to wok if its printed within five days of the date and including the date before the actual day. I hope that all makes sense. If anyone is able to help and is able to off…