Calculation Engine (Define Fields)

How do I extract the first occurrence of any item in a list from a field? Field A contains lengthy text. Within that text is the description of a fabric. The predominate fabric always appears ahead of the lesser fabric i.e., “...75% polyester, 25% cotton...” I want Field B to show the first occurrence of any of the values in the following list. “Polyester” in the above example. Any help would be appreciated. ...full grain cowhide full grain pigskin full-grain cowhide full-grain pigskin suede cowhide polypropylene Work-Dry® mesh PVC CoolMax® polyester wool polyester polyester fleece Merino wool cotton rigid denim cotton denim cotton…

how can i have a field act as a simple 1 or 0 via a check box? all i seem to be able to get gives me a value list missing error in my layout, i see little point in creating a value list for 1 or 0!

question about validating. See attachment FM9 file, contain 2 small table. 1. table User fields: userId Text unique auto increase UserName text 2.Table Service Fields: user ID Text (value: User::UserId) user name text (value: drop-down list from the value of User:Username Service text (select from drop-down list) (given sample sevice name cleaning,gardening,teaching ...) Relationship user::Username --< Service::Username random added some sample user names from table user. (e.g. peter, simon, jon) Now, I'd like to add service to those users from table Service. each record conta…

Hi all, Is it possible to do a loop within a calculation (in Define Fields) OR to call a user-defined script from within a calculation? Each record has a set of related records. I want to define a calculation field displaying a particular related record if and only if it meets other criteria as well. Right now I have a calculation which looks really ugly (I am inexperienced with FileMaker scripting please don't yell at me!): If (GetNthRecord(Foo::Bar;1) = "Criterion"; GetNthRecord(Foo::Foo;1); "") & If (GetNthRecord(Foo::Bar;2) = "Criterion"; GetNthRecord(Foo::Foo;2); "") ... etc I couldn't figure out a way to lookup a related record fr…

If field a is $769.89 and it is multiplied by .05 then field b will show 808.39. This works fine, however I need to do this: Field a will be user input, field b will also be user input but field c will be the calculation. Any advice??

Hello I would like to make a file in which the field "future date" might ok the opening of a new layout based on a particular future date. If a person in the present scenerio wanted to 'cheat the date' they would simply change the system date in the preferences on their computer but if they were to retrieve a date only from the web http://www.time.gov/timezone.cgi?Eastern/d/-5/java then the field could be populated by the absolute accurate time. I am just trying to find a sure fire way to make sure the date is correct and not cheated upon. I have a reason for this. I want to set up layouts that can only be opened on a certain date in the future. Any…

I have a text field that has an order number in it that is a 13 digit number. This number is in each record but the text is always different and the order number can be different. The only constant is that the order number is 13 characters long and is numeric. I cannot know what the order number or text will be so I cant do a portal or loop though to find the order number. Best if explained by example: record 1 qwreouh arw1226213456998eouaw awreohou arouw awgou record 2 agw24 erh43'pwr|gw4uon w4gpnhp5216813050354w4gh What im looking for is to make a calculation field that will trim off all the text and numbers that are not this particular…

I need help with a calculation that will only change the modification date if MANUAL entries are made. We have a data import script that runs every time the filemaker database opens. This always changes the modification date and basically renders it useless. Is there a way to have the modification date change ONLY if data is entered into the record manually? Will I have to just exclude all the fields that are getting touched with the import script? I am not sure what to do. Thank you! -Ray

It is common in Europe that the year be at the beginning of the date, 2008/03/12, followed by the month and day. The data set I received is in this format but the filemaker date functions seem to act like this is some kind of alien language. In the test fields using... WeekOfYear ( 2008/03/12 ) DayOfWeek ( 2008/03/12 ) Year ( 2008/03/12 ) Yield the same outcome in the field, "?" I have played with different combinations of file and system settings, field formats and field types and sometimes it works but only if I reverse the date to 03/12/2008. Then the functions work correctly, rendering the result needed. I find it hard to believe that File…

I have a report that tells how many jobs a worker do, but i need to report how many days he/she showed up... I need something like this: George Plumbing 2/2/2008 $4.50 Fix Tube 2/2/2008 $9.00 Plumbing 2/4/2008 $9.00 Plumbing 2/5/2008 $9.00 Worked Days: 3 $31.50 Anyone can help?

I have a database for tracking budgets, amount spent, and balance. I have a layout that I show information about each account. I want to have a field that will total all amounts written out of that specific account. How can I show that amount in a specific field. For example I have an account for the warehouse supplies. I want to have a field that shows a total of all the purchase orders that was written out of warehouse supplies.

I noticed that GetNthRecord, when referencing a related table, "returns the Nth record of the related set (relative to the current record), regardless of how the related table (or portal) is sorted" - as defined in the Filemaker Help. Is there any way to make a related table function get the nth record based on how it's sorted? (i.e., as if it were referring to the current table?). Thanks

Hello, trying to see if this is possible: I've got a table that is basically an activity log (simplified) TRACKING_ID (unique) TASK_ID (the TASK being tracked, multiple) ACT_DATE (when something happens to that TASK) ACT_DESCRIPTION (what happened to that TASK) DURATION (time in between) So example records would look like this: 1, 50, May 1, Submitted, x 2, 33, May 3, Submitted, x 3, 50, May 5, Reviewed by John, 4 days 4, 50, May 7, Returned to John, 2 days 5, 33, May 10, Rejected, 7 days The counted days are what I'm trying to calculate (2 days between the act of TASK_ID 50 being reviewed by John and then being return to John.) …

I have a related number field, which sometimes contains "<" on front of the number, due to it being a scientific test value. ie. They can't test for the existence of something below a certain threshold so it gets called "<0.1" instead of "0". I am trying to get an average of this field via a batch relationship. Only trouble is that the "<" seems to be upsetting the Average function. I've tried this: Average ( If ( Left ( RelatedTable::RelatedField ; 1 ) ="<" ; 0 ; RelatedTable::RelatedField ) ) to try and make the "<0.1" translate into "0" but the Average function is just drawing a blank (empty). If I leave the value as "<0.1" then the …

I have a field with a value list Check Box. When a person selects more than one check box the field in another layout does not show all the values. Just the first one. It throws the other underneath the first row. How can I get the values to list side by side of each other I do not want the check boxes in the other layout. Thanks in advance

Hi, I have a bank header table and a transaction table. I need to be able to display transaction records in date ranges in tabbed portals. One tab would show transactions up to and including today, the other tab would show future transactions. I created a global calculation field on the header table using get(currentdate), and used it in the relationship. It semed to be working OK, but then I noticed that the portal was displaying records in the wrong date range. So I displayed the calculated get(currentdate) field on a layout and saw that the problem is the value in the calculation field is wrong - its showing a date of several days ago. On his website, Dw…

The attachment file is FileMaker9 file which has 2 tables inside. The 1st is original one, the 2nd are created one by myself, they have exact same fields. (I've delete all other non-related fields from the 1st table). 1. Country TEXT (show contry names) 2. Price number (different trips in those contries) 3. Price total Summary (Caculate the total amount for all price field) 4. Subtotal Caculation use "GetSummary(summaryField;breakField)" function to caculate subtotal price for all trips in each country. When Created Subtotal field, I use Specify Caculation "GetSummary ( Price Total; Country)" Problem: The origina…

Hi, The project I am working on involves date ranges. User may select monthly, quarterly, annual and fiscal date ranges. The report then finds parent records with related records in the range, looks at the related table and counts the number of records in the range. That part is working fine, but I also need to set a field to Yes or No if the parent has or has not any related records within the previous period: monthly within the previous month, quarterly withing the previous quarter, annual within the previous annual and fiscal within the previous fiscal. I can set the ending date for the previous range easy enough by subtracting 1 from the begin date of the…

Is there a way to make a field dynamically self aware? Like displaying how many characters is being typed into a field without having to commit the record first or stopping the user from typing more characters then allowed under field options without having to commit the record? Pretty much like a web form field where one cannot type in more characters then allowed.

Hi everybody, i´m new usingfile maker and I don´t know how to validate that the user must just input 12 chars into a field ... Actually if the user input more that 12 chars into a field i get the message, "sorry, you must enter 12 chars... (I write this messahe into the Display custom message if validation fails option), and this happens (i think) because i´m reading data from a mysql database (using ODBC connector and that field is defined as a varchar type and size 12 ). I also try to use the Length(field) funtion but is not working ... Would somebody please help me with this issue ? Thanks in advance ....

I use an email plugin together with a SMS to email service . When I receive a message there is a line space before the message text starts. The company I use for the sms to email service can't seem to get rid of this line space so I would like a field to format the message correctly. I have tried the Trim and TrimAll commands but they don't make any difference. Is there a way to solve this problem? any help would be appreciated bt the way . if I send an email from say, Outlook to the email address , there is no gap and everything is fine.

hi.. is there some way to lock a field after i write on it? ... thanks for your help

I'm creating a database to replace a warehouse receiving log. The fields are part number, quantity, supplier, etc. Rather than navigate to the individual fields an type in data, I'd like to use a barcode scanner to enter the data into a temporary "data entry field" and have it copy to the appropriate permanent field based on the prefix (i.e. P is for part number, Q is for quantity, etc) To test the concept, I created 3 fields: part number, quantity, & data entry. I tried to set the data entry field as a calculation to perform a case function for the various prefixes, but could not find a way to copy the contents to another field within the calculation. Pla…

I've never seen any definitive evidence for choosing Number fields over Text for primary key fields. Either works with the Auto-Enter options for serial numbers... Is there a performance and/or logical advantage towards picking one over the other?

I have a field that is a calculation field, I have another field that is a check box. In the calculation field the calculation will look at two other fields if they equal or better the amount that it needs then It returns a Text statement. all I want the Check box to do is that if that box is checked the statement does not show. Example Case(total1 ≥ 3 and total2 ≥ 2 ;"Text") This will work fine until I through in the additional check box and I cannot get the file to work. Thanks in Advance.

Hi, I use the "List" function to get values from a relationship. I would like to get a list of unique values from that list. Example: From the following list: A B C A D Get the following result: A B C D I've tried with the "Filter" function but can't get it to work properly. I think it must be simple but I'm stock right now ... any help would be much appreciated. Thanks a lot!

Hi I'm not sure if this is a calculation situation, a script situation or both. I need to find a set of records that is in a date range of "14 days prior to today... to today" on a daily basis and automated with a button/script. In short - click a button and records with that date range would be found. THANKS.

When a validation fails for a field thats in a portal, how can I get it to return to that field?

In FMP 6, I used to be able to do (all number fields) (If(Field>"1","1","0") In FMP 9, the expression evaluates to "1" if Field is indeed greater than 1, but if Field is empty, I do not get a "0", I get a null value. I tried the inverse (If(Field="","0","1") I get the same result. What's up? Peter

this is going to be kind of hard to explain, but i'll try my best. i work for a photographic company, and we use file maker to gather people's information at events, and we print out order forms to have sent to the people. the people fill out the paper orders and mail them to us snail mail, or call in the orders. which provides a lot of extra work on my behalf, of which most of, i know could be eliminated by making full use of filemaker. (We have used v6 up until the other day when i finally convinced my boss to buy v9 advanced for what its worth) so now that i've explained what it's to be used for, i'll explain the current setup in slight detail... w…

Hi there, I have portal which shows related records (short notes-- one sentence). I'd like to concatenate all the related records into one note with each of the records separated by a carriage return. Is the best way to do this to create a stored calc that has the note and a carriage return and then to use ValueListItems in a calc looking through the relationship?? thanks ;)

Im new to Filemaker and trying to make a calculation to calculate sales price of my database of books. I have attached my database of pricing for you to look though to see my logic. What im trying to do is set my sales price for each item in the database to be a set amount above cost. There are two variables that I have to contend with, one is a merchant fee of 14% of the sales price. The other variable is a credit card fee of 4% of the sales price. I have each item cost but im not sure how to set up the calculation field to set the sales price so that I make my desired profit margin. Im sure this is really basic but Im new to this, any help would be great! exa…

Howdy, howdy: I have two calculation problems that are severely testing my last few brain cells; please refer to the attached. In the Trip Results box, I summarize some of the calculations in the Flight portal--one of them is average speed of the flights entered in the portal. The calculations (number) are: Total_Trip_Time = Sum(Flight_Data::Time_Leg) Total_Distance_Flown = Sum(Flight_Data::Distance_Flown) Average_Speed = Total_Distance_Flown / Total_Trip_Time As shown, Total_Trip_Time = 2.0 and Total_Distance_Flown = 34; both of these are correct. However, Average_Speed = 16.8. How on Earth did FM come up with that? It should be 17.0. The nex…

Hi I have a field containing a set of numbers (eg telephone number). Let’s say I want the field to contain eleven digits. If the incorrect amount of digits is entered I would like all the digits in the field to change colour say from black to red to alert the data entry person that there are too few or too many digits. While I realise I could use field validation to ensure that the correct number of digits are entered I would like to learn a calculation to satisfy the colour option. Can any one help with the calculation (or script) please? Thanks

I having an issue with Filemaker 9.0v3 and the screen redrawing PAINFULLY slow. It's a portall with many rows. On the one pc the same exact database opens and displays fine, on the faster computer (faster processor, more ram, an NVIDIA geoforce 8500 gt monitor adapter)it redraws every portal row line by line... SLOWWWWWWW.... I've tried changing resolution etc. to no avail. Any suggestions, I'm thinking it has to be a compatibility issue with the video card. Any suggestions?

In past versions of FM, the issue of uniqueness of the record ID was raised and several schemes were suggested for making a record ID match field other than FM's native Auto Enter Number. I use an auto enter calculation: Get(HostDateTime) & Get(UserName) & Right(Random, 12). There is a theoretical chance of 1 in roughly 1,000,000,000,000 in one second that a duplicate will be made. Since my scripts make a single record per user button click, this is not possible. (for fun, I tested a looping new record script and was able to make about 3 records per second.) With the new version of FM, is the ID number uniqueness issue irrelevant? Can I now just use …

I'm trying to get my head around how to do the following. I have two .fp5 databases, one of TV programs (each record = a particular episode), one of schedule events (each record = one airing of one show at a particular start/end time and date). They're related by a unique program ID field in each -- schedule event records get created via a portal in the program database and the ProgramID field in the schedule database is auto-populated with the appropriate programID for that particular show. And the relationship specifies a sort order of date and then start time, from earliest to most recent in each case. It's a one to many relationship, since a single show may…

Hi Can anyone tell me how to subtract one date field from another and have FM return the number of days that has elapsed? Thanks!

I'm having a challenge figuring out how to identify and remove a single value from a list of values within a single non-repeating text field. The value to be removed always starts with "V" while the values that will stay do not start with "V". For example: 123-12345 123-23456 123-345456 [color:red]V123-removeThisValue (not just the V) 123-234567 Thanks for any help.

We are using a container field to insert a picture (even though we are inserting PDF's) using both Macintosh and Microsoft Clients ... all Server & Clients are version 9. When we insert them from a Macintosh, all computers see the file contents in the container field. When we insert them from a MICROSOFT, only the icon and filename appear in the field. any suggestions?

Hi, I am really new to filemaker. All I want to do is add the values of two numeric fields together to give the value of a third field. I thought it would be as simple as Third Field = First Field + Second Field but I can't get it to work. any help for a coplete novice? Cheers

Hi, I am new to all this. I want to have buttons that when pressed would add to a field. For example I would have five buttons:apple,orange,kiwi,strawberry, banana and pear. When I press the apple, banana and pear buttons I want the field (chosenfruit) to show all three items but not the others. Thanks in advance for your help John C

hello ... im having problems when printing reports ... i have a numeric field that is not money is just quantities .. when i put the field in the report ..it shows a $ money symbol .. can i take that symbol off? i need that field numeric because im making sumary with that field. thanks for your help

Is it possible to reference field names via parameters? For example instead of using Table::Field could one use Table:$$Test where $$Test would be a parameter set to the name of the field? I tried it and it does not work, is there a different way to achieve that or does it just not work? I really do not have / need to use it but am just wondering if it can be done as it might be useful in my future projects. Thanks.,

I have an active direct marketing campaign where I feed a new name in one side (at various dates) and the proceed through a series of mailings until they become a client or are flushed out the back. I note the date of the first mailing (FirstMail) to them and date of last mailing (LastMail). I want to auto calculate the next Monday two weeks forward as (NextMail). If I happen to send out something on say Wednesday it can be slightly shorter than two weeks but I want my DB to tell me each Monday when I check who needs to mailed to this week. I've tried playing with various ideas and read some boards but nothing is what I really need. Any help wou…

I'm creating a database of "books". Each book is made up of EPS files stored in a repeating container field called "Pages" I have a calculation returning a container called "PageView" and a third field called "CurrentPage" The calculation for pageview goes like this: Pages[CurrentPage] This works like you'd expect. Enter a page number into CurrentPage and PageView returns the EPS data stored in that repetition of Pages. The script I use to get the EPS files into the repeating fields stores them as pictures. My problem is that some EPS files will display their contents in the container field (the desired behavior) and some will simply display an icon and fil…

I have been working on this for over a week. It seems so simple but I can't figure it out. I have a table called construction payments. It contains records of payments needed to be paid during the construction phase of a condo building project by clients who have bought condos in the project. There are three phases to the payment process. Phase 1) The contract signing phase where the client pays a deposit and a down payment. Phase 2) The construction phase where the client pays based on a payment schedule. Phase 3) The balance is over a fixed time frame. (Mortgage) What I have done is put the first two phases together in the construction payment table. …

I am not even sure if this is possible - but if it is I think I would have come to the right place. So far everyone here has been helpful with any problems I have had with FileMaker calculations. My next issue with the database is to do with the 'Time' function. Is it possible to setup a script/calculation/formula to convert a numeric entry into a time result? I have looked into the 'Time' sub-menu in the 'Format' menu without any luck... As an example, entering 360 (minutes) will produce 06:00, or entering 15 (minutes) will produce a result of 00:15 0 I would then use this as the base to calculate the per hour rate. Currently the minute charges are in a nu…

I am trying to finish a database that will track job time. I have a start time and a finish time. I was going to subtract the start time from the finish time and use that as a calculation for the duration of work. I understand that the results of that subtraction is in seconds. How do I convert that duration to hours and round that to the nearest half hour.

I was just wondering what the best calculation is for converting numbers to words for check printing. So 125.50 will print out One hundred twenty five and 50/100. I have one that I've used for the last few years. It's a long calculation that I originally got from the filemaker knowledge base -(See Answer ID 2860). I wondered if now in Filemaker 9 there is a more streamlined approach.

I am creating a customer database for our marketing dept, and I would like to know how to make 2 fields and combine into a 3rd as one field. Here is the sceniro: First - Seperate Field Middle - Seperate Field Last - Seperate Field First & Last - Serperate Field I would like to know how to make the last by inputting the First, Last so it will auto fill into the First & Last field? Thanks

Hi .. I create a table with many fields .. when filling with infomation i use the TAB key to change fields but it doesnt has secuence .. Example ... im in " item 1 " now i want to go to "item 2" i use the tab key and it goes to "item 5" so i have to use the mouse to select the "item 2"... can i control that? I hope you understand Thanks for you help Mauricio MOnsalve

I am creating an access console where I can script the creation of an account across selected databases, but I would like to get a value list of all available privilege sets in a given file. Is there a custom function to do this?

I have a text field that a user can enter a message. I know how to get a field to count how many characters in that text field using the Length function but what need is a field to show how many lots of 160 characters there are in the text field. For example if the message length is 140 then the result is 1 and if the message length is 267 the result is rounded to 2 ect. I could use the Case funtion but with so many possible results this is not ideal. Any help would be appreciated

i am wondering if there is a way to upload a video ( residing in a container field or even referenced in a container field) and fill in the forms on the video upload page from Filemaker? here is that page... http://www.youtube.com/my_videos_upload Just wondering. This may be something that can be done with Filemaker 10 maybe?

Hi there, everybody. Please help me to figure out what to do about getting the current date using a calculation; it's just the first step to a larger problem of calculating a date dependent on whether another date is two weeks earlier than today. The Get(CurrentDate) function calculated in a field that's defined as a date field returns 733168. Formatting the field in my layout as date format mo/da/year only lead to a displayed result of 00/00/0000. What's up with this???

ok...this has to be an easy thing...but for some reason, i just can't get it. i have a field called 'money'. i need to count anything in that field and have it give me a number, but i only want it to count things in that field that are greater than zero....how do i do this?

We have a database that records the time that has been taken to typeset or create a job in our department. We previously used a decimal point system for entering time taken for any work that we had done. For example - 15 minutes equated to .25 (1/4 of an hour) and 1 hour of work was entered as 1.00. Recently we have switched over to a new system of charging for artwork and it is on a per minute basis and a cost has been assigned at the rate of $124 per hour. I have been able to create a field which will automatically calculate the cash rate and enter it into another field using the time reference that is entered by the user (such as 15 minutes instead of .2…

I have some problems for calculations to display, even with a refresh action they wont display. The only way I get them to refresh is clicking on the portal record. So I found a work around to display the calculations. I added a script that switches to a copy of the same layout and back to the original one, it is refreshed, but is there not a "Right" way of doing this? Thanks

Inventory arrives on the dock - I would like to webcam an image directly into Filemaker inventory record, without going through an intermediary file. Is that possible? Right now, the import picture function opens a file dialog box. I just want to IMPORT PICTURE from what is currently on the webcam. It is less time consuming and error prone that way.

I posted this in "The Left Brain" but I think I should have placed it here in the calculation area of this forum. A link is below to my original post - I am hopeful that someone can help me with the date formula/calculation... Date Calculation - Turn around time?

I have a solution wich is built with the separation model in mind. I have separate Interface files, br-files and data-files. From what I understand it is nowadays no problem importing a script from one developer version of a solution to a solution out on the field if all fields mentioned in the script have been created before the actual import. Filemaker checks if the field name matches and uses it if it does. (In FM6 this was not so - it strictly used fm internal field id which could be a issue if you had created fields in a different order etc. ) Now to my issue. I tried to exchange the interface files on a solution X with the interface files of the develope…

I currently have an address table that uses lookups to update City, State, ZipCode. If a user enters a ZipCode, it will update City/State. If a user enters City/State it will update ZipCode. Now I am attempting to make sure that the ZipCodeSuffix also follows logical behavior by using the Auto-Enter Calculated Value. I would like the ZipCodeSuffix field to set itself to an empty value if Address/City/State/ZipCode change. If a user is actually inputting the ZipCodeSuffix I do not want its contents to change. This will insure that any time an address is changed, a user has the opportunity to put in the correct ZipCodeSuffix and the solution will not a…

I'm re-writing some of my find scripts and running into a brick wall with the perform find script step. I'm trying to find the records created in the current month, using either a variable or a calculation of Month(date). The problem I'm running into is the validation of the date field when using the PerformFind(Restore) Step. My old way of; Set Variable $Month;Month(date) enter find mode set field; datefield;$Month perform find works, but this seems like a lot of extras just to find records created in the current month. Am I missing something?

I am attempting to populate a container in file "Orders" with the contents of a container field located in either "Graphics" or "Logos". "Logos" contains the logo of individual vendors in up to six different colors. Keyword in "Logos" is Vendor #. "Graphics" is almost identical in that each record contains 1 graphic in 6 different colors. Keyword is Graphic #. Each Order record will have the following relevant fields: Text Color Vendor # - Keyword related to Logo File Graphic # = Keyword related to Graphics File Here is what I want to happen: 1. Place the correct color graphic from the Logo file into Order "Graphics Container" field based on Order …

Hello, Im not sure what you would call this BUT... I have two fields in the database I am trying to design that I do NOT want associated with any record. What I have is A summary field that adds up the total of the fields call "Spent" and then another summer that adds up the total of the fields called "Earned" Then I have an another field that is a calculation field called "Total Profit" that finds the difference between Spent and Earned. That all works well. However, occassionally I need to adjust "Spent" and "Earned" from another source so I am trying to put in 2 more fields one is called "Adjust Spent from Book" and another is called "Adjust Earned fro…

Hi there. I am pretty new at this. What I want to do is take three different fields and combine them to make another field (a file name). Here's what I want: I want the creation date (which shows up as 05/01/2008), one of two text choices from a drop down menu (LK or OMI) and the automatically generated serial number to combine to look like this 0501LK1234 (in it's own field.) I have absolutely no idea how to do this, I have never used scripts, and what I tried in calculation just didn't do anything. Can you help? Thanks!! emileezer

I am trying to make the LayoutNames function work without success. I calculated the following (see attached screen shots) from Define Database. It creates an empty field; indexing brings up the index list box containing nothing; the database contains nearly 100 layouts. Thanks in advance for wisdom and assistance.

Please help. I am totally a beginner FM database designer for my non-profit org and have come across a problem which I feel may be easy to fix but I cannot find the answer anywhere so far. I have a member database which includes a Transaction table which creates and stores invoicing info for our customers/members. The problem is with our Tax field in this table. This year, Canadian gov't decided to decrease our tax from 6% to 5% so I did so with the tax calculation, however all previous transactions (pre2008) changed as well and although we do not need to print this old info anymore, it is annoying to see the incorrect tax for a prior transaction. Also, you never…

I have a similar problem - but the next step. I have multiple survey questions. IF a user chooses "N/A" then I do not want that question to be in the count of valid answers. My value list is, right now, all numeric, with N/A as the last item (over 300 questions, some as EDIT boxes, and others, with over a dozen different value lists). However, the count function for a set of, say, 5 questions, returns 5 regardless of the answer. How can I count only questions where the the response is NOT N/A?? Almost every value list I have has an opt out N/A optional response that would change the calculations (the average of responses NOT N/A) I have this workin…

Hi, I'm trying to extract purchasing information from a paypal email, and got stuck. The goal is to read out the amount, name and email address of the buyer. Here's the beginning of the email in plain text: Dear Rheinport Media Inc., This email confirms that you have received a payment of $149.00 USD from *buyer name* ([email protected]). I'm using the following variables: $price, $name, $email, and a function MiddleWords ( Messages::Body Show; xx; z ). This works fine to get the price, but since a customer can have a first-last name, or first-middle-last, or other combinations, extraction of the name and following email address fails. Does anyone h…

I know this sounds simple, but I'm getting beat up on it. : [color:green]I am creating a "Bill Payment" database and I have a field called "Monthly?". It is based on a Yes/No Value List. I need a calculation that if the answer is Yes, it will take the entered "Due Date" and add 1 month to that and place it in the field called "Next Due Date". Thanks,

Hi all people! I have 2 simple questions for you: 1)Is there any way to use 2 fuctions into the same calculation field? I mean: Case (......=.....;......=....;....=...)[color:red]; If (.... =.....; ......=....;.....=.....). Which is the operator to join both functions? Semicolons? 2)Is there any function to get only the year, and only the month? I mean, I want to get "April of 2008" Well people, is pretty late here in my country and tomorrow everyone has to wake up early in the morning... so I hope you'll manage to understand my English, and I'll go to sleep. Thanks a lot for everything

Howdy, howdy: I have the following calculation: Case ( IsEmpty (Total_Distance); ""; Choose_SAE_or_Metric = "Metric"; "km/h " & TextColor("(" & Round (Total_Distance * .6213712; 1) & " mi/h)"; RGB (0;0;255)); Choose_SAE_or_Metric = "SAE"; "mi/h " & TextColor("(" & Round (Total_Distance * 1.609344; 1) & " km/h)"; RGB (0;0;255)) ) ...where I want to have FM automatically insert a comma should the calculated number be over 999. Since this has to remain as a text field, what would be the calculation to add for inserting a thousands-place comma? I thought of using the "Right" function, but got nowhere with it. …

I'm a beginner with Filemaker. Assume I don't completely 'get' filemaker just yet, although I'm very savvy with Macs and a pro with spreadsheets. How do I display a calculation of a field across multiple records? Let's say I have a field that can either be 1, 2, or 3. I have ten records and I want to display, in the header, how many No. 3's I have. How do I do this?

This is my first post for this forum and hopefully can get some help with this. I have been searching the internet to find something like I am trying to do , but no luck so here is my post. I am trying to make End of Day report showing Sales per hour based on the hours of operation our store is open(9am-8pm) plus show # of sales taken by clerks per hour as well. Ex: 9-10am $100 Blake 12 Kim 16 Ron 4 10-11am $250 Blake 15 Kim 12 Ron 10 and so on. I have the following fields to work with: Sales_Order_Number Sales_Order_Total Sales_Order_Date Sales_Order_Time Clerk_Id Which way would be the…

Please help me to correct the calculated fields. Brief summary of my problem. We import part numbers from external system to manage BOMs. First scenario: We import all part number information from extenal system 1. I have standard price and current price columns in my filemaker database wherein standard price is fixed for a part and current price is keeps on changing. For the first time when a part number is imported current price will be same as standard price. so I created stored a calculation field CurrentCost = If ( Current Cost ≠ 0; Current Cost;Standard Cost) Next time when that part is imported current cost should not get changed. Next scear…

How would I go about to create a converter the same as in Mac OSX dashboard? I started with the basics but I dont know how to start. Here a very helpful conversion site: http://www.peteducation.com/category_summary.cfm?cls=0&Cat=1305 converter.fp7.zip

Hi, I am a hoping someone can help me figure something out. I have been using filemaker for sometime. I am just not good with advanced programming or calculations. I'm more of a designer than a programmer. I am building a database to keep track of staff time spent on projects. The database has been created to help me keep track of clients time spent on task, expenses associated with a client and task. Also, I am creating a way to build a budget estimating costs for an assignment - in which I want to view actual costs based on estimated costs. I am having trouble with one component where I calculate a staffs hourly rate against time worked on a task. Th…

Is there a way to retrieve content (multiple values) of a repeating field A into a another field B by calculation. I could not find a solution. Any help would be great. Cheers Greg

at this url http://www.movable-type.co.uk/scripts/latlong-vincenty.html There is an accurate formula for calculation of distance between two gps coordinates. It is best to go to that page. : I have these fields "Lat1", Lat2, lon1, lon2 and distance. I would like to implement this formula into a filemaker file. Can anyone help with this. Here is the forumla from that page a, b = major & minor semiaxes of the ellipsoid f = flattening (a−:/a φ1, φ2 = geodetic latitude L = difference in longitude U1 = atan((1−f).tanφ1) (U is ‘reduced latitude’) U2 = atan((1−f).tanφ2) λ = L, λ′ = 2π while abs(λ−λ′) > 10-12 { (i.e. …

I just ran into an odd problem. I have a file that gets book images from Amazon. It didn't work on a couple. The reason was that there was a % character ("%2B" was the combo) in the file name (which was in the xml returned from a web services request to Amazon). What I found was that FileMaker translates (unescapes) the % and characters following in a file path, in the image path; so the path to the file is not valid. The latter surprised me, as % is valid in a file system file path (silly perhaps, but valid). There is no way you can use that file path to Insert or show an image file. The work-around, which was simple enough, was to remove the % character when…

I am trying to create tables via a calculation for use in a text field. For instance, lets say I have 6 fields (Test1, Test2, Test3, Score1, Score2, and Score3). The "Test" fields are the names of different psychological tests that I use. The "Score" fields are actual scores. I would like the text calculation to result in an evenly spaced table: Test1 Score1 Test2 Score2 Test3 Score3 However, given that I want the user to be able to use non-fixed width fonts and the actual length of each test name will vary, is there a way that I can get these tables spaced correctly? I guess what I rea…

How do you populate a field with the value of a field either in a related table or the layout.

Hi, This is an extremely basic question and has to do with calculating a sales tax in the individual lines of a bill. Basically i have my products table which defines whether a certain product is liable for sales tax and another 'base values' table which holds global variables of things such as the current sales tax rate, etc. In the bill lines, i want to do a simple calculation of the sales tax once the user has entered the quantity and price of the product (price may vary between supplier and depending on seasonal demand, so i do not store it as a fixed value anywhere). With a calculation field there is no problem, this calculates the tax correctly. The p…

I am trying to use GetasBoolean in a calculation where I am using a global repeating field. The fields are used in report generation and I need to test to make sure only one field has data in it or less. Repetition 7 has text, all others have numbers. All but Repetition 7 give me a response when I enter something into them....the calculation is below: It is supposed to recognize text and give me a 1 for the statement, but it does not. Let ([ A=GetAsBoolean ( gReport Field[1] ); B=GetAsBoolean ( gReport Field[2] ); C=GetAsBoolean ( gReport Field[3] ); D=GetAsBoolean ( gReport Field[4] ); E=GetAsBoolean ( gReport Field[5] ); F=GetAsBoolean ( gReport Field[6] …

I created a database in which I am attaching related documentation. This would include things like purchase orders, emails, notes, etc. I can see how to add a container in which the documents are stored but when I click on the item (such as a pdf) the "attachment" doesn't open. What am I doing wrong?

Hi All, I am using a tabbed layout object with nested tabbed objects. I'd like to put a record count number in the upper right corner of each tab. I want this number to be the count of the portal records (related records) on each tab. I have the fields in place and they look great, but the counts are not accurate. Is there an easy way to get this calculated? There is no Get ( PortalRowCount) function which would seem the most obvious solution. I've tried using Get ( TotalRecordCount ) and of course that gives all the tabs the same number, the total number of records in the related table. What am I doing wrong? Help please!

I have an application where time is entered as a number based on a 24 hour clock without separators (such as ":"); for example, the "start time" of 8:30 AM would be entered as 0830 and "finish time" of 4:00PM would be entered as 1600. I would like a way to calculate the total time (duration) between two such entries that results in a number not time. Obviously, a simple calc: Total Time = "finish time" - "start time" for on the hour entries is simple but in the case above, the calc would generate 730 and the preferred result would be 7.5 (hours).

I't s a nooby question but can't figure it out. I need to extract the text between the "_": example: A0110001_2draw1_.ai D1111_detail1_.tif 30821_1fx1_.tif ANY help is appreciated! thx. MacB, Netherlands

Hi I am trying to find out how to get all the text between 2 words in a field. example 1 "The cow jumped over the moon. " -- in field phrase between [color:blue]cow and [color:blue]moon yields [color:red]jumped over the example 2 "The dish ran away with the spoon." between [color:blue]dish and [color:blue]away yields [color:red]ran Thank you

So I have some fields in my table that are blank, and am trying to write a case statement that would replace the blank field with a value of "Null". Is the case statement the best way to do this?

Hello all, I need to calc field "Combined_ALL" that brings together the contents of 3 other text fields. The only issue I have is that the calc field needs to place a carrige return only between the fields that actually have data in them. I have tried to figure this out and have come up short.

I need some help on a calculation where the original quantity and the balance fields reside on my master database and the inside quantity and the outside quantity fields reside on two different related databases. I am trying " balance = original Q -( inside Q + outside Q )" I cant get it to work correctly ( grrrrr !) Any suggestions ? Thanks

What is the shortest way to make following calc, but I do not want to use a giant Case()like Case( result >97 and result <100; A+; result >93 and result <96; A; etc. Values are: Letter Result (from - to) A+ 97-100 A 93-96 A- 90-92 B+ 87-89 B 83-86 B- 80-82 C+ 77-79 C 73-76 C- 70-72 D+ 67-69 D 63-66 D- 60-62 F 0-59 TIA

I use a custom function to force the number of decimals to display in a numeric field on the layout (e.g., to display '32' as '32.000' if 3 decimals are required in one instance of use of the layout or '32.00' if 2 were required in another. An application that can be run where different currencies might apply is a possible reason for the need to do this. I can use this same function to cause a summary field to display the proper number of decimals when the summary field is used in a 'grand summary' total. But when the function is applied to the summary field when needed in a sub-summary part on the layout, the 'grand total' is displayed, not the sub-total. …

I'm working on a movie database. I have a field that tracks number of certain scene types in a feature. Let's say "10". I want a button that can create the same amount of "New Records" in a related table that breaks those scenes down into details. I haven't found anything in New Record that can be modified. Any ideas? Thanks!

Hi, I am trying to create a report from a related table and do not know how to Total one field from all records? I was hoping someone could help me with it and give me any suggestions for my database? Also If I could send my project to someone for assistance? Goto MOCC Revenue report layout. This is where Im having problems? Username: Admin Password: 1113 Thanks Randar MOCC_Conference_Center.zip

I am using Get (CurrentTime)to set a time as a script parameter. Is it possible to get military time regardless of how the system time is set to display?

Hello, I am making this database, and one of the features of it will be a clients database. I don't want everybody to add clients, but I want everybody to see them. I made a layout with all the information, that cannot be modified in browse mode with an "ADD" button. When pressed this button will create a new record and go to another layout modifiable in browse mode, so the person can put the information. This layout has 2 buttons, one OK button that will lead to the information layout and commit the record. Now comes the question. I have another button, called cancel, this button has the script "revert record/request" and go to information layout. The…