Calculation Engine (Define Fields)

I knwo I am overlooking something but I just cant seem to figure out what it is to make what I want work. In each record I have a field called Cost and then in my footer I have field called Total spent. I want total spent to be the sum of every record's Cost field. How do I do this?

I'm new to the forums, and I am seeking help because I've had this issue that I can't figure out. To simplify, let's say I have three tables: 1- Orders 2- Line items in those orders 3- Products The line items contain the quantity of an item ordered. The products database calculates how many items are left in inventory by taking the starting inventory and deducting the sum of the quantities for the line items, that all works fine. However, this is the tricky part. Say item 1234 has 4 units in stock Order 1- has qty 1 unit Order 2- has qty 2 units Order 3- has qty 2 units the system correctly calculates that there is -1 in st…

Hello, Help please I think with "if then" I am using portals and would like to have a field populated with a value when a selection is made in another field. For instance: When grade 3 is selected then the reading average is populated with a predetermined value in the reading average field. Then if grade 5 is selected then a reading average is populated in the reading average field. Thanks for your help in advance DH

Hi Folks, I need your help. I have designed a database to study Chinese and now want to include sounds. To test for sound, I created a container field, clicked on this in brouse mode, and selected insert from Quicktime [which I have just bought]. The sound appears OK and there is the playback bar at bottom of container field, and when I click on play the sound is played. It plays fine for a while. But whether I do this on laptop with vista or desktop with XP, fairly soon everthing crashes and I cannot open the file again. The sound I am testing is fairly large, but it would have to be under the limit of 4gb's. I have tried different formats, the latest WAV. …

I'm having the hardest time getting this to work. Could be due to the lack of sleep this week, but anyway... I'm trying to use a grand total summary result in a calculation. I have a report with a few subsummaries and a trailing grand summary. Everything is working great up to that point. I have an earnings summary that is a running total and restarts when sorted by income type. This is both in the sub and trailing grand summaries. Both instances work as advertised. What I now need to do is take the grand total earning summary field and use the result in a basic calculation. Here's what I need to do, I'm using () to illustrate where the field is o…

I am using the get function get(PrivilegeSetName) but it always returns the [Full Access] as a result regardless of who is logged on to use the database. I have most of my users on a Privilede Set called [user Set]. Has anyone else had this problem or am I missing something obvious.

For some reason my mind is slipping.. I have an inventory database with each record identifying an item and several fields with qty of that item in different locations. I have a second database where people input changes to the stock locations (this tracks the inputter by records and their actions). I want to input numbers in the second database and have it add to the field in the first without creating a new record there. Any help please?

I am new to fm9 and databases in general. can do anything in excel. not so here. could someone post the syntax for using a date range in a form to have a fileds total between the range? i want to show sales within a date range and then total for the month. thanks bucky k

Is it possible to put an autoenter value in the second repetition of a field. Thanks Jalz

I would like to get a formula to find the entire last line of a paragraph. ( I mean the very last sentence of the entire field ) AND [color:red]I would like to find the very next [color:red]word to a [color:green]specific [color:yellow]word in a paragraph. In the last sentence , for example: find the word , immediately to the right of the word 'specific" which would be "in" I need to be able to do both these things with text in a field named "alltext" Thanks Dave

Hi I'm trying to create a lye calculator. Each oil has a certain SAP value (Aloe Butter NaOH 0.1340 or KOH 0.1876 depends what choosen). So the calculation should access the chosen oils in a portal and calculate the individual SAP values of each Oils amount. The problem I cant figure out is how can I calculate each oil in a portal and make a calculation out of this. The file explains more. Its hard for me to explain. Here a site that uses the same method. Link Thanks Test.fp7.zip

I am using the DoScript plug-in by myFMbutler to execute a script based on the current time. The following calcs result in the following returns in Filemaker 8 Advanced. ## If the current time is 12:30 AM ## If ( Get ( CurrentTime ) > "12:00:00 AM"; "yes"; "no") RETURNS - yes If ( Get ( CurrentTime ) > "1:00:00 AM"; "yes"; "no") RETURNS - yes If ( Get ( CurrentTime ) > "2:00:00 AM"; "yes"; "no") RETURNS - no If ( Get ( CurrentTime ) > "3:00:00 AM"; "yes"; "no") RETURNS - no If ( Get ( CurrentTime ) > "4:00:00 AM"; "yes"; "no") RETURNS - no If ( Get ( CurrentTime ) > "5:00:00 AM"; "yes"; "no") RETURNS - no If ( Get ( CurrentTim…

I don't want the user to be able to modify a field after the record is commited. I know I've seen this before but I can't remember how it's done.

Does anyone know of a calculation that when any number is entered, the result is the next highest EVEN whole number? Thanks!

Is there a way to turn: 04142008 (when entered) and it turns into 04/14/2008 automatically. I know you can do this in 'excel' but no idea how to do this in filemaker

Hey folks I can't seem to find an answer for this question so perhaps a: it is very obvious, b: I'm searching for the wrong terms, or c: nobody really cares as long as it works : I have a container field in a runtime DB where people can attach resumes in PDF format. But where are these attachments located after they are attached? Does Filemaker just assimilate them into the database somehow or does the file get put into an invisible folder somewhere? Thanks

My solution has invoice numbers with five digits followed by a letter. I'd like to let my users preview what the subsequent invoice number in a particular series would be, before it's created (why? Long Story). In other words, if invoices 20000a, 20000b, and 20000c exist, then I want a tooltip to show that the next invoice to be created in the 20000 series would be 20000d. Auto-enter serial numbers are not being used, btw. I'm working on a formula that's getting me close: Let ( [ trailingLetter = Right ( invoiceNumber ; 1 ) ; trailingLetterAsNumber = Position ( "abcdefghijklmnopqrstuvwxyz" ; trailingLetter ; 1 ; 1 ) ; traili…

I was having trouble with a custom function. Discovered that you can not pass a value from a repeated field to a custom function. I had to set a non-repeated field value to the value I wanted to pass and then use it as the custom function parameter. Is this a bug in FM? Seems you shouldn't have to go to through the extra step of 'set field' to use a custom function

Hopefully someone will know how to do this. So, what im trying to do know if the following. Im having users create a new record and assign a customer name. what i want to do is after they assign a customer name to the field i don't want them to have access or be able to change it. further more, i have 90 records with the view "Form" and i want to disable the scroll Botton in the mouse.. is there a way to do this??? Thanks

If I have a text field that contains a particular state. How do i return a list of all states that are being used for the found records.

How should I go about this? I have a field that displays a popup menu 5%, 6%, 7%....10%. Then I have a calculation field that should calculate the % of a total amount. For example "121.1 - 5%". I added in the calculation field: "Total * "0.0" & popupmenue (5%)" the result is wrong. I did not show the % sign so I thought this might work but it does not. Also it seems like this is not the way to do a calc like that. Any help please!

Is it possible to program FM to record a date when a new record is created and another date if a change is made to a record. I am not yet very familiar with Filemaker, so please forgive me if I have not posted in the proper place or have not made myself clear. Any direction would be most appreciated.

I'm trying to script a case statement that compares the value of a field to a set of numeric ranges to get a result. I am not having much luck with the syntax to define a range. I have tried the following so far: CMSinglePhCalc ≤ "404" and CMSinglePhCalc > "320.5" ; "24" and CMSinglePhCalc = "404...320.5" ; "24" Any ideas? Thanks, Greg

Hello I am using the Send Mail function to try to generate an email with multiple addressees in the "To" field. However, whether I use hard-coded email addresses or variables, the Calculation engine just seems to pick up the last email address in the Case statement and not all of them. Please see below example. Any ideas? Paul Case ( $recordCount = "1"; "[email protected]"; $recordCount = "2"; "[email protected]; " & "[email protected]"; $recordCount = "3"; "[email protected]; " & "[email protected]; " & "[email protected]" )

After having been shown how to automatically embed referenced pix, I thought I would try and do the reverse; reconnect embedded pix to source and save as a reference. Quickly, I have realised that it involves searching the HD and therefore may need an applescript (I could not find a FM script function that searches the HD). Does anyone have any ideas how it could be done (on the proviso that there is only one copy of the file being searched)? Thank you

Here I Have a simple database, with a problem I am trying to resolve, this should be straightforward, but the more I think about it the more my head gets scrambled – Using FM 8.5 The database is this : I fit Fire Alarms to premises, and they are serviced every 3 months after the installation date, until 12 months has elapsed, the customer then has the option to renew for a further 12 months, again with 3 month servicing and so on The database I have created is the following Customer Name Date of contract Start is a Date field And 6 calculated fields based on the contract start date Field 1 (3 Month Service Date) is Contract start date + 90 Field 2…

I have a Radio Button that displays oz and gram. How can I format the fields from oz to gram when switched? Is it a calculation in the field itself? Thanks

I have a database with a considerable number of images. The file has now grown to almost 3GB. Many of the images imported were larger than necessary. Is there a way to determine the size of individual records and/or find large records so I can resize those images and significantly reduce the file size? Thanks!

I have a small problem that I'm sure someone has solved before, so if this has already been discussed, feel free to point to the link: I have a database with first and last name fields into which I often have to import data with the full name as a single field, so I have a "split first-last name" script that does this (full name is in the first name field at start of script): Replace (lastname, RightWords ( firstname ; 1 )) Replace (firstname, LeftWords ( firstname ; WordCount ( firstname ) - 1 ) The problem is that if there is a middle initial, the period after the initial disappears, so "John B. Doe" becomes "John B Doe". How can I hang on to those peri…

Hello, I have several fields that i am able to add up for a total, such as principal + intrest = x amount, but i am having a hard time trying to figure out a payoff associated with dates. IE client made payment on 03/07/2008 and has $1,000 in the principal field and lets say a payment of $100 for next months payment due, but lets say they payoff early by a few days, say 28 days i have a field that divides by 30 to get the daily rate but would like to be able to put in a date in a field that would click on a tab and have it show the exact payoff for whatever date they want to payoff. I have tried, Current pricipal + Interest Per Day+ Most recent payment date?? and todays…

I am producing reports under different circumstances and what I would like to do is display in the header of the report the count of the records not in the report. I have tried using calculation fields. For instance if all my records have "yes", "no" or maybe in an attendance field and I am reporting on the "yes's" I would like to be able to show in the header the total of all the records, the no's and maybes and those that were blank. Any suggestions please.

in my database, I have 2 fields. 1 is a number, and the other a calc. Lets say in the number field i enter 21. In the calc. field, it will say "twenty one dollars". But if I enter in 100. It will say "one hundred and dollars". I am trying to get it to say "one hundred dollars" , or maybe even "One hundred and zero dollars" The Calc. I am using is below, NumberField is the field it is reading the numbers from. Any help? Choose(Int(Mod(NumberField;10^12) / 10^11); ""; "One Hundred "; "Two Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six Hundred "; "Seven Hundred "; "Eight Hundred "; "Nine Hundred ") & Case(Int(Mod(NumberField;10^12) / 10^1…

Hi all, I am looking for and idea to check an expression syntax off-context. I've been very disapointed by the fact that EvaluationError gives priority to runtime errors (missing field...) over syntax errors, and even more disapointed by isValidExpression which just seems to be a sub-EvaluationError (unlike its documentation, it is also sensitive to runtime errors) Still looking for an idea...

Hi, I have a solution in which I need to put the value in a portal into a repeating field. I have a portal with 5 records. 3 of which have a number from 1-10. So record 1 have value 1. It need to go into a repeating field in the 1 slot. Related record 2 have a value of 3 so it need to go in the 3 slot of the repeating fields. Get the picture. I am trying different variations of getnthrecord, get(calcuationrepeatnumber) case and extend calcs, but I am not getting all the parts in the right place. Seraph_nyc

How can I format a field that when I enter like 500 into a field it automatically adds a g to the end, so the result is 500g. I tried the Calculated Value (""+g) but that wont work. How can I do it? Thanks

I'm really struggling with how to even ask this question it makes sense in my head but as I type I feel I can't express this clearly but its a simple idea. I need to calculate/lookup/assign the next number/value based on the number related family members. I'd like this to happen when the family number is assigned. So the first person in family 001 would be 01 2nd person in family 001 would be 02 3rd person in family 001 would be 03 and so on. I've stared by trying this but it doesn't work because it easily broken. Let ($Family_Count = Count ( Contacts Family Portal::Family_ID ); $Family_Count + 1 ) This doesn't work well be cau…

I am trying to move my database from Windows to Mac to create runtime solutions for both. When I open my database on a Mac can't access the Scriptmaker menu or change any of the fields. It behaves rather oddly. It works perfectly on Windows. Is there something I am doing wrong?

The name of the host computer can be obtained with... [color:red]Get(HostName) How do other users get the name of a client computer connected to the FileMaker Network? Thanks for your help.

Hi Guys can you help me with a calculation base on my excel formula. I need to get the difference between the data that i input yesterday from the data today. Sample of my excel file. [Date] [RCBC] [cDifference] Thanks

In various places there is outstanding available custom functions that eliminate duplicate values in a list of records. I am trying to do something a little different, leaving me a bit stymied. I would like to use a custom function to loop through a found set of records and detect a duplicate value in a text field. The result of the custom function would be a listing of those values that are duplicate. If There are no duplicates the result would be blank. Lets say I have a found set of 5 records Apple Pear peach Pear Grape I am looking for a result that shows: NotADup Pear NotADup Pear NotADup I want the custom function to …

Greetings, Can the result of a calculation field be defined as unique? I have a calculation that combines a unique identifier with a suffix and I would like the result to be unique so I can be warned if I have created the resulting "art number" before. It is kind of a long story why I need to do it this way but it is necessary. Also, I know I could creat another field that would be autopopulatd based on the result of the calculation (and I could define that as unique) but I was wondering if I am missing something basic here. thanks, Steve

I have a list of all the cities in the usa. I need to be able to replace he space between the words with a dash "-". Sometimes the city is 1,2 or 3 words. Can anyone help?

Hi, first post here... I'm a composer and I'm compiling a cuesheet. It gives information about when a piece of music begins in the show, it's title, composer, name etc... The way I have it set up right now is that every cue (piece of music) has it's own record. Within that record, one field is for music cue duration. My last project has 52 music cues and I need to add up those time durations from all the records in the file (52 records). The result field should be in hours, minutes & seconds. Can someone walk me through this? I'm a newbie... I've only created some very basic layouts before with only simple calculations. Thanks in advance! Da…

I would like to use the Length (text) function to show a user how many characters they are entering into a textfield.The only problem is the numerical value only shows when the user has left the text input field.Is there a way of having it count automatically whilst staying in the text input field. I remember trying to do something like this before and it involved a modification time of the text field, but I can't remember much else! Any help would be appreciated

The toughest questions are the ones that are tough to frame in your mind, I have been trying to figure out how to ask this question for a couple of days. Some background... We have a database with 3 tables: Blogs, Blog Comments and Blog Stats. The first table, Blogs, has a ID # field that is tied (related) to a key field in the Blog Comments table. This is what ties the correct comments to the corresponding blog, this is working well. The 3rd table, Blog Stats is tied to the Blogs table via the blog author's name. It has 4 fields that show statistics. A record is created for each Blog author, this is not a problem as there are few of them, less than a 100. …

Can you recommend an easy way to add a number of days (say 10 days) to the current date to calculate a due date ? Thanks

I am relatively new to FileMaker and have tried to extensively search this issue before posting and could not find an answer! I have a database with 4 tables, People, Companies, Job Orders and Events. I have 2 fields in People, "First Name" and "Last Name" and another calculated field called "Full Name" which is set up as "last name, first name". I am trying to set up a dropdown list to be used in the other tables which will incorporate the "Full Name" setup. I am only able to get one name in the dropdown list even though there are lots of other names in the People table. What am I missing here? Thanks in advance for your help! Alan

I run salon/spa where clients need reservation reminders. On a given DATE, a CLIENT has a TICKET with multiple SERVICES and multiple STARTTIMES Goal: I want to be able to select a DATE, and have the found set show the first STARTTIME for a given TICKET. I will then send a reminder email for that reservation time. Right now I can only get all services and all start times to show, so I have to manually go in and select the record with the first start time. Can anyone help (I am a newby, so please be specific). Thanks

Hi, I have an interesting problem that I can solve myself but it seems to be taking too may variables and manipulations that must be easier. I have 4 global variables within a record, let's call them V1 V2 V3 and V4. Is there any way to sort these variables Lowest to highest? For instance, I have numbers in each variable such as v1=25 v2 = 65, v3=75 and v4=35. Is there a way to sort these variables into positions lowest to highest? Maybe set 4 new Global variables with the sorted value like S1=25 S2=35 S3=65 and S4=75? I can get the lowest and the highest with the Min and Max functions but I can't seem to figure out the other two middle ones. Any thoughts?…

I'm working on a Training (Certificates) Database. Employees take a test, and every year have to retake the test for re-certification. Table Classes - Class Number - Class Name Table Employee - Employee Number - Employee Name Table Training Records (as a join table) - Class Number - Number - Employee Number - Number - Test Date - Date - Results - Text - Pass / fail - Recert Date - Calculation - Test Date +365days - Recert Date Last - Summary - Maximum of Recert Date From this info, I have to create reports for: Past Due - where Recert Date Last < Current Date I tried to do find mode on the Recert Date Last field, but yo…

I'm creating a database for storing shipping/receiving records. The information stored in the various fields will be scanned from a product's barcode label. The barcode labels contain part number, quantity, supplier code, etc. Each of these barcodes has a unique prefix such as "P" for part number, "Q" for quantity, etc. Is there a way to create a single field to scan the barcode and have filemaker copy the information to the proper field based on this unique prefix? If possible, I'd like to remove the prefix and just copy the data. For example, scanning Q100 will result in 100 copied to the quantity field. Thanks

Hi, I have a database with two accounts. I have user A for one account, he can print documents with logo A and user B for the other account, and he prints documents with logo B. What I need is a container field? How can I set this up?

I have a 'Follow Up Date' in my contacts table as well as in my 'Call Data' table. I use the Follow Up Date (FU_Date) for my Follow Up script. It determines what records show in my layout. I use this if, for instance, I don't get to all my calls within a given period. By resetting the FU_Date I can have the record continue showing up until completed. On the other hand if I call a contact I set my 'Do When' field as the follow up date. I tried to create a calc where the 'FU_Date' would set to either the greater of the 'Do When' Date or the 'FU_Date.' See below: Problem: It will only reset the 'FU_Date' when I physically go into that field and attem…

Hi, I'm trying to generate a report that's reporting a value along with some statistics broken out first by the day of the week, and then by the meal of the day. The table has two relevant summary fields (average and std. dev.) And I have two calc. fields that calculate the relative standard deviation for each of the two break fields. The calculation for meal type looks like this: GetSummary ( std_dev ; type )/GetSummary ( average ; type ) The calculation for grand total looks like this: GetSummary ( std_dev; std_dev )/GetSummary ( average;average ) But the calculation for day of the week doesn't work: GetSummary ( std_dev ; Days::dayOf…

Hi Hope someone can help with this problem. Running FP6. I run a large junior golf tournament at various venues. One file holds player details, their names, scores etc. A second file is used solely to calculate the CSS (Competition Scratch Score). It is this file that is causing me problems. There is only one layout with 16 records, one for each venue. I input the number of players in two different ability categories. One field then calculates the percentage of each category in relation to the total number to the nearest 10%. At the end of the tournament, the main database gives me the number of players who have made a"qualifying score". This number is t…

I though this would be a breeze, but it is driving me batty. I need to change a layout between the hours of midnight and midday. In other words, if the current time is between midnight and midday, go to layout A, otherwise go to layout B. This is activated by a button, so I don't need any scheduling plug-ins or anything like that. My script currently reads: If(Get ( CurrentTime ) ≥ "00:00:01" and Get ( CurrentTime ) ≤ "11:59:00" then go to layout A Else go to layout B. So my machine time is 14:26 - when I run the script, it goes to layout A. When I look at Get(currentTime) in DataViewer, it shows 3:26PM - so maybe this ha…

Hello everyone, I used to be a member of some FM forums a few years ago and I'm stuck / looking for some help. I have just started a small recruitment agency specialising in Architecture and I'd like to be able to use the postcode information to the best of Filemaker's ability. Has anyone created a postcode radius search calculation that can return records say within 10 / 20 / 50 miles of a certain postcode ? I've downloaded several thousand postcodes with their longtitude/latitudes coordinates, but at this point I'm stuck. What I'd ideally like to achieve would be to perform a search, say 20 miles from "HU17 8WD" and for filemaker to return all t…

The problem is you cannot reference non-global fields from an unrelated table. I want to calculate, whenever used, the current day of the year. Every reference number in my system uses the last 2 digits of the year, plus the day of the year "dash" serial ID. My calc field for 3 digit days of year..... Right ( Year ( Get(CurrentDate) ) ; 2 ) & Globals::DayOfYearCalc &"-" & ( RightValues ( SalesOrders::SO_ID ; 3 )) The DayOfYearCalc is ... If (DayOfYear (Get(CurrentDate)) < 100; 0 & DayOfYear (Get(CurrentDate)); DayOfYear (Get(CurrentDate))) The global DayOfYearCalc calculation field does not calculate if it is set as a global …

I am having trouble with the below calculation: Case ( Month ( Get ( CurrentDate ) ) = 1 or Month ( Get ( CurrentDate ) ) = 2 ; (Year ( Get ( CurrentDate ) ) - 1) ; Year ( Get (CurrentDate ) ) ) In the data view it works correctly. The problem I am having is that today the value did not update to 2008 but rather stayed at 2007. This is a global calculation field used in a multi-predicate relationship. Any ideas? Thanks, Drew

Hello all, I need to copy/paste single contact record info out of filemaker and into the mac clipboard. To do this I've created a calc field to concatenate the various fields into an organized text string with line breaks. I have a little script to put the contents of said calc field into the clipboard. So far so good. Here's the thing I can't figure out: related fields - specifically tel numbers and the tel number tags stored remotely and called in by a contact ID number relating the files to each other. How can I get all the related records (list of telephone numbers with tags) into my calc field for pasting? Thanks so much for your he…

I have an application developed with FM7 and the client would like to automatically calculate (and display) the value in field B when they tab out of field A. I'm only an occasional user of FM so I do not know the best way to do it. In FM7 there is a button beside field A which invokes the calculation via a script. I've now put the calculation into the database table definition but that still doesn't change the displayed value until the record is saved. Is there any new way of doing this in FM9?

Hi, I'm a complete newbie so please forgive me if there's an obvious answer to this. I've tried and failed to find an answer so far. I have a table containing seven years invoice totals with their dates and customer IDs. I would like to clear that table down so that it only has the last two years data by making up a new table linked by the customer ID and with fields for the total sales for each old financial year. (Those run from 01/10/200* - 30/09/200* and they're in UK format). Can anybody suggest a calculation which will produce a total for each year and therefore populate the fields in the new table?

I want a field that displays a list of all the events a contact has participated in. The tables are Contact Participants Events I know I can show these in a portal, but I'd like to list them in a single field in the Contact Table. Is this posible? What's the calculation for this field?

How can I add a character before and after an existing text-string? I have a field that contains 1001 and when I print it out I would like to change it to *1001* automatically as part of the print script. I tried to use "Set Field" to "*tablename:fieldname*", but all I got as tablename when printing. So what's the correct way of doing it? Thanks

I want to determine the # of items selected in a checkbox set and adjust the background color of the label when it falls within a certain range Lable(max 2) []option1 []option2 []option3 []option4 for instance I want the label's background to be red when nothing is selected And when at least 1 is selected but not greater than 2 selected - I want the background green

I am new to FM having worked with Access. I have searched the forum for an answer to this question without success and I apologize if I have done that incorrectly. I am working with an event planner where we have a number of available rooms some of which can accommodate more than one. I have a sub summary field that counts the no allocated to each room. I now want to count the number of sub summaries as this will be the number of rooms used. Can anyone help please. Mitch

Hi Guys, I'm a total filemaker beginner with some very minor programming experience. So I am designing a database for my psychology lab where workers can input the birthdates of subjects and their age in months and years will automatically be calculated into a separate field. Right now I have it set up so that the value for the age field is calculated using: Int((Status(CurrentDate) - birthdate) / 365.25) for years and Int((Status(CurrentDate) - birthdate) / 12) for months My question is how to get these fields to autoupdate once they change. For example, if I create a new record for a subject that is 9 months old, I don't believe that right …

I have a table with invoices and a field with the outstanding balance for each invoice. I need a field (calculation) a sort of 'customerflag', when the balance on 3 successive invoices of a given customer exceeds a given amount. I found a way to have it with a script (search/found set/loop/flag), but I wonder if there is a way without user interaction (no button click to activate script) TIA

I have a database for schools where we track GPA for each semester the kid has been in school. I want to tally (for a Risk Factor field) if the youth has less than a 2.0 then that would indicate ONE point and so on - for 8 semesters. Here is my calculation: Sum (Case ( s_0405DecGPA<2 ; 1 ); Case ( s_0405MayGPA<2; 1 ); Case (s_0506DecGPA<2; 1 ); Case (s_0506MayGPA<2; 1 ); Case (s_0607DecGPA<2; 1 ); Case (s_0607MayGPA<2; 1 ); Case (s_0708DecGPA<2; 1 ); Case (s_0708MayGPA<2; 1 ); ) My problem is that if a field is blank, FM thinks (of course) that that is less than two as all fields may or may not be filled in. Wha…

FM 8.5. I use calculation fields to apply background colors to fields that I want to highlight. The colors are stored in a global repeating field. A typical formula is: Color1=GetRepitition(ColorContainerField,Case(Flag1=1,0,Flag1=0,2)) Flag1 is set by script. The colors compute ok, but they do not appear unless I mouse click on the record outside a field. I’ve added the Refresh Window script step at the end of the script, but this has no effect. Any suggestions on how I can eliminate the need to click with the mouse?

Hi I'm trying to create a calculation field that will give me the LAST Occurrence of a portal. I have two databases: (1)Products; (2) Invoices (and within, another Invoice Line Items Table). When I look at a Product, I want to have a field that gives me the last date it was bought. I would like to do this with a calculation rather than setting up another portal table showing just one row on a descending sort. Does anyone have suggestions? Thanks so much!

It seems you can accomplish whatever you want in a Lookup with an Auto Enter Calc, with the exception of partial string match field (though that can be accomplished with a new relationship based on an exploding key). And reportedly, Lookups are much slower in a large solution. However, you can do a lot more with a Calc than a Lookup. So, when would you use a Lookup instead of an Auto-enter Calculation?

I have an item, line items and sales tables. Everything works nicely and exactly as it should. They are related as follows: Item::ItemID = LineItems::ItemID LineItems::PO = Sales::PO In Item table I have a field that sums the Quantity from LineItems and gives me the total quantity for the item over all Sales regardless of PO. So if PO 1 sells 10 of item x and PO 2 sells 90 of item X when I look at item X in Item table I will see 100 of item X sold. The problem: Item X will eventually be discontinued. After it is discontinued I need to reuse its code for a new item. When I create a new item with the same code it shows 100 sold - how do I avo…

Hi, I have a portal with related records, each one with a "description" field. I would like the "description" field of the first record to be auto-entered in a field of the partent table ("description_child"). I have tried by using the "Evaluate" function, and the "child::description" field as a trigger, but it doesn't work ... This is what I have tried (field defined as text, "auto-enter calculation") Evaluate( Lookup(Child::description); Child::description ) or Evaluate( GetField(Child::description); Child::description ) I guess that my question is if the "Evaluate" function can use a related child record as a trigger whe…

I have a field called "State Withholding Tax". In it I need it to look at "Gross_f" and if it is between 0 and 30, it will show 0.00. If it is between 30 and 85, it will show 1.00. If it is between 85 and 140, it will show 2.00. And finally if it is between 140 and 160, it will show 3.00. I tried this Case ( Gross_F = 0 & < 30 ; 0.00 ; Gross_F > 30 & < 85 ; 1.00 ; Gross_F > 85 & < 140 ; 2.00 ) But it doesn't work. Any advice. Thanks

I know this is probably simple, but I just can’t get my head around it. I have a table (A) which is related to another table (. A single record in (A) may be related to many records in (: Table B has 2 field, (B::Status and B:Type) B::Status’ will contain either “Active” or “Complete”. And B:Type will contain either “Old” or “New” I need a calculated text field in table “A” return “Yes” if [color:purple]any of the multiple related records in “B” have [color:purple]both “Status=Active” and “Type = New” I hope that makes sense.

I Will try in my better English to explain myself. I did a pdf file as help to the application, I will like the user to click on the help button and read the pdf file and return to the application. Any sugestion will be appreciated Rogelio Control_de_Calificaciones_Ver_1.0.pdf

What I'd like to do is to show on my host computer where each of the client computers are in the database? (Which file and which record). Something like a Get() function that returned the current displayed record number? I've tried Get(RecordID) but thus only returns the value for the file containing this calculation field. What I want is the value for the record in the file that is currently displayed or accessed? Obviously this could be done with setfields, but these are not dynamic and leaving the record in some unexpected way would leave the wrong value in the setfield. Thanks.

I have a client whose file crashes when they input a particular value into one of the fields. I surmise that this is a data corruption issue, since I can load other datasets and input the offending value, and I can input any other value in the problem file without crashing. The database is a subscription management system, and the table in question tracks specific deliveries. I have a linking field in this table to connect a specific delivery to a payment. This PayID field is a number field which is filled in with a Payment record ID via script. I use 3 special values, -2, -1, and 0 to represent respectively, Payment Unknown, No Charge, and Unpaid deliveries. All is…

Running into a bloat issue using FMPv9.3 and linking pdfs to records using a container field. Does it make sense to see a MBs worth of space being used to just link a pdf to a container field? And, what if a file is hosted? It seems the opening and closing helps recover some of the space that was used to create the link. But if the file is being served, there is no regular open/close. We ran into a problem with v6 and the 2GB size limitation. Only 1400 records with linked pds filled that 2GB up. Network Admin is concerned about file size, and backups and using up that much space for "nothing". Thoughts? Below is what we are seeing when linking/embedding p…

I have a ¶ delimited list of numbers in a field. I need the outcome to be: first value - second value second value - third value third value - fourth value etc. Example List is 900 200 600 500 Outcome: 900 - 200 200 - 600 600 - 500 I managed to have the first and the second, and then I'm stuck. Any hint ? TIA

The data I am working with is formatted as yyyymmmdd I assume I need to use the currentdate function, however when I ask for current date it gives some number that is not formatted same way. I would like this to work so I don't have to change it again. Thanks

My data input file has time in 24 hour notation. I can format the "time" field such that it appears in 12 hour notation as a stand-alone. However, when I try to combine this field in a calculation (generating an email message with multiple fields - in this case reminding a client of an upcoming resrevation time), the time reverts to he 24-hour notation. Any ideas?

I'm trying to get a record modification timestamp when only certain fields are modified. I.E - If field 'a' is modified, record the timestamp, but if field 'b' is modified, don't bother. Not sure if there is a simple way to do this without resorting to an 'audit trail' type arrangement? I'd be grateful for some bright ideas....

I know there is a button to duplicate a table under relationships tab in the DEFINE>DATABASE. But, it duplicates a table under the original table and that's not what I want to do. I want to create an entirely new table with the same fields as my original table. How can this be done? TIA, Jerry

I have a table with a text field ('CF Year') containing yyyy-YYYY values. These are year ranges such as 2004-2006, 2003-2004, etc. Can anyone explain to me why the expression Max(History::CF Year)returns 20042006, ie no hyphen? And also why (text) '20042006' is greater than text '2008'? I've read all I can find on hyphens and words, etc but this looks as though FMP refuses to believe my fields are text.

I have a voter file that has unique fields for the past 10 or so primary elections. I am trying to determine the percentage of voting democrat or republican over that time. What I am thinking is I have to some how have a calculation that adds 1 for every primary vote in a certain primary, then takes the total votes in both primaries. Take each unique primary vote and divide by the total.

Looking for some help with date calculations. I have a start date and an end date (defined as dates). I need to calculate the workday duration between the 2 days and also figure out a way to exclude holidays. I'm guessing it needs to be a custom function, but I don't know how to do that. thanks

HI, THis is probably really simple, but cant seem to think of a way to do this. I have a list of 1000 Records with different acct names. How can i create a report that will give me the total number of Records by Account. Example. Customer name Total Records Chevron 5 Exxon 2 Costco 15 im just trying to find out how many times those names appear in my 1000 records the field is called "Customer_Names" Thanks...

I have a calculated field..... a fraction The calculation is (Let ( [ test = Mod ( cn_wtu_winter; 1 ) ; denom = Case (test ; 60 ; 15 ) ; numer = Case ( test ; Round ( cn_wtu_winter * 4 ; 0 ) ; cn_wtu_winter) ] ; numer & "/" & denom ) I need to create a new field (easy peezy).... but this field should be the lowest demonitator fraction. So instead of 3/15 the new field should read 1/5. Any advice? Thanks in advance

I currently have a database running in my shop that handles all sales invoices . I have been fiddling around with a "sales calculator' this allows allows sales staff to pick a product and a service and see how much profit we could make. I have 3 tables involved . Sales Calc - (this is what the sales staff see) Product File Service file via relationships it shows a list of services (only one need be selected) and a list of products . At the bottom , once the user has selected a product and service , the potential profit is shown. I am currently using radio buttons for this and it works fine. Now that my Product file has an image for each record, I wo…

Hi - Does anyone have a formula of a dynamic countdown timer for filemaker. I want to send emails with the countdown time to an event. ie. It is 4 days, 21 hours and 6 minutes until ... Does anyone have such a formula ? Dave I would be happy if I could just track the hours until an given time. If I have an event tomorrow. How would I track the number of hours dynamically from now until tomorrow. Like 23 hours away. as an example.

Hello, I designed a form that requires two signatures at the bottom when it is completed, supervisor's & trainee's signature. Instead of paying bug bucks for digital signature plug-in's, I figured I could make some type of limited access to these fields for users with proper permissions. Any help on this would be greatly appreciated. Steve

I want to have two radio buttons that specify whether or not someone wants an option. If the yes button is selected then there needs to be a charge that appears for $75 in another field. If the no radio button is selected then the value in this field needs to be $0. The radio buttons are using a value list with Yes and No. In the field that displays the charge i need a calculation that will essentially say if Yes is selected then make the value in this field = 75 else make it 0. I dont this this should be so hard. The problem is i can only get is to display the number 1 or 0. Im not sure. What must i do? Do i need to use a different approach?

I am using the "if" function in a calculation field. That field is located in a portal. It gets the response for the "if" calculation but only from the first/top record in the other related portal. How do I have the "if" function calculation search through the whole portal (all related records) to get a response? I will copy and paste the calculation that I am using down below if that helps. If (Individual ID=Small Group Male 2::Individual ID Male and Small Group ID=Small Group Male 2::Small Group ID;Small Group Male 2::Role)

Here is my problem: I am working on an accounting system. Each record is equivalent to a paycheque. We have a field called PayPeriod which is basically a serial number. Each employee starts at 1 and for each pay the number is incremented by 1. For an employee's first 20 pays, he is given 0.5 days of vacation for his bank. For the next 6 pays, 0 days in the bank. Then the next 20 pays after that it's back to 0.5 days added to the bank per pay and onwards like that theoretically forever. My question is, based on the pay period number, can anyone come up with a formula that will tell me whether or not to add to the vacation bank on that day ?

I have set up a table that contains catergory Value Month In browse mode I would like to have a subtotal calculation that sub totals all "value" for a given month, without subtotalling for all months. Could someone pelase give me the calculation or do i need to set up more tables?

I am trying to find a way to get a field to hold data in a found set - i shall explain I have a field called 'telephone number' and I need to have 1 field hold all the telephone numbers from a found set and have the ability to copy them to the clipboard with a ; inbetween each telephone number . I have experimented with merged fields and repetitions but no luck. Any ideas welcome! thanks

[color:red]FileMaker 9 Windows - sharing with FM Network: How do others control the number of users who can be editing the same record? Get(UserCount) will show how many users are accessing the file, but not the specific record. I want to stop 2 users competing to edit an individual record. Thanks for your help.

Hi, I am trying to write a if function that can change the end date if the finish time is between 12:00am and 6:00am. This is what I would like to happen... If (Main Screen: Finish Time = "12:00 am ... 6:00am"; Main Screen: End Date+1; Main Screen: End Date) Can anyone help me out on how I can do this range? Many Thanks, Dean