Calculation Engine (Define Fields)

I'm wondering of there is a summary or calc function that would look at the values in a field and count how many unique values there is. ex. Ive got a very simple ECM. three table all link thru the record number. In my call log table their are multiple records per record number, i would love to have a field that returned how many different contacts were in the table (not how many calls) if there was a field that just counted how many unique occurrences were in the record number field in the found set..... id be golden

I don't quite understand how to write a calulation to take into account that there may be more than one record to look at. What I have is 2 tables, one with people and another with the tasks for all people - with entries related to a person. There may be up to 3 entries (or tasks) for any one person. I have another field marked complete, which I enter the date to say the task is completed. I want to enter either a 1 or a 0 in a field on the persons record depending on whether any of the tasks do not have a complete date. The calculation I'm using currently: If (IsEmpty (workflow_records::records_complete ); 1; 0) However, if I have say 3 records,…

I am still puzzled by this there has to be a way to pull from a list of items and the updated quanities to show on one record then update the info without compromising the record. I cant get passed this part. I use a drop down list of available items and select an item number. Based on the number I select shows the Qty and I enter in the amount of that quantity used and that completes a record. But when i update my inventory with new numbers the records then update which defeats the purpose. Am I missing something here?

I have a table consisting of event locations, which has a check box field containing 9 conditions. Each record therefore has a combination of conditions for each event location. I have another related table that contains performance data from 200+ events (each event has 45 unique players, rated 1 to 45). I have a third related table that contains 45 records of people (the same 45 people who are counted 1 - 45 in the performance table). I am trying to figure out how to determine what conditions each person performs best in. any idea how to do this?

Is there a way to automatically trim whitespace off the end of a field? Without a script? Do I need to go to an event script setup?

Hello FM Forums, I primarily work in Excel and can solve a lot of problems there. However, I have been presented with ~1m records that need summarizing and from my limited understanding of databases, think this is one way to solve my problem. I have zero experience building/manipulating databases, so after several hours of searching and trying to figure this out, I am posting here asking for help. I have ~1M records that have 3 fields: UniqueID NumberDays NumberUnits All fields can be repeated. I would like to count all combinations of NumberDays and NumberUnits. The output would be a table with # days vs. # units (per the attached file). I can solve …

Hi I create a Delivery Order Form. In side the Form I have Order Qty, Per Pallet Qty and 20 field of Pallet and Time. Here I try use the calculation engine, when I key in the Order Qty at the Qty field like 2550 set and I want per pallet have 500 set. So we need 5 pallet with 500 set and 1 pallet 50 set. Is they any way when we put in the Order and per pallet, on the pallet field were auto calculation and show 500 set and give me 5 pallet of 500 and 1 pallet of 50 (like I show in my file attach). Thanks gregory DO_copy.fp7.zip

I'm still a calculation newbie, so this simple situation is puzzling me. I have a database of films, and since sometimes I'll want to print the simple title "The Big Sleep" and other times the 'postponed' version "Big Sleep, The", I have created two title fields: Title_Prefix and Title_Primary so that these elements can be manipulated in different situations. Also, I have a radio button to click if the title is a "Given" title (one created by the cataloger in the absence of an official title). In this case, the copnvention is to enclose the title in brackets when printed: [bogart Test Film]. I call this third field Title_Given. So, say I wish to create label…

Hi, I have following format in my phone number field. Let([ con_phone_1 = Filter(con_phone_1;"0123456789"); Length = Length(con_phone_1); Phone10 = "(" & Left(con_phone_1;3) & ") " & Middle(con_phone_1;4;3) & "-" & Middle(con_phone_1;7;4); Phone11 = "(" & Left(con_phone_1;3) & ") " & Middle(con_phone_1;4;4) & "-" & Middle(con_phone_1;8;4) ]; Case(Length = 11; Phone11; Length = 10; Phone10; con_phone_1) ) How can I remove the text format in this calculation? TextFormatRemove (con_phone_1) Thanks

How is it possible to have 2 case functions in one calculation? Case 1 = Case ( uw = "g" ; ?; uw = "oz";: * .0352739619 ) Case 2 = Case ( lye_type = "NaOH" ; recipes::sum_NaOH * (1 - superfat_percent / 100); lye_type = "KOH";recipes::sum_NaOH * (1 - superfat_percent / 100) ) Both NaOH and KoH should have the possibility of "g" and "oz" conversion. Case ( uw = "g" ; (Case2); uw = "oz"; (Case2 * .0352739619 ) Thanks

One thing I keep finding that I'd like to be able to do in FM is to concatenate a text field accross multiple records. This is analogous to the summary fields that allow you numerically sum or average a number field across multiple records. Let's say I have a database with two tables: People and Households. I'd like to name each household with a calculation: Household Name = "The " & People::Last Name & " house" But that will only work if all the people in the house share the same last name. If Joan Smith and John Regan live together, I'd like for their house to get the name "The Smith Regan house" On the other hand, if John Phillips and Sandra P…

Forgive my ignorance.... Is there a way to move a global variable defined in a script as $$Var1 from File1-Table1 to another File (say File2-Tablex). e.g. In file1, script sets $$Var1 as recordID, then goes to a separate file2-Tablex to create a new record and save SSVar1 in a field in that record. This works if table1 and tablex are in the same file. But the moment, the script goes to the new file, the SSVar1 is lost. I am using a physical global field to move the information from one file to another. I was wondering if it could be done using defined variables. Thanks for the help....

Hi there! This is my first post on this forum. Have heard great things about the greater Filemaker community and hope someone might be able to help me with this problem. Before we start, I warn i'm a relative newbie to this software... I am playing around with FileMaker to create a database to catelogue lighting fixtures used in a theatre rig. Am hoping that by using one main database, i can generate varied paperwork for a number of different uses. One of the peices of paperwork I wish to produce is what we call a 'Fixture Schedule'. It's a list of all lights used on a show sorted by position (which i've created easily enough). In theatre, when w…

Hi all, I have a database for students who have graduated from their degree. I want their final ranking to appear in a layout (letter). Eg. Joe Blog graduated in the ceremony held on 12th May 2007. Joe was ranked 25th out of 200 students in his ceremony group. I do a find on the field 'graduation date'and I sort based on the field 'final average mark' (which is a calculation). I view in List Mode and I can see Joe's rank for his graduating year. How do I capture this information in a letter? I'm assuming I would create a field 'ranking' which would contain a calcuation. The calculation would need to determine: 1.the total number of students f…

I have a 2 field database field 1 is a text field field 2 is a yes no check box. I only want one record to have yes selected. Therefor if someone click on yes when another record has the value set to yes, the action of clicking yes on the subsequent field changes the value in the intial record.

I have a database where the user chooses the week beginning on a Monday from a drop down calendar. There are other fields that then calculate the date for the other days of the week. So if they choose Monday 4-1: Tuesday 4-2, Wednesday 4-3, etc. is automatically generated. I'm trying to write a validation calculation that ensures that the date the user selects from the drop down calendar is a Monday. I'm not very familiar with the date functions so any help would be appreciated.

i have a table called "emails", and one called "search" which are related by a serial number. Emails all contain 1 serial number and "search" contains a list of serial number. Emails has a field called "temperature" which can be marked hot, warm or cold. I want to put in start date hot, and end date hot fields into "search" have tried this Min ( Filter ( Emails::temperature ; "HOT" ) ; Emails::newDate ) and other similar things please help!

I was looking for a function or a solution if it already exists but was not able to find one. Basically I am looking for a way to compare two sets of data (text) and write out the differences. I got as far as comparing text in two fields to check if it is same, that was easy. How would I make it so that if the text is different the different text gets showed in a new field by itself or if possible the whole text with the differences highlighted? Something like: Field1: This is a test. Field2: This is a tes. Result: This is a tes[color:red]t. or Result: t The first way is preferable. Thanks.,

Hi all, Just a newb question here - I need to take a date and calculate 10 weeks on from it automatically. If the day turns out to be a weekend it should automatically round off to the next Monday. Any ideas how I need to do this? Some of the solutions on here are a little intricate for what I need for this! Thanks in advance for your kind assistance. Rgds

Hello, I'm new to FM so i dont even know if what i'm wanting is possible. I have a date feild for when a vehicle is taged. What I need to know is how to set an alert to give me a 2week notice before the tags are due. I've tried the brithday alert script but alas no go. Any help with this one would greatly be Appreciated.

I've prepared the attached example in an attempt to unravel a problem with FileMaker's capability to drag and drop images from one container field to another. The drag/drop part never fails -- it's the image size that suffers. It gets downsized by some mechanism that I can't figure out. I've tried different combinations of Graphics Format -- Enlarge and Reduce -- to no avail. FileMaker seems to apply some unseen sizing restriction. Has anyone else noticed this? Is there a way to preserve what the user originally drops or pastes into a container. Thanks in advance for any help.

I have a question about calculation fields that could be a pretty important one to others as well. It addresses strategy when building larger databases. I'm in the process of building a comprehensive solution for calculating math for all the products my cabinet shop builds. It also relies heavily on the use of graphics. (There could easily be 100 different views of the data between various layouts and tabbed panels.) There are a lot of calculations. Most of them are simple arithmetic. Someone today gave me an idea that could change my whole approach. I need some advice on this idea. Rather than building this database with a million calculations, wo…

I have a calculation that looks like this: Right ("00" & Int ( shots::shot); 3) This works fine for num fields. However, I would like to use num and characters. "001a" for example. Can't seem to figure this one out.

I saw this a few weeks ago and cant find it... If i have a calc field and want to show the result as either 0 or >0 but never <0 how would i do this... do i add the not equal to <0 in the calculation? my calc is set as text as I like the format better than number.

I would like to have a field that shows 1 if the date field is at or prior to 1/1/09 and 0 if the date is after 1/1/09. Thank you

Case(IsEmpty(Date Inspected) or IsEmpty(Frequency); GetAsDate(""); Date(Month( Date Inspected); Day(Date Inspected);Year(Date Inspected)+Frequency)) I am using version 8 I have not used fmp for a while am a little rusty. The above code calculates a date from the date Inspected and add's the number of years to it and places the result into the field called Due date. However when i set it up i set the Frequency to be years instead of months, but i cannot seem to get the result to revert to add in months instead of years. So if the user puts today's date as the Inspection date, and 3 in the Frequency, the result should be 31/6/09, but i get 15/3/2012. …

I am new to File Maker and can't figure this out. I have a date field called WeekStarting that is a dropdown calendar where the user chooses the week beginning on a Monday. I want to calculate the dates for Tues, Wed, etc. and display them. I tried using this calculation for figuring out the date on Tuesday, but it's not working. Date(Month(WeekStarting); Day(WeekStarting)+1; Year(WeekStarting))

I'm a medical student doing a database for a medical research project and I've decided to try FM9 (since it's avaiable at my school). The thing is one patient may have more than one exam (echocardiogram). So I've created a separated table and used a portal to show all exams of each patient. The problem is that when I export, it always export (i have to export to SPSS for the analysis) the first exam created for that patient. So I tought that one way to counter this would be to recreate the fields in the main table (as calculation fields) in such way that for each patient I could select which exam would be exported. So I've created a field(examselect) on which I …

Hi, How can I add up all the sold products? So if I sell 2 products to one client and I sell 5 products of the same kind to a different client how can I add up all the sold products. Is the picture enough or should I post a file?

I have a table which I post payment entries in. We recieve these payments every 10th and 25th day of the month. I have a date field that holds the date and I would like the invoice ref field to automatically create when the date is entered. for example: Date invoice ref 25/3/2009 MCC300 10/4/2009 MCC301 25/4/2009 MCC302 10/5/2009 MCC303 and so on any help would be appreciated thanks

The following unstored, Calculation yields a result of "Yes": field_Test = Case ( RightWords( GetFieldName ( Self ) ; 1 ) = "Test" ; "Yes" ; "No" ) The following unstored calc, where Field is any field in the table, yields no result (Null): field_Test = Case ( RightWords( GetFieldName ( Self ) ; 1 ) = "Test" ; Field ; "No" ) Why does this not work? That is, why does the calc work when the true case result is literal text, and it does not work when the true case result is a field. Note that the calc result is set to the correct data type, so date type mismatch is not the issue. Darren Darren Burgess

I have a Summary field called MinDate that returns the Min of a date field called TheDate. Then I have a Calculation field called MinDateWords that returns the monthname and year of the MinDate summary field. I want to use the MinDateWords field in a Sub-Summary that breaks on OrderID, but it is giving me the minimum of the found set, and I want the min of only the order that the Sub-Summary breaks on. I have checked the sorting and I know it is correct because the MinDate field in the same Sub-Summary is properly returning the minimum date for each order, not the entire found set. To reiterate, the problem is that MinDateWords is returning the min val…

I created a script to either exit the application or take the user to the administration page depending on layout and privileges. But the script is not working well. This is the script: If[(Get(PrivilegeSetName)="Admin") or (Get(PrivilegeSetName)="[Full Access]")] If[Get(LayoutName) ≠ "Family File (Admin)"] Go to Layout ["Family File (Admin)" (Preferences)] Exit Script[] Endif Endif Exit Application The script is set to run in full access privileges. When the user is neither Admin nor [Full Access] it evaluates the first if statement as true. I could not find the error in the code. I checked everything in the data viewer and all was corre…

I have an inventory file that amongst many things has an Item table and a Product table. Item table contains information about the item such as cost, vendor, vendor code, item code etc. Each record has an unique record ID (auto-enter serial) however a couple of items can share same item ID. Reason for this is that the same item can be obtained from couple of different sources. So for example item Case could have 3 records: Record 1. Record ID: 001 Item ID: CS001 Name: Case Cost: 1.25 Vendor: Vendor1 Record 2. Record ID: 002 Item ID: CS001 Name: Case Cost: 1.20 Vendor: Vendor2 Record 3. Record ID: 003 Item ID: CS…

Hey! I'm trying to figure out a calcuation field that will do the following: Figure out how many orders shipped out each day and then show that number. For instance, on the 26th, it will find all jobs that shipped out on that day and then the result will be something like "5" and then on the 27th, it will show "9" And i am want to add this to a monthly calendar view so that on every day on the calendar it shows how many jobs shipped out that day. Would anyone be kind enough to help me figure this out? Thanks!

I have a table called "day" with a single record for each day. I have another table called "Mail" containing many records for each day. The two tables are related by date. There is a field in "Mail" called "temp". This is a dropdown containing HOT, WARM or COLD. I want to put a field in "day" called "hot" which counts the amount of records in "Mail" where "temp" = HOT. please help!

I'm trying to set up a field to calculate the elapsed time that an order takes. I've got a timestamp_create field that auto-enters a timestamp when a new record is created. I'd like to set-up a field/script that calculates the elapsed time in HH:MM:SS when the "Done" script is executed. I searched the forum, but didn't find a good solution?

Hi Hope I'm in the right forum - could be a script question. So sorry if that's the case. I have a number field with the validation set to UNIQUE VALUE. However I do have instances where the data does not have to be unique. Specifically I need it to be OK if "CLASS II" is entered. ( Which I have entered automatically through a calculation in the number fields options, [if ( Job Type = "Class II"; "CLASS II" ; "" )] ) During a script, when the "CLASS II" is auto entered the dialog box comes up and asks if I want to allow this or not since it is supposed to be unique. Question: Is there a way to have this querie answered YES (allow)automatically in the scri…

I'm sure there a better way I could have implemented, but I'm hoping there's a function/calculation/script that can do what I want. As an example, I have a field pulling down a value list of a few dozen items, with checkboxes. Say I check 6 of these items. What I want is a second field that will list only the items from the first field that are checked (the new field also with checkboxes). Is this doable with a function or something similar?

lets say I have a small number in a field let's say $37.50. I cannot get the number to break down with the decimal point it always comes out as 3750 without the decimal. I have tried number format but cannot figure this one out, does any one have any ideas. Jimmie

I have a balance sheet with services of different types. I'm trying to create a past due function. Now there are different services provided at different times of the year. So I'm wondering if there is a way to control the field calculation? I was thinking of linking the calculation to a global field that I could change with a check box. My first attempts are simply creating a literal text link, they aren't really performing calculations to related fields. Maybe this type of connection isn't permitted within filemaker... Any idea's would be greatly appreciated. My current resolve is to create every possible situation with its own field…

O.K. I have this database that has evolved two fold since the brain storm step. I have a Class Registration table and a Student Contact table and then another table is related to the Student Contact table to keep track of every class said student takes/attends. In the table it has a "Overall Grade" field that contains Complete or Incomplete and possibly nothing if the record hasn't been adjusted yet. So in the Class Registration section I want to count how many students have a status of Complete. I've tried the case and the first non-complete it find it stops. I'm not sure how to do the if / then statements if that is in fact the best way to go. Did I…

I would like to duplicate the data in a field to a different field for a found set of records. Is there a way to do this besides copying and pasting the data one record at a time? The field needs to remain editable, and the copy/paste can effect only the found set of records.

This is actually a continuation of another thread I had (http://fmforums.com/forum/showtopic.php?tid/201932). I am creating a new post because I have a better idea of what the problem is now and it seems better to restate it. I was looking in the wrong place on the other thread. I have a calculation field that determines the distance between the zip code of an entry in our Candidate database and another zip of the user's choosing. This zip is entered in a global field. The calculation works perfectly, but searching doesn't. The first search on the calc field works fine. After that search, if you change the user chosen zip the search brings back strange results. What …

I have a dollar value field and i need it to always have decimals to the 100th place at the end of the number. How can i achieve this?

Is there any way to control the playback of Quicktime video that is stored in a container field, or a function that can capture the current timecode of a video that is currently playing? I have in mind something like a script with parameters: PlayVideo_scpt(container_field ; start_time ; end_time) where if end_time is omitted the video plays to the end, and if start_time is omitted the video begins from the beginning. I'd also like a custom function with syntax like GetTimecode(container_field) Now I should say that I already have worked out an Applescript-based solution that takes a video stored in a container field, opens it in Quicktime Player, …

Wonder if I can get some help with this? I have a database that consists of employees, a week ending date, hours worked on a particular job, and a job number. What I am trying to do, is when I do a find for a particular person, it will give me the hours worked by employee. How can I add on, the grand total number of hours from everyone, on this particular job? What I am trying to show, is their total hours, and the total hours of the job by everyone. This will enable me to get the % of their hours worked. Right now, the best I can get is it just totaling up for that particular employee only, not the total for the entire job. I can't seem to get the grand total of al…

Is it possible to eliminate AM and PM from a Calculation Field set as a Time Output. Im trying to calculate total hours from a start field and an end field and get the number i'm looking for but has an AM showing and want to eliminate that. Both start and end fields are set as time fields.

I would like to use a calculation to combine the contents of one field from every record. If the field the values are: Record 1: apple Record 2: pear Record 3: orange I want the calculated result to be "applepearorange" I can do this is a script that loops through every record, but would like to do it as a calculation if possible. I've been hunting around without success for a way of doing this.

hope this is the right section... Get (SortState) returns if records are sorted or not. is it possible to somehow return by which field is sorted and if so, whether the sort is up or down?

I have a calculated result of 1000000000 it ends up as: 1.0e+9 could someone explain this to me. 100000000 (fine) 1000000000 (re-formatter) 1000000001 (fine) 10000000000 (re-formatted) I must admit to being a little confused. I need to reformat the calculated result so 1.0e+9 is not much use to me. best Stuart

Hi All, I'm quite rusty with Filemaker and having trouble with the following: I have 2 database files. 1. ENTRANTS 2. CODES Entrants has about 1,000 records. The records have a field named CLASS. CLASS is a related field that pulls up information in the CODES Database. I want to display a count of all Classes in a different database file. The new file will be called TASTE. e.g. record 1 - CLASS 120 record 2 - CLASS 100 record 3 - CLASS 120 record 4 - CLASS 100 record 5 - CLASS 150 sub summary results yield CLASS 100 - 2 CLASS 120 - 2 CLASS 150 - 1 There should be one record in the TASTE database for each record in the CODE…

Hi all, I would like to set the title of a report based on a calculation which checks the contents of a particular field (Field A). If the Field A contains only "Apple" values in the entire found set, I would like the title calculation to read "Apples". If the Field A only contains "Orange" values in the entire found set, I would like the title to read "Oranges". And finally, if there's both Apple and Orange values in the found set, the title should read "Apples and Oranges. How could this be achieved? Any tips much appreciated!

While wrestling with a repeating calculation I found something interesting... For a repeating calculation field: Let([ rep = Get(CalculationRepetitionNumber); item = Items::Item ID]; rep ) will not return a value for any repetition but #1. Let([ rep = Get(CalculationRepetitionNumber); item = Extend(Items::Item ID)]; rep ) will resolve correctly. It seems that even if the Let variable is not used in the result, the calc will fail without Extend.

Hey everyone, I hope everyone is having a good day! I need a hand. I'm trying to get a money total on one field "total" based off of 4 different senarios. the senarios come from a tab with a drop down that has 4 options and i would like the total to calculate only certain fields based off of what was selected in the field with the drop down. example: fields that will be included in the calculations: adj gross net total overage bonus % (this is a field too) tab down selections: vs + gt bonus so what id like my total field to do is calculate different fields based on what was selected in the drop down. ex and this is the more c…

Hi everyone Can someone help me with this. If number field A = "5" field B is an auto-enter-calculation containing : field A * 5 so my result in field B is 25 and it can't change. Let's say, later, I made a mistake and want field A to = "6" now my field B won't recalculate because it's an auto-enter calculation (And I need it to stay that way). Is there a way to create a button (or something) that would trigger a recalculation of field B when I need it? In one table I Have a dozen number fields like Field A and another dozen number fields with auto-enter calculation like field B. So at the push of a button I'd like them all to recalculate.…

Hey there, I have a calculation set up to find the total of a few related records via the sum function. Here is how it works. I have a Contracts Table that is Related to a table called InKind. They are related through a unique ID number that is given to each Contract in the Contracts table. Each entry in the InKind table has a cash value. The calculation resides in the Contracts table and is as follows: Sum ( AFF_InKind::Cash Value ) However it doesn't work that way.... First Issue: The InKind table is being displayed in a portal, when I delete a portal row the TOTAL does NOT update. If I add a portal row or update the cost of one of the entries t…

Hi everyone, i've tried to do a search on here, but can't find an answer. I want to create a field that is global and shows today's date. I can get modified, and created, but I can't find a way to do it so it updates as soon as the day changes. The rest for this is i have some calculations I'd like to do, to show how many days since a service call has been logged. Thanks in advance!

I have 2 main tables, Pages and Services, with a Line Items link table that allows me to assign multiple Services to any number of pages. I have a Qty (number) field in Line Items. I also have a Tasks table, whose records are created from a portal in Services, which also have a Qty field. I want to show a portal from Pages that shows all related Tasks (which I have done) but also contain a calculation of Line Items::Qty * Tasks::Qty. The logic is that each page is assigned numerous services which are each made up of a number of tasks. A page can contain one or more of each service. The user wants to be able to add services to a page, specifying the quantity need…

I've been searching the forums like heck to no avail. Can some one point me how to Prohibit edit of a field if a value exists. I thought it was as simple as checking the Validate existing value box in the Validation Tab but unfortunatly its not as simple as that. Thanks

I populate some fields from a relational database in a second database, e.g. first name, last name. When I try to make merge fields out of them in a form letter, they read in the layout mode <>, <>. The fields print fine in the browse mode, but when I try to slide the fields together in the slide/print window, they refuse to slide. I select both of them with shift/click or a lasso; they both highlight, but when I look at the browse version, they are not slid. The are formatted to the left. Help please

Hi, I have 2 fields, date & time of an event .. I have a key that combines them both (date&time) ... we have a certain number of seats open for each timeslot for a day .. is there a way to add/summarize the total # registered for each key ? w.r.t registration, each record is a registration and multiple people can be registered by the same person .. e.g. person x has a party of 5 ... and max people for that time slot is 100 .. i just want to display the total # registered for that time slot .. e.g. for date = 2/26/2008 and time = 10:30 AM, Total attending = attendance from record 1 + attendance from record 2 + etc. for that time slot .. . i wa…

I know there's an easy way to count related records: Count(RemoteRecordID). But what if I need to count only those records, which match a criteria in a second remote field? I have Events with Positions which need to be assigned to Volunteers. I need to count all Positions which have not been assigned yet. There's a field on the Position record which states Assigend: yes/no. The information is there, but how can I integrate it into a Count calculation?

I have a calc that is looking at one field. It is a date field. The calc field will calc the days after the last date and put a return of "eligible". Everything works right except for the ones that have no date in them. My Calc Case ( Get ( CurrentDate) - (Last Walk Worked Date) > 180;"Walk Eligible";"" ) if the last walk worked field is blank then it needs to reference OriginalWalkdate field. So I get the first calc right and if the Last walk worked field is blank then it returns a blank. Is there a way for it to look at the different field if the primary field is blank. Thanks in advance. Randy

Hi, In one of my table, I have a calculation field (calcCost) and a number field (cost) which is an auto-enter calculation that simply copy the value from the calculation. "calcCost" is working perfectly. "cost" is not taking the value from calcCost cost = calcCost (Auto-enter calculation replace existing data) Can someone help me with this weird problem? Thanks

I have a field on an invoice layout which produces a calculated value using a LIST function. This field is then used as a merge field on an invoice letter. I have one annoying problem. The items in the list function are seperated by tabs (Tab symbol set to a global variable $$tab). The first item on the line is the product description which varies in character length, which in effect means I end up with something like this Firstproductdescr??:?:?? 550 0 550 secondproddescr?? 550 0 550 So can I either format the List function so that these columns line up, or is it possible to modify the text in the returned value. or is it poss…

This one eludes me I try to get the Next Month Name result through a calc. MonthName ( Get (Current Date) ) Text This one works fine for the current month but how to get the month? Thanks for your help

I have a 'product waitlist' table that holds the following fields: Record id (auto enter serial number) Creation Date (auto enter creation date) Creation Time (auto enter creation time) Customer name Product wanted (via a drop down value list) I would like to have a field called 'position' which shows the customers position on the waitlist based on what product they are waiting for- this will have to be relative to the date and time the record was created. When the customer collects the product , the record will be deleted so it needs to be able to recalc when this happens. Any ideas on how I go about this? thanks

I have a table of expenses and a report layout that summarizes them by month and category. At the end of each month is a total of all expenses in the month. I want to take those monthly totals and average them over however many months are in the found set. For instance, if I perform a find of records from 3/1/08 to 7/31/08, and display them in my report layout, I get totals for March, April, May, June, and July. But how do I get FM to automatically total those totals and know that there are 5 months in the found set and therefore divide the total by 5? It seems to me there would be some way to find unique month values in the found set and count them, but I'm not …

In order to fill out my IRS quarterly payroll form, the IRS wants to know how many employees were paid during the quarter. My current quarterly report has 11 records. It shows that I have written 11 checks for two employees (one employee received 10 checks, the other employee received 1 check). Every summary field I have tried so far gives me 11 rather than 2. Any help? Thank you.

Firstly, sorry if I have posted this on the wrong board. My question is as follows: Is it possible to create a link/alias from a FileMaker record in my database to a folder on my desktop? The reason for this is to be able to go directly from a folder which contain the clients job/artwork(and said alias) to their specific data base entry? Many thanks for any help give.

Through a web form FileMaker receives a text like this: Friday, 6 March, 2009 How to convert this into a FileMaker compatible date field like this: 3/6/2009? I am stuck with it and don't know what to do. Is there an easy method or complicated calculation available? Any help would be great. Cheers Greg

Hi Everyone, Quick Intro: I'm completely new to this forum and completely new to FM altogether. I just downloaded the trial FM10 Pro. I'm trying to help my boss/his business have better inventory management. I'm a web graphic designer. We have 6500+ products. I have already created a FM template and imported the product. Question: Is there a way to import an image path and name into the image field in our FM database? Example: C:/images/product/892818-920.jpg Currently our template (came w/ FM - Inventory) has a picture box. I'm looking for a quick way to point FM to the image folder and just upload a path and image name in the proper field.…

Hello Team, I need to compare two dates and determine which is the "newer" date. For example, I have a field called "ExitDate". I need to compare whatever the date is in ExitDate, which may be June 19, 2009, and compare it with todays date and determine which is "newer" and place the result in a field. I can't see how to create what must be an "if/then" statement of some sort. Let me know what you think. - Sez

OK..hope this is the right area. I have a text field with a bunch of text. I am trying to extract a valid US telephone number from the field. It is fairly easy with php and mysql but I can't seem to find the magic bullet for filemaker. The usual variables are XXX.XXX.XXXX OR (XXX) XXX-XXXX and so on. IE: "1971 tandem grain truck with a 20 foot box and 427 motor air brakes runs well call 402-768-3486" or "71 Chevy pickup was restored from ground up in 2002. It is a pearl blueish/purple Has a brand new rebuilt GM goodwrench 350 crate motor. It has a rebuilt turbo 350 Transmission. It has a total of 7 inches of lift with 35x12.50x15 super swampers. I re…

I have a calculation where the result is 1.5. I need the value in my field to only be .5. How do I truncate or format the field so it only recognized the decimal point value.

I have, say 500 Records. Each record is assigned to a userID. There are 10 unique userIDs... I am sorting the sub-summary by UserID... so there are 10 summarys. Is there a way for me to count the sub-summarys... and therefore count the # of unique user id's? Thanks, Campbell

I may be just having a mental block. I have 5 records Say: Grapes 15 Pears 10 Grapes 20 Pears 5 Apples 30 I am producing a report. The report has a SUB-SUMMARY sorted by fruit type. I also have a field called fruit_count which sums the types (Grapes = 3, Pears = 2, Apples = 1) So, my sub summary goes: Grapes 2 Pears 2 Apples 1 Ok. here is the question. I have 5 total records. I want to divide the fruit_count by the total records (5). Can someone tell me how to do that INSIDE the SUB-SUMMARY section of the report? Thanks, Campbell

I have strings of text that read as; 006B_A1_001_C.mov 006ZZ_A1a_001_A.mov I need to remove the last 6 letters from the right so that the calculation reads; 006B_A1_001 006ZZ_A1a_001 Anyone?

Hi, I have a field, that contains multiple lines (i.e. paragraphs)... Now I want to separate each line into another field, field content: paragraph1 paragraph2 paragraph3 now I want: field1 content: paragraph1 field2 content: paragraph2 field3 content: paragraph3 (The number of paragraphs varies in every database entry) How could i achieve this? Cheers Lars

I have a field in a single table that has the path to an image. When I run a report I want the image to show. How can I do this? example would be: field--imagepath---> /images/coupler.jpg

In my schedule table I have a field called 'date' and a field called 'job#.' There are multiple records with the same job# and different dates. I am looking for a calc called 'next_date' to only show the next date beyond the current date for each job# occurrence. Thanks in advance

I found in the forums a way to enter time as example 1300 and fmp changes it to the proper time format of 13:00 and what I tried was Replace ( Right ( "0000" & Timefield ; 4 ) ; 3 ; 0 ; ":" )but when I put it into my db it says it cannot find "timefield";4). I am wondering if this is because I am using FMP 6 although I can't really see why it shouldn't work. Any help on this? Thank you

I have the following situation : For each record in the database, I have a repeating field "values" (up to 20 repetitions, 10 currently used), which shows the market value of this item from year to year. If, in a given year, the value has not changed, the repetition is blank, and the function Last is used to derive a value for use when totalling values across all records for the latest year. So, a given record might look like this (where "Year" is simply a layout header text field, showing a list of years, while "Value" shows the contents of each repetition of the field "values") Year '99...'00...'01...'02...'03...'04...'05...'06...'07....'08 Value …

Ok, here it is in the simplest form I can present it. I have a repeating field with 2 repetitions. The first repetition is a variable value selected from a drop down. The second repetition contains a global value. I need this second value to change based on the value in another field. Basically something like this (this is just to illustrate, I realize the calculation is invalid): Field 1 [repeating - 2 values] Field 2 If(Field 2 = "text"; Field 1 [rep 2] = "value 1"; Field 1 [rep 2] = "value 2") I have tried all kinds of solutions using the Extend function. In fact I can just about get what I need if I make Field 1 a calculation field, but as I need to…

When I use the text tool I can show a specific field like this: <> but I'd like to know if it's possible to show the get(accountname) with the text tool ? If yes, how ? Thx

Hi, I wonder if I can calculate a "shortcut" in one of my calc fields. Currently, I'm using this expression to get certain field values, based on the field Category: Case ( Category = "Person" ; BAS_PER::LemmaPER ; Category = "Organization" ; BAS_ORG::LemmaORG ; Category = "Geographic" ; BAS_GEO::LemmaGEO ; ... ) Instead of using the Case function I'd rather calculate the name of the result field like "BAS_" & Left ( Category ; 3 ) & "::Lemma" & Left ( Category ; 3 ) (I'm ignoring UpperCase here to make the function easier to read.) The result I get unfortunately is [color:red]"BAS_GEO::LemmaGEO[color:r…

Did you know that there are 53 weeks in a year and that a week can be 1 day long... Sat 30 Dec 2006 = Week 52 Sun 31 Dec 2006 = Week 53 Mon 1 Jan 2007 = Week 1 I know its right but there goes the week view on my calendar solution. :

Hello Here's a beginner question. What is the best way to call a data from a list? I must estimate a time of production: table 1: prod_data (number of linear feet) table 2: variables (fields: ID, name, variable (time)) table 3: estimated time What I must do is: prod_data*variable = estimated time My problem is how to call (calculation) the variable from the list? I have never done that before. For example, I need the variable data from ID number 5. At First, I created a field for every variables and just make a case calculation to retrieve it. But I don't think it's the best way. In another part of the database I will have to deal with m…

How many decimal points can I store in a number field. Can I store a number in the following format for example without any serious repercussions 1.12.34.123.5749 I wont be using them for calculations, however I will be using them for sorts (as I am trying to create a folder tree, each decimal point being the parent of this record, if that makes sense). Thanks Jalz

I'm trying to build a "drill-down" system in FileMaker and I've run into a wall. Table "Fonts" has a portal pointing to table "Keywords", which in turn has a field called "Keyword Count" that, via a self-assigned relationship, counts how many times a given keyword is used. When I look at my layout in "Fonts" I see the keyword (e.g., "industrial") followed by a number; the number indicates how many fonts have this keyword attached to it. Any given font can have any number of keywords. That part works fine. I wrote a script that, when you click on a keyword in the "Fonts" layout, omits any fonts that don't have that keyword attached. That part also works fine. …

Not sure if this is the correct forum but here goes. I have a number of records which contain a calculated text field. I want to be able to perform a find on and then write just this one text field to a text file in the order that it appears in the database.

I have a message forum that is driven by FM. I’d like to create a calculation to automatically cull posts and topics that contain certain words; sort of automatic censoring to make it easier to moderate the boards from flamers and people using profanity, etc. So, if my source field is MessageBody and my result field is MessageBody_c how might be the most efficient way to have MessageBody_c be the result of MessageBody if no banned_words are in the MessageBody string or conversely if banned words are present then MessageBody_c would be null (empty or perhaps present a message indicating that the context is prohibited). The banned words would be stored in a global ‘b…

I am using an aggregate function (sum) in a field linked to a related database. The field in the other database is a calculation field... Round ( Meters–Owner * SIMs::BonusFactor perMtr ; 0 ) The problem is that when I change the value in the field, "SIMs::BonusFactor perMtr" the aggregate field does not update. As this data is important to projecting and estimating values, it needs to be current and update if any other values change. Is there a way to make sure that the fields update ASAP ? Thanks

Good day. I've been trying to use a global variable ($$GVar) in a field definition calculation to no avail. I thought this was possible but might be confusing in with the Let function. When I test in Data Viewer, the calc works, but when it's in the field definition calc, nothing happens. Just need some verification. Thanks, M

Hello Team, I'm pulling data from a student information system that our district uses to take attendance. I need to be able to calculate the number of hour a student spends in a tutorial class that may be once a week, twice a week, or even three times a week. The problem is, the entry and exit dates for the class could potentially be unique for each student. For example one student could be in a class that is held every Tuesday for an hour but entered the class on September 9, and his/her last class was November 25. Another student in the same class could have started later, or earlier and finished later or earlier. Ultimately I just need the correct num…

My first post here. Apologize if made something wrong. Looking for a solution in FM similar to this in numbers. I have tried If (Table 1::Client; Client; Sum (Table 1::Paid)) but it did not work. Any suggestions or helpful links?

I did not how how I should go about searching this topic. Well I need to put a signature container into a layout. Certain records need to have a signature for Alex, Others need one for bruce, and another for Joe. I have all three in a jpg format. I was thinking of placing all three in separate container fields in a table and layout. Now I need to make a function (either a radio button or drop down menu for the names) that allows me to choose their name for the record on our main layout. Then places their sig on the layout that needs to be printed or PDF'ed. Anyone have any ideas or a link to a topic that might have overlooked? Thank you Travis

Newby, I am reading FileMaker Pro 9 Bible. Have created the test Data Base and some fields do not appear in tables. Suggestions appreciated.

good software that dose flow diagram for DB.