Calculation Engine (Define Fields)

Is it possible to set fields in such a way that if an entry is made the first word (name) automatically is written with an upper case letter? I am on FMP 6

How do you create a calculation in the parent table that will return you the value from a field in a particular portal row of a child portal. For instance I have record of Contacts and the phone numbers for those records are kept in a related table of Phone numbers. I can manipulate and reorganize the order the phone numbers appear in that table. What I would like to do is have two records in the parent Contact table. One field would be Primary Phone and it would pull via a calculation the Phone number from the first row of the Phone Number portal and Secondary Phone filed would pull the Phone Number from row two of the Phone Number Portal. Kind of the same way the G…

I can't seem to get this to work. A good example is having a conference affiliation automatically assigned to a University based on the date a game is played. Here is a sample of what I have for two schools. Case ( Team = “Univ. of Arkansas” and Date > 8/1/1992; “Southeastern Conference”; Team = “Univ. of Arkansas”; “Southwest Conference”; Team = “Florida International” and Date > 8/1/2005; “Sun Belt Conference”; “Not Division 1A”) Team and Date are the field names. However, the date part of the function doesn't seem to work. Any ideas?

Dear FileMaker Gurus: I have a FileMaker v.6 DB, hosted on v.5.5 Server. I was asked to create a new field, in the form of "Yes""No" Radio Button, default to "Yes." Yet! Users are allowed to check "No." How about the field definition? Calculation? Help! Please! Max NY

Dear FileMaker Guru: I was asked to revise a FileMaker v.6 Report Tracking DB, hosted on v.5.5 Server. On the "Entry" Layout, users can enter "projected" total page number to the "Page Count" Field. The value will be replicated to the "TotalPageNo" Field in the "Tracking" Layout, where all sorts of information of the report are monitored. Now, I have received a request: Create a new "Finalized Page Count" Field for the manager, only, to enter the "Finalized" total page number into. If the "Finalized Page Count" Field is empty, current replication stays. If the manager enters, in this field, an "final" number, which will updates and locks, beyond m…

Good morning, I have a field that returns a value but can not create a report from this field because there is an infinite number of possible returns and the "count of the records" is just of little value. So what I would like to do is in a seperate field use a calculation to state that if a number falls between X and Y then Z. My current case statements is as below. The problem is that it only evaluates the first expression. All records in the DB (if I use replace field contents) receives the value, 0 to -4 after running this calc. Case VAR<0>-4 ;"0 to -4"; VAR<-8>-12;" -8 to -12"; VAR<-12>-16;"-12 to - 16"; VAR<-16>…

Hello, I'm looking to create a calculation field that substitutes for the text style "italic" and inserts HTML code (i.e. text) For example: Substitute( text ; TextStyle(italic) ; "" & text & ") This way, when users decide to italize a word in a text box, the calculation will make it HTML compatible for uploading to a MYSQL database.

Why would this calc not return "the first day of the month that occurred 11 months ago"... Date(Month(CurrentDate)-11;1;Year(CurrentDate-32)) ...and return "2/1/2005" instead? My first guess was that subtracting 32 days from the current date in the "Year" section of the date would allow for an accurate "last year". Was hoping to be able to "slide" dates on an existing report to account for the change of year... *CurrentDate is a global date field set with Get(CurrentDate) upon opening. Thx!

Can someone help me with this calculation : I have defined the following fields : Field VN text (input string of max. 30 characters) Field VN1 text (cal. Field showing one char. of VN on position 6) Field VN2 text (cal. Field showing one char. of VN on position 7) Field VN3 text (cal. Field showing one char. of VN on position 8) / Field VN25 text (cal. Field showing one char. of VN on position 30) Field Nr_options number (count of possible VN numbers) Field Error text (cal. See description below) In field Error in want to show the user a warring of a incorrect input in field VN by using a calculation. For example : If fiel…

I am trying to create a field that will create a flag for a date field when the value is saturday or sunday. Here is my calculation where the date field is "service date". I have also tried this using DayofWeek with 1 or 7 for the weekend days. If (DayName ( Service Date ) = "Sunday" or "Saturday" ; "Y" ; "-") It does successfully flag the first day specified, but not the second, so I am probably not using "or" correctly. I have tried various permutations, but without success I would appreciate some help with this. Thanks

I'm trying to setup what would seem to be a very simple calc that will show the user: 1.) Total Number of Records 2.) Total Number of Active Records 3.) Total Number of Inactive Records 1.) For the total number of records I have a simple "Get ( TotalRecordCount )" calc field setup. (That works fine) So the user can keep old records in the db but keep track of what's current I've added a field that refers to a single value list "active" and set the field as a checkbox. This allows the user to check or uncheck the box to keep track of which records are active. 2.) To setup a count for the total active records I've created a summary field to get coun…

I have a calculation that times how ling someone is in a record. i.e TimeFinish - TimeStart = Actual Time My problem is when a record is created at 23:30 and finishes the next day/morning at 00:30 I have a minus time calculated.... same thing applies if it were 12 hour clock. If not worse as it happens twice a day. How can I over come this one?

Hi, I'm having a problem trying to create calculated field. I want to do a sum and average of the salaries, but all I get are empty fields. Here is a simple database definition. Table: Employees Fields: pkEmployeeID, WageHour Table: Weeks Fields: pkWeekID, NumberOfHours, fkEmployeeID Relation: pkEmployeeID - fkEmployeeID How do I sum the total number of hours in a calculated field in the table employees? I tried Sum(Weeks::NumberOfHours), but all I get is an empty field. Any help would be great. Thanx

I have two tables, A & B with no relation. How can i make a field from table A to auto lookup a global field from B whenever i make changes? Any solution beside using script? Thanks

I have the following formula: Case ( MilesuntilService ≤ 3000 ; "Good"; MilesuntilService ≤ 4500 ; "Service Soon" ; "Schedule for Service" ) I want the text to change color depending on what is in the Service Status field, for example - "Good", text will be green - "Service Soon", Text is yellow, and - "Schedule for Service", text is red. I have placed the TextColor calc in several different spots - sometimes I get too many parameters error, or the field lists a 1 or 0 even through the calc should return Text. I not am getting where the placement of the additional info should be. I swear I took several, hours long stabs at trying to figure this o…

Good Morning, I have a feild hat calculates a value during every import. Is there a way I can refresh the data to perform the calculation over again for the enitre database? Thanks.

I've got a order database with a portal showing all the items included on an order. Part of the items list is a field indicating if goods were received complete, short or in excess. I've put a flag field on the order to indicate if all items were not received complete by using the Case function. However, it only evaluates the first record in the list of related items. How do I get it to evaluate all the related records instead of just the first one? Or do I need to use a different function or method to do this? Here's my calcuation: Case(Items for Order::Goods received ≠ "Rec'd Complete"; TextColor("Receipt Notes" ; RGB (255; 0; 0)); IsEmpty (Items for Ord…

Hi all, Although I have been using Filemaker for almost a year and have some instinctive understanding of the various field types... it seems that on second thought I do not really understand some things in regards to fields on the deeper level. For example: 1. What is the difference between a calculation field being unstored versus a calculation field being stored ? 2. What is the difference between a stored calculation field versus a number-field having a calculated value with the "do not replace existing data" unchecked ? 3. What is the difference between a number-field having a calculated value (and the calculation is simply a reference to anothe…

Hi, I need to remove and replace blank space in an address field. Ex: 100 Main Street becomes 100+Main+Street THank you.

Hi, I am trying to create some kind of a automated formatting for one of the field in my customer database. In this case, I have a field with the expiry date of their credit card. At the moment, this field is a Text field to allow me to enter the expiry date in such way >>> 07/07 or 03/05 , etc... I'd like to find a way for that field in my layout to become red (wether the whole field or only the font) when the expiry date is less than the month and year of the current date... If this is not clear, here is an exemple > We are in January 2007. Customer A has a credit card expiring 05/07 so it's cool. Customer B has a credit card expiring …

I have three text fields one global one account_name and file_name I’m having a hard time figuring out the correct function to do this correctly. Any help would be appreciated Nick

I have a case function that is not working properly. It only picks up the first case. Case field >0, field; field 2 >0, field &","&field2; and so on until I go to six levels it is a bunch if names. that I want to put together with commas, I will pick up the first line but not get any of the others. Thanks in advance Randy

I have a field that I want to populate only if one thing is present. but I want it to populate with the following criteria. If the child's last name is different than the parents then I want the field to be the childs first name and his last name, if it is the same then I want to use the childs first name and then the Last Name field. Now is there is no child first name that is populated then I don't need anything to happen. What I did works except when there is no child first name. It will populate with the last name anyway. The Childs First Name, and if Case (Child 1 Last If Different > 0;Child 1 & " " & Child 1 Last If Different;Child 1 &a…

I am using setfield and its formulas to format a text. I have phrases, sections and paragraphs whose ends are defined with a comma (,) semicolon (: and period (.) respectively. To insert new entries, I use Replace to get the end marker and add the new text with the appropriate new end marker (, or ; or .). This works fine inside the paragraph but if the section is at the end of the paragraph I end up with both a semicolon and period (;.). Is there a non-printing elements that I could use as markers to avoid this problem? Thanks R

Hello, I have a database that tracks advertisment hits by a unique source code per ad...my problem is I can't fiqure out how to summarize and list how many hits per source code....does anyone have any suggestions?? Thank you, T

I have an application where there are two fields on the current layout that are keys to create records in another table. ; style & color. There are quantity fields that are also part of the relationship. My problem is that a user can input the style, color and quantities, then change either the style or the color, and build a whole new relationship, leaving the first set of records intact. So if someone creates a record, inputs the values, an then changes their mind, I will be left with line items that should not exist. Is there a way to prevent modification of a field after it conatians a value ? I have messed around with scripted validations and FM8.5 field validati…

I have a cabinetshop. My customers like to have their specifications in inches. We, however, actually build the cabinets in millimeters. My database has one table for CUSTOMER SPECIFICATIONS and one table for CABINET ENGINEERING. These tables are related with the match field PROJECT NUMBER. Is there a way to start in the engineering table, LOOKUP the customer's preferred countertop height & automatically multiply the Inches X 25.4 millimeters? For example: If CUSTOMER SPECIFICATIONS list countertop height at 36 inches, CABINET ENGINEERING reports this result as 914mm. If possible, I would like to keep this as a simple lookup without add…

I have three tables in a file. Two of the three I have no problems with. But the "Measures" table is getting my goat. Whenever I try to delete a record, all records get deleted. I'm not dealing with a portal. I'm working with the main table. I've tried a recover but to no avail. I usually exaust all of my resources before bothering others with my problems and that's where I'm at now. Any Ideas? Thanks, Mel

If I search the database and the manuals of course I find it but if you know you can tell me in order to spare time. I want to ask: 1. How to validate a field to accept only 15 values and not anything else. I know that we can make it with a list validation. 2. How to validate @ if exists in a field and do not allow the costumer to leave without to put @. in the EMAIL field. Pascal Learning fast I think FM amazinf prog...

Hi, I'm having a tough time tracking down FM functionality that works like a LIKE statement in a calculation. Specifically, I have two fields fieldA and fieldB. FieldA has a list of values associated with it, and the user can choose multiple values. I want fieldB to be populated with specific values based on whether the user selected single or multiple values for fieldA as follows: If fieldA equals "valueX" then fieldB = "result1" If fieldA equals "valueY" then fieldB = "result2" If fieldA contains "valueX" and "valueY" then fieldB = "result3" Of course, setting fieldB based on single values in fieldA is easy. So no worries there. What I'm t…

I'm having a severe "senior moment" that's lasting all day today and may drag on throughout 2007 at this rate. Ugh. So, if you can help me solve the following I'd be immensely grateful. (Refer to the attached file, please.) What I want to do is in the Reports layout/table, select an identifier (ex. PHX) so the field beneath it, City, will autpopulate with "Phoenix, AZ"; it would autopopulate this field from its related City field in the table/layout, Value Lists. Why I can't get it to work, well...I'm down to two brain cells and counting. Thanks in advance for your help! Rich pee ess: This calculation will eventually end up in a run-time soluti…

Previously I saved all scanned documents into a FM containor field as addresses. With FM8 files able to store terabyts of info I am saving scanned documents directly into the containor field. Several problems have arisen since I converted. a) The new application will not recognize the location of previous addresses. I put the the folders with the actual scanned document files on the desk top and still it will not recognise the address. The only thing I did not do is put the document file directly on the desk top as this is not practicle with 20,000 ducuments. since the FM8 application now has older records with documents stored as addresses and newer records with s…

I have a database for a private music teacher and we're trying to have it automatically keep track of lesson charges based on the number of students in each family, the length of the lessons, and the charge per lesson at a rate per half hour. I've been using simple calculations that work fine if each lesson is the same length. Now we're having to deal with lesson lengths that can vary from student to student, either 30, 45, or 60 minutes. I've set up a field for the lesson length formatted as radio buttons with each one of these values and I'm trying to complete a calculation based on these fields: Count - the number of students in the family Lesson Length - 30, 45…

Hello guys! I have some questions. I mean need some help. Where i can read more about this problema? Please, don't derect me to http://google.com i know about it. Please derect me with some links. thanks! UCAKK^^

I know how to create a time/date stamp field which will track changes to a record, but how about a way to track changes to a field? I have a list of "tasks" that need to be accomplished for each person who has a record. Each task is formatted as a simple "yes" only checkbox, and I'd like a stamp to record when that "yes" was checked off. However, sometimes tasks need to be done again (committee chair dies, so a new one needs to be appointed -- happens all the time), so I need the stamp to go blank if the "yes" is unchecked, and then record the new time/date when the task is checked off again. Any ideas? Thanks! Lisa