Calculation Engine (Define Fields)

I have an un-stored calc that contains Get(PageNumber). When i export as PDF it returns "1" for each page. I am working on this project in 8.5 on OS X 10.5 Leopard so can not append to PDF (build page by page), and anyway i do not really want to do it that way as it is not very efficient. Am i missing something or is this just the way it is. best Stuart

Ok, I need a field to change a number entered into a specific format. 1-23-456-78-9A-10111 (which is comes out to NINE numeric, ONE alpha, FIVE numeric)when the input is 123456789A10111, making sure that the 10th is alpha and not numeric. That way the only acceptable input would be restricted to this specific set requiring the 10th to be alpha. It is a bit of a twist on the phone number format, but it has me boggled at best at this point. Any suggestions?

Hello, I am trying to obtain the sum of specific records without performing a find for those records. My database tracks the progress of cabinets being produced in three different facilities. One cabinet = 1 record. I have a field called : "WOOD SHOP LOCATION" with a drop down of three options: Los Angeles, Chicago, New York I have another field called: "STATUS" with a drop down of three options: Not Started, In progress, Delivered My goal is to have ONE layout (a breakdown) that contains fields that calculates the following running totals: LOS ANGELES: Not Started: [__X__] - the number that appears in this field is the sum of the f…

I am working on a script to find all call back dates after 01/01/2009 and todays date Here is the script Enter Browse Mode Show all records Enter find mode Go to field (People::Call Contact) Insert Calculated results [ People::Call Contact=(Date ( 01 ; 01 ; 2009)..Date ( 12; 31 ; 2009 ) ) What do I have to use here?

When I put my cursor on a random place in my database when I am not in layout mode most of the fields highlight and some dont. I tried finding where to turn this off but cant. I am not referring to layout mode/layout setup/show field frames. That isnt checked and still see fields getting a dotted highlight. How can I turn this off completely?

This one is right out of the book. I'm simply trying to set a variable to the number of days between two dates. My script step is: Set Variable [$Diff; Get(Current Date) - Invoice Date] What I get is $Diff = 179:24:37 ... which equals the days expressed in hours:mins:secs. What is preventing the calculation from simply returning 7 days or 7.46 days? Is there a better way to calculate days between dates? Thanks in advance for any help.

I have a file that has phone numbers formatted like this: 919/213-4567. A long time ago I found a tip that allowed me to paste the number into the field, with a calculation field on top of the actual phone number field. The calculation field then showed the number correctly formatted as (919) 213-4567. I have used this many times before, and it has always worked fine. Today, even though I had pasted in several numbers successfully, I suddenly encountered a message stating that the field (the mask on top of the actual number) could not be entered because it was not modifiable. I know that I cannot modify a calculated field, but why did this suddenly become an issue?

I have several thousand records that have to be summarized.... I noticed it takes a really long time to summarize it. I have most of my calculations set to unstored.. because I am worried about conflicting data getting indexed if someone changes one of the global fields. But I think this is slowing down the summary process. Anyone know how to speed up the summarizing in Filemaker?

I still have a TON to learn about Filemaker Pro but this problem has me stumped. I have 3 fields Field 1– Book Name Field 2– Revision Field 3- Page Number I want Page Number to increase by 1 with each new record as long as Book Name & Revision stay the same. When either Book Name or Revision changes I want Page Number to reset to 1. How should I do this? Thanks in advance, Angie

Hi, we're trying to use date of birth to pull out demographic reports for clients in certain age ranges (6-9 years old, for example). The age calculation we're using gives us a result that is in decimal format (2.5 years, for 2 years and 6 months, for example). We then created a calculation field that groups the clients into categories by age using the following calculation: To calculate if age falls into the 6-9 year old category (Field name=age69) If (age calculatednoblanks < "10.0" and age calculatednoblanks ≥ "6.0" ; 1 ) Then we created a summary field that totals that category. To total 6-9 year olds: Summary Field that totals field “age…

Hi - I have a calculation field to show local time where my clients are, based on a time difference field in each of my client records. The calculation is: Time(Mod(Hour(Get(CurrentTime)) + Time difference;24); Minute(Get(CurrentTime)); Seconds(Get(CurrentTime))) This field only updates when I make a change, such as clicking in the field, or resizing the window, even though I have the 'unstored' option selected in the calculation. How can I make it update itself continually to show current (local) time?

I have an age calc Let ([ today = Get(CurrentDate); datedif = today - birthday+1]; Year ( datedif ) -1 ) I put it in as an custom function. This has worked fine, until I enlarged the network and put filemaker server 7 on and every now an then it will not calc the field. I have indexing turned off and at a loss for what the problem might be. Is there something on the server that need to be selected so that it calculates that field everyday. Thanks in advance. Randy :)

I have a database of photagraphs in FM10. Some of the photos are on my hard drive, some are on an external hard drive. A link to the photos was imported but they are not stored in the database. If the external hard drive is not present instead of the image, the container field reads: 'The file cannot be found:' followed by the name of the file. I would like to be able to filter the records to create a found set of only those photos currently available, that is, only those on the hard drive if the external hard drive is not present. I've tried various different ideas within a calculation field, things like isempty and referencing the container field and showing the r…

I have attached an extract from my database. I've successfully managed to implement a calculation field that calculates all the dates between the from and end date fields (courtesy of Andrew Persons, Excelisys from Brian Dunning's custom functions). Now I'd like to add a field which will calculate the week numbers which are between the from and end dates. I have managed this with some success by modifying the date range calculation. However, this calculation does not seem to work properly in all situations. In particular it does not work correctly when the from date field and end date are in different years. The date range calculation works flawlessly, but the week range …

Goal: In an Event Table the Repeating Field "Company Serial" displays the Serial of each Company via a Relationship matching the Repeating Field "Company Name". So far, no dice. I've tried defining "Company Serial" as: Number Field (Auto-Enter Calc) ... but all serials mirror the 1st entry Calc Field ... but only the first entry gets a serial Calc Field (with Extend) ... but all repetitions mirror the 1st entry Please see attached "Serial Errors" (the three repetitions should read 1004, 1061, blank). Any thoughts welcome, and my thanks in advance!

Hi! I am trying to create a field that will display text from a record based on finding a specific date in the record. I have this calculation written but I know it's not correct: If ( print::final_lasers_due = Date ( 2 ; 12 ;2010 );print::Project Name ) Basically I want it to search the records in "Print" to find a record where the final lasers due (this is a date) equals a specific date I enter. In this case 02/12/2010, and then show what the project name is in the field. In addition, and I'm not sure this is possible, I would like the field to display the project name for ALL records in print that have that date. Any help would be amazing!! Thank …

We sell many products. Some of them are a combination of other products. Each product has a cost and a markup. Example: Product A, Cost = 1.00 Product B, Cost = 2.50 Product C, Cost = Cost of Product A + Cost of Product B. This is the question. How do I perform a calculation from within the database like this?

I have a container field that I have linked a file to, selecting "store only a reference to the file". Is there a way to extract the filename that shows up in the container field?

I would like to try to reword my question. I have a calc field generated based on the input from other fields. I would like to check to make sure this calc field is unique, giving the user the ability to change the source fields until a conflict no longer exists. If I have 5 records, and the dynamic fields are 322-14 322-15 322-14 335-01 332-14 I would like the 3 "332-14" records to show a conflict until the user changes the fields that the calc field uses. Am I over thinking this? why does this seem difficult?

Hi All I have an FMPro 8.5 database with a field called "Category" which might contain a single word such as "Insurance" or "Food", or a phrase such as "Travel and Holidays", or more likely multiple words and/or phrases from a list of possibly 25 or so choices, each separated by a comma and a space, and in any order. What I need is to have a number of fields called for example "Insurance" and "Food" and "Travel" (there will eventually be about 20 such fields, and new ones may need to be added at a later date). Into each I paste a calculation as follows, which causes a "y" to be inserted in the field if the "Category" field of that record includes anywhere i…

I have a portal called cash out, and one of the fields is for a visa gift card number. We use a card swipe to enter the number. I used Middle (GiftCardNum; Position (GiftCardNum; ";"; 1; 1) + 1; Position (GiftCardNum; "="; 1; 1)-2) to capture the number. This has been working just fine, until I added a field above the portal to enter the customer ID number to create a new cash out. Now when I swipe the gift card the number will not go in to the GiftCardNum field. The field is blank and a different set of numbers are put in the field above the portal. When gift card is swiped: %B4793470269250179^GIFT/CARD^1104121100000001000000433000000? ;4793470269250179=110…

Hi! I have the following situation... Table A has x number of records - names of people. Table B is a join table between Table A and Table C. The relationships have an "priority" ranking associated with them. That ranking is stored in Table B. Table C is a list of goals that the people in Table A can perform. Table C has has a portal to relate to entities in Table A by displaying the values in Table B, in a typical use of a join table. For each entity in Table C there is a number, n, of entries in the portal, where 0 <= n <= x. A typical entry in Table C might be "Lower Healthcare Costs" and the portal entries might be something like: Name - Pr…

Hello, Please help me make a calculation that will combine the first or last, 1 or 2 characters from several fields to create a Code Name for an item. For example: Season: Fall Year: 2010 Type: Shirt Item_ID: 12 Code Name: F10SH12 I've been searching for an answer for an hour and figure that I don't even know where to look. The answer is probably very simple. Thanks! Heatherrd

Hello all, I have a database which stores information about buildings and it has container fields storing links to drawing files. The client likes it this way so they can click on the field to pull the file from the server. I need to be able to add a link to the file on a website I am creating for them and therefore I need to extract just the file path with a "file://" prefix for the link. when I calculate getText(container_Field_Name), filemaker returns an extra part ("file:filename filemac:/Macintosh HD/Users/filepath/filename). How can I retrieve just the filepath and filename parts of this in a calculation field? Thanks in advance

Hello. If field1 contains: "make my day" and field2 contains: "Clint Eastwood" I need a calculation field that will read: "make_my_day/clint_eastwood" I have no idea how to turn the spaces into underscores. Thanks, Ndugu

At the end of my invoice is a Total field (calculation) which adds up the various bits of the invoice. If the total is done with a calculator it differs from the filemaker total by 1 at the end. I need to make it so if the last number < 5 it rounds down and if its > 5 it rounds up. Any ideas?

How would you sum a field from multiple records during a specific date range. Would this be in a calc or does it need to be a script? Can you provide an example please? attempting to create a report dashboard but not sure exactly how to sum fields from multiple records within a date range.

I have a portal which lists the average rate of a product. Each row shows the average rate for a different year. I would like to add a field that calculates the percentage difference between the rate listed in each row i.e the % increase or decrease each year. I guess this is possible, but I'm not sure whether it has to be scripted or can be a calculation. Has anyone already implemented something similar?

hey I use FM9 but I can't calculate the number... why always like 1,10,11,12,....100,101,.....1000,1001 why can't consecutive number like 1,2,3,4,5,6,........99999?....

:welcomesign: I have two fields in the layout "Table".One field is "feet" and the other is "Centimeter".I need a code so that if I type a feet value (say 5.51) in the field "feet" its corresponding centimeter value(say 168) should be displayed in the field "centimeter" and if I type a centimetre value (say 168) in the field "centimetre" its corresponding feet value(say 5.51) should be displayed in the field "feet".I have tried a lot but nothing worked ..Can Anyone create a code for the above requirement please ... :runningcat:

I'm having trouble defining this in a calc. I realize I could do it with a loop, and setting a global field to concatenate. However, I was hoping for a better solution to "hard code" it. Example: foundset count = 4 output text = (0,1,0 | 0,2,0 | 0,3,0 | 0,4,0) so depending on the number of records in foundset, I need to add a string and add 1 to a specific part of that string. it will always be the second integer, following pattern 0, n, 0. for some reason, I can't figure out how to best do this. thanks in advance. -Kevin

I'm using google charts to create a chart based on data in the foundset. Using webviewer, I have a url such as: http://chart.apis.google.com/chart? cht=lc&chs=450x330&chd=t:7,18,11,26,22,11,14& chxr=1,0,30&chds=0,30& chco=4d89f9& chxt=x,y&chxl=0:Mon|Tue|Wed|Thu|Fri|Sat|Sun& chls=3,1,0& chm=d,4d89f9,0,0,12,0|d,4d89f9,0,1,12,0|d,4d89f9,0,2,12,0|d,4d89f9,0,3,12,0|d,4d89f9,0,4,12,0|d,4d89f9,0,5,12,0|d,4d89f9,0,6,12,0& chg=0,6.67,5,5 where I have substituted my data fields. the problem is the chart point markers chm=. I need to specify each marker counting up 0,1,2,3.... in the url above chm=…

I have a calculation "prev_st_1" field that is used in a relationship so that "st" can be calculated. because of the relationship structure that "prev_st_1" uses to calculate the first x records have no relations and thus return a ? I need this field to return a 0 if one of the referenced fields is empty, so that the other relationship can work. My current work around is to create a second calc field "prev_st_2" which looks at "prev_st_1" and if it is empty returns a 0, else returns the value. any ideas or is my work around as good as it gets?

I have a folder of images that I would like to import as a reference. Each image has a matching simple xml file - e.g. pic001.jpg has pic001.xml. Inside the xml file is the tag blah blah blah (there are several more that I do not need along with the usual xml headers). I would like to extract this line of text and add it to the picture in filemaker. I am hopelessly overtaxed with creating a xml style-sheet and was wondering, if the various text functions of filemaker can handle this? I have tried, but am not able to come up with something that works

There is a good description of how to set up validation in Filemaker in order to prevent dulicate records being created here: http://fmforums.com/forum/showtopic.php?tid/176348/post/204677/#204677. This post is concerned with the cases in which even these methods fail. We have implemented option 1 in the above post, defining a unique key for each record and using the Unique validation requirement to ensure that no duplicates are created. However, we still occasionally get duplicates being created. Does anyone have a list of scenarios in which this can happen? In our case, the records in question are all in "incidental" join tables and are created from a parent r…

Hello, I have a simple calculation that changes a field based on a difference between current date and the creation date of the record. The calculation evaluates some fields in the record, and if those fields contain a certain value AND the date difference is greater than x, another field is changed. However, when this calculation changes the field, the modification time and date of the record are not changed. This is an issue for me, as I need to export records that have been changed since the previous export. The records that were changed by this calculation don't get exported, because the modification was not triggered. I have searched the forum for a soluti…

I have a script that runs when a user logs into a database that searches for records that have been "checked out" for more than days. The check out feature simply adds the date the record was checked out to a field and turns a selects "Yes" of a Yes/No radio button. The problem is that I can't seem to get the search criteria right to find dates that are 90 days before the current date. I have entered the calculation as Get ( CurrentDate ) > -90 but it's not finding the correct records. Can anybody tell me what I'm doing wrong? I would appreciate any comments. Thanks!

Hi Been using the time billing solution and cannot seem to be able to create a calculation that totals the hours worked by date. My purpose is to add a field to the form that shows me how many hrs I have worked that day. For example, I work one date on item A for 2 hrs, B for 2.5, C for 3. My total for that day = 7.5 hrs. Next date, different items and hours for each, but think you get the drift. Seems pretty basic, but I cannot seem to resolve. Any thoughts would be kindly appreciated, thanks for your help. Cheers...

I use FMP 10 on both a Mac running OSX and a PC running Vista and I'm essentially a novice when it comes to calculations, scripts, etc. I have several databases that have container fields for images. In the past, as I've created records, I've just used "Insert Picture" to insert a reference to the image for each record. Copies of the images are stored locally on both the Mac and the PC (though I would like to eventually get this to work without that redundancy). In the past, when I was running an earlier version of FMP, I was able to host the database on the Mac and access the database from the PC remotely over our wireless network. For whatev…

Hi Guys I Have some text sotored in a normal text field with lets say the following data within it: There will be a reception in the Library at 10.00am, and from there we will move to the School of Arts for the opening. This will be celebrated in the building itself, after which you will have an opportunity to see pupils at work within the new workshops. Lunch will be served in the Dining hall. ---End Of Page 1--- another bit worth of Text come in adasdssadsad asdsasad asdsa as asdasd asdsadas asdsaadsd ---End Of Page 2--- another page worth of Text come in adasdssadsad asdsasad asdsa as asdasd asdsadas asdsaadsd ---…

I have a field that is for distances. The data gets imported from a non FMP database. The other DB puts Mil at the end (for miles) so 100 miles shows up as 100 Mil (space and Mil - 4 characters). I need to lop off the Mil at the end. I was going to use a script, and use the set field, and I was thinking it would be something like Left (text) or Right (text) kind of thing, but neither seems to fit, because I could have any number of characters before the Mil (1 mile or 1000 miles)

We are a non-profit with a database of all our donors. I believe this problem is tied in with the new year, i.e., going from 2009 to 2010 because that's when it started. All fields with automatic dates have changed all dates in 1999 and earlier to 2099 (or earlier). In other words, a donation made 1/2/1999 is now listed as 1/2/2099. Same going back to 1994 when we created the database (now 2094, etc.). Years 2000 through 2010 are good. This has enormous repercussions. I can go in and manually change the donation date from 2099 to 1999, but that will be very tedious. My platform is Mac OS X. Thanks to anyone who can help.

Hi everyone! I can't seem to get this one but it seems as though it should be a simple calculation. I'm trying to set up a calculation field that references a number field and returns positive value if it's negative and a negative value if it's positive. Here's my approach which is apparently wrong: [color:blue] Case( Amount < 0 ; Filter ( "1234567890." ; Amount ); "-" & Amount ) I think it's wrong because when it references an amount like "-10,000" it returns "10". What happened to the other "0"s? Thanks in advance.

I'm ready to do a fresh import into a live database, but I'm trying to reset all of serial values back to their initial value of 0. Does anybody know how to do this?

I'm sure some of you wonderful calculation guru's will be able to help me easily with this! Yes I am trying flatter you all Here's the situation. We have a customer who pays up front by a PO number, 10 call outs to repair printers. Now, in order to see how many they have left, I would like next to the PO number field to add a field that counts all the records with the same value. Is this possible?

I import an excel file to gain pricing info etc. Currently I have Round(field):1 this take all the weights and rounds them up to a single decimal place. Works great. 10.37 = 10.4 The issue i have is if the weight comes in as 10 only i need to put in a zero at the end. Any thoughts?

Good Afternoon, I have a problem with "Insert Object" on a MAC. I'm using the insert object to attach an excel sheet to a container. using a PC-I right click go to Insert Object-Create From file-Browse my file, and check the box "display as Icon". what this does is it gives me the ability to double click on the box and my excel file will open up--I love this feature, BUT it won't work under a MAC... Does anybody know why? or a way around this. I have a solution that PC/Macs need to be able to open up that excel sheet that's inside of a container. Thanks

I ve been trying to come up with a formula in order to use portal information. Said portal displays info just fine but now I need to use that info in the very table where that portal is located. In other words, I can copy and paste the portal info into the table field that contains a formula that returns a result ,manually with no problem. I just want instead of copy and paste that the field gets portal info automatically. I hope i am explaining myself correctly. Thanks in advance!

I have an old database (9 years+) to update which seems to be programmed inefficiently. I understand the issues with repeating fields but feel they may be appropriate. Currently the database tracks various functions for learners at a small college. One of the related tables just looks after learner paperwork and contains contact details plus the dates of all key documents most of which occur once per learner. One document which tracks formal review meetings, occurs up to 45 times and is currently held in 45 unique fields then forecast in 45 other fields which each have Case calculation which is very slow if the learner has a lot of review meetings. Essentially th…

I find the 'auto enter last visited' option erratic. Sometimes when I make a new record, all the fields with this option perform properly. Other times they all remain blank. I've checked the obvious - that the record I start from has values in the fields - but I can't discern any pattern to the problem. Can anyone suggest what I might be missing? Thanks

Please help. I have attached a sample file. Please see below the 2 requirements. This is for helping children and ensuring their height does not decrease as each visit to the doctor. If their is a decrease in height, it will be flagged. Requirement 1: The program is to flag any record in which the value of height decreases for the same patient as date progresses. (A patient should never have a height less than what has been recorded in the database). Requirement 2: If the user enters a height value less than what has been recorded for the same patient, a pop up message will alert the user to please check data and will not allow the user to proceed u…

Howdy FMP Gurus! This is obviously my first post here and I suspect chuckles will be forthcoming from many of you shortly. I'm hung with what I thought would be an easy calculation. SCENARIO I'm trying to get a one line CSZ field into three separate fields. I've read and searched a number of posts here and elsewhere (2 days worth) but have yet to tackle this calc(s). The text field is currently formatted like this: "San Mateo, California 01234" There are no returns, and currently all Zips are five characters long. There is a space between "," and State Name, as well as a space between State Name and Zip. I referenced "San Mateo" because, indeed,…

Not sure if I am posting in the right place here...but... I have a database that reads an imported Excel file which comes from our main management system...the issue I am having is I have a "Removal Box" basically for tracking exceptions...the Database inserts the item number, what store, and the reason for removing it from the list into this field, literally hundereds of lines. Because we have different people inputting the reasons for this, there is no telling how they will spell it, if there will be spaces, or anything... The format is: 12345 - Store Name - Reason Again, any of those values could be any number of characters and will almost nev…

I need to change the Titles in my database from ALLCAPS to Proper Case. I already have in place the following: Trim ( Upper ( Cluster Title ) ) I know I can change this calc to Trim ( Proper ( Cluster Title ) ) Here are my two questions: 1. This Calc requires that this field be entered and submitted before this calculation takes effect. I have 30K records, so going one by one isn't feasible. My current plan is to create a separate database with this calc in place so that when the content comes in it will be formatted as Proper case and then Import this back in. Is there some way I can "touch" the field in each record so this takes…

I am using the formula below to list the found set cities and email the results: Substitute ( List ( City ) ; ¶ ; ¶ ) When the script runs it populates the email and lists the cities on seperate lines: Boston London Tokyo However when the email is received the format changes to: Boston London Tokyo How can I force it to add ¶ at the end of each value?

I have a calculation that basically lets me know when the content of a field is changed. Case ( Length ( "a" & • Bullet 01 & • Bullet 02 & • Bullet 03 & • Bullet 04 & • Bullet 05 & • Bullet 06 & • Bullet 07 & • Bullet 08 & • Bullet 09 & • Bullet 10 & • Bullet 11 & • Bullet 12 & • Bullet 13 & • Bullet 14 & Product Title & Cat Cluster Title & Undercopy ) > 0 ; Get ( CurrentDate )) My problem is that when i do an import into the rest of the database, it changes all the existing dates even though the import file does not touch these particular fields. When i do the import - It "up…

Hi, I have encountered a big problem in the Case function, that I hope someone has an intelligent solution to... I am start to suspect a bug in the great FM.. I am programming a price enquiry tool for book printing, and needs to set up multiple conditions to get a correct price. The first Case function is regarding the amount of pages in the book, more or less x number of pages. The result consist of another Case function, which takes the amount of books to be printed into consideration when calculating the price. So, lets say that the first case function notices that the number of pages is less or equal to 50, then the second case function needs to se…

Ok, this elusive calculation seems to be hiding from me! I would like to show on my layout: "1 of 15 out of 100 records" for the end user during a find request. Whenever I use Get (FoundCount) , even in find mode, it shows total number of records, not just the found set. What am I doing wrong?

I'm an extreme noob that's been using Filemaker for maybe a month now so I'm having a little trouble with this calculation. I have a parent table in my database that contains a child relationship containing a field with a specific phrase I need to be able to count the occurrences of. I then want to display the total number of occurrences on the parent table. How would I do this?

Hi. Im generating a report in Preview mode. At the bottom of each page I want it to say Page X out of Y Pages Getting X is easy with get(PageNumber), but how can I calculate the total number of pages, Y, in advance to display? Thanks

Hi All, I am just starting work on a "Action-Trigger" system. Concept and Simple Example: Have a table of Action-Triggers: Action: Key to SWITCH script (If-{ElsIf...}{-Else}-EndIf block) Trigger is the Calculation text that I wish to apply the Evaluate function to to return either "TRUE" (do the Action) or "FALSE" (don't do the action) I am having these be text values so that I can extend to things like "PROMPT" (see example) Simple Example: Motel with Sr. Citizen discount of $50 for those over 65 yrs of age. In my Action Trigger table I add this record (very pseudo-coded so that the syntax is easier) Action: Sr.Citizen Discount Trigger…

I'm trying to create a calculated field that will display a text string description of a number in another field. For example, 124.1 would result in "One Hundred Twenty-Four and One Tenth." I could swear I'd seen a very similar example on these forums (for a checkbook type application I believe), but my searches didn't turn up anything. Before trying to reinvent the wheel, I figured I'd see if anyone could point me in the right direction. Any help would be greatly appreciated! Edit: still using FMP5.5

Good morning all, I am new at this, and need some help converting names. I would like to convert "First Name Last Name" to Last Name, First Name. Thank you in advance. Filemaker Noob

how can i write a calculation the will tell me that if a portal is empty?

I need to figure out a calculation giving a results based on dollars due each month when the results may or may not over lap years. I have a pledge amount of x due either monthly, bi-monthly, quarterly, semi-annually or annually as pledge type. A start date field is established and the due dates are determined by the pledge type, which gives a number of payments. So far I've determined what is due each month from all pledges, but with a pledge overlaps a year it's throw in to the summary as well i.e. Joe pledges $200 a month beginning in Sept. 2009 and running through Aug. of 2010. Sam pledges $100 a month beginning in April of 2009. The desired results for Sept. 200…

I have 2 timestamps in 11/10/2009 15:16:42 format and trying to figure out a calculation that will count hours/minutes between the 2 timestamps removing weekend and overnight hours. So only business hours.

Hey I am creating a contact management.Now I have two fields one is "Birth Year" and other one is "Age".I need a calculation so that when I type the Birth Year in the field "Birth Year", the Current Age should be Automatically typed in the field "Age". Please Help....I know You can Do it Guys.. :help:

Hey Thrill seekers! I'd like to create a calculation, could be a summary field as well I suppose, that totals records that match a specific criteria. I've a summary value that of course totals the "Risk" field in a database, but I'd like it to only total the "Risk" value in records that also have an "Open" value in another field in the same table. Any Ideas?

Hi all, I've got two short questions I've been thinking about recently which I'd like to run past some of you more experienced members. 1) Case(), if more than one case is true, it will display only the first. How would I calculate if I want to show all that are true separated by "/"? e.g Case(3+3 = 6; "A"; 2+4=6; "B") -> "A/B" 2) Is there a way to repeat a "word" based on another field's number/count? e.g. number = 3, text = "word word word" Thanks! fmphysio

Hi All and HAPPY NEW YEAR! I have multiple merge fields I need to add to a document I created in FM. BUT not all of the merge fields will return text, nor should they. My problem is I am trying to create a calculation that will show the merge fields with text and add a comma and proper spacing if there are more than one. I found this on another Filemaker site: Example: Create a database with the following fields: Address (Text) Suite (Text) City (Text) State (Text) Zip (Text) AddressSpace (Calculation,Text) = If(IsEmpty(Address) and IsEmpty(Suite), "", " ") CSZCommaSpace (Calculation,Text) = If(IsEmpty(City), "",^", ") CSZSpace (Calculation,Tex…

Hi - THis was a tough subject line to come up with. Here's what I'm trying to do: I have a list of projects with start/end dates, but I want to display them in list view showing check boxes for each month of the year (or each quarter) that those projects occupy. The idea being that scanning the list visually, you can see for a given month, which projects are in progress. The incredibly clunky way to do this would be to have many fields for each project for each month: 2010 Jan, 2010 Feb, etc, and then use calculations to see if that month is between start and end date. But then I'd have to have new fields as additional years are added, and clearly, this is not the …

I'm running into a problem reporting sales by month and counting the months with sales. The report has a sub-summary part that totals invoice detail rows using a month(invoice_date) break field. The 'body' is the invoice details, which we actually hide on the report. Some customers purchase only a few times each year and the challenge is to count the number of months (we want to use that count in various calculations, like avg-sales-per-month for that customer). The 'count' function only counts the records in the body (the invoice detail rows). So how the heck do I count the resulting months? The report breaks each month, then breaks again for a new customer. …

I'm not sure if this is the right place to post this, but if I knew the answer, I would probably know where to post I have a script that has about 150 steps that ultimately posts a work order and creates a related invoice. There are many scripts that create invoices, all with Show All Records/Show Omitted Only/New Record-Request then goes on to add the new InvoiceID to a variable then creates the related line items. All the scripts work fine except for one. Anytime I'm managing fields in the Invoice table (I don't know if I may be editing a field or even possible working in the ERD) the New Record/Request step gets skipped and the script goes on to create the …

i have 2 fields one is "religion" (text) and the other one is "Parish or horoscope" (text).I want a calculation so that if i type "christian" in the field "religion" then the field "Parish or horoscope" should be automatically typed with "Parish" and if i type "hindu" in the field "religion" then the field "Parish or horoscope" should be automatically typed with "horoscope".. can any one help me please..

I have a file with values such as A1,A2,A3,A4...A200. I want to create a "Sort by" field that will contain the corresponding number of " " spaces based on the number in the value. Ex. For A6 I want 6 spaces in my "Sort by" field. What calculation do I need to do this.

I need to track mileage from one week to the next. * I need to initialize a record with the current milage (beginning odometer) reading. * One week later, enter milage for the week ending in a new record (ending odometer reading) and display the beginning odometer reading from the previous week (record). * Calculate the difference between the beginning odometer reading and the ending odometer reading. Make this value available for further calculations. * Repeat this process each week (a new record for each week ending) using the previous weeks ending odometer reading as the current weeks beginning odometer reading.

I an creating a TimeSheet table, but have a question. If i set the start/finish time fields as TimeStamp, and use the DropDown Calendar, a Calc field set to Number, gives me the correct hours between the start and finish times[(Finish-Start)/3600]. However, as the Dropdown calendar exits the field when a date is selected, i need to re-select the field to manually enter the time. Is there a way to use a number field to enter the time as (0700 & 1500 etc) instead of having to re-select, and utilize a date in another field? If i choose a edit box instead of the dropdown calendar, i get a revert field alert, as there was no date entered. I am afte…

I am posting this to help others. I have just spent about two hours pulling my hair out trying to add 7 days to a date field, I was trying to auto enter in a date field. When what I should have done is defined the field as a calculation unstored of type date the calculation was 'MyDateField+7' There easy when you know how, judging by the amount of posts about date calcs Filemaker could do a little more to make this clear, if this saves anyone a considerable amount of time send me some money. Thanks

I have a field that contains a variety of data. I need to know the format for a function that would evaluate the field for the text string that is somewhere in the field (not exclusive) but is within the field. If the string is present within the field, than "x" if not than "y"

Does anyone know if it's possible to turn off record last modified timestamp fields auto-updating during scripts? I'd like to batch process a set of records for a simple checkbox field update but I don't want to lose the last modified data which reflects last *user* modification of the record data. Thanks

Hi FMP guru's, Happy New year! First time poster here, though I've been looking through some of the posts over recent days while trying to build our very first FMP databse. And have found the info to be very helpful thus far! Although, I can't seem to find an answer to what I believe may be a very simple problem for most of you! I have a simple Database ID field, which we'll use as a cross reference to part numbers, serial numbers etc. at some stage in the future as the DB grows, but I'll be darned if I can get the ID to show me any figure other than a simple "1", "2" etc. Just to be a little different, I wondered if I can get it to show "XY10001…

Ok, I've searched the forum, and haven't found a solid answer for this question. I have two dates, beginning and ending, and can simply find out how many days are left between the two in the same month. But, how can I go about scripting it to find out the same if say the startDate is 12/31/2009, and the end date is 1/6/2010? where the months change, and the years? Thanks in advance for the help.

Howdy, That another user is currently using.. This starting error now and them.. maybe its due to developing, restarting FM and or doing so on WITHOUT a reboot is the only thing that fixes it. Its notice say 'Can not share another user is using a file or logged on.. '' and i am on a single computer I just continue through it... then can do everything.. sometimes again, or if I come up to regarding some permission to modify something.. i might get the notice again, but can continue on.. REBOOTING it fixes... When clicking my X to exit... I do have checked to Close= run my close script... with debug on... its do so Any ideas?

FM function WeekOfYear return wrong value for year 2010 for example. 1 Jan 2010 is week 53 (year 2009) not week 1 and 8 Jan 2010 is week 1 not 2. Normally the 1st week of the year is the week when 1Jan is after Wednesday. Or I'm wrong?

I just tried the following, posted elsewhere in the forums: Case[DayofWeek[OrderDate] < 5 , OrderDate + 9 , DayofWeek[OrderDate] = 5 or DayofWeek[OrderDate] = 6 , OrderDate + 11 , DayofWeek[OrderDate] = 7, OrderDate + 10,""] But this only works if there are only 7 days between. I actually need 12 business days starting from my order date.

Hi there Forum My question surrounds the Substitute formula used to display name full name of patients in a medical database. Patient records were filed in "Title First name Middle name Last name" format. Now the clinic wants them filed as "LAST NAME, First name Middle Name" format. How do I get the "Last name" to appear in capitals and a comma directly after to separate from First name. Substitute ( List ( Last name ; First name ; Middle name ) ; ¶ ; " " ) The system in question was born with the generous help from these forums and it is wonderful to be able to revisit during redevelopment! Kind regards Tim

I am not sure what I am doing wrong. I have a global variable in a table. That Global Variable triggers a calculation..unstored... The unstored calculation triggers a summary field (Count of). When using a layout from that table this works fine. But when I try to do this through a portal on a different table.. when I change the Global Variable... none of the information refreshes. I think the issue lies in my summary field... it isn't refreshing in the portal... Is there a way to force it to refresh when I change the global value?

I have thousands of email addressses in a database. I need to add a website field. I cannot figure out how to write a calculation that will strip away the users name and the "@" symbol keeping just the domain name.

Hi All, I have a list of 80 products each product in a separate field. My database consists of customers that each have to choose a handful of products for their companies so each contact record contains what they have chosen. Radio tagged per record. Now I am trying to create a summary sheet per company and would like to include the products they have chosen to be listed on this summary sheet. I have tried every calculation I thought it may be to get this information into one field. If I use the insert merge field it blows my document on two pages. I tried to create a calculation using "Case" but it would only read the first field chosen. Before I rewrite the entire th…

I am attempting to put together a database that will report figures such as YTD Return, Median, average monthly return, compound yearly returns, Skewness, Kurtosis, and other results such as Trailing 12 month returns. Does anyone have an example file I could look at that has functions like this set up so I could learn what you did to write that code? I am trying to analyze data that includes a date, a company, and their performance (in a percentage) for each month. LIKE THIS: Date Year Company Return 1/31/09 2009 xyz -0.56% 2/31/09 2009 xyz 4.53% and so on...

I am working with an external Java app that I have to open and pass parameters to. It works great on windows, but I am having difficulty making it work on Mac. Here is the code for windows Send Event["aevt"; "odoc"; "cmd /d /c java -jar " & Quote ( "C:Documents and SettingsNickLocal SettingsTemp1OEtempDxcUploader.jar" ) & " -file " & Quote ( "C:Documents and SettingsNickLocal SettingsTemp1OEtempdxupload.txt" ) & " -password " & PT_Docs::DX_Pass & " -source EDI -url https://somewebsite.com/dci/upload.svl -username " & PT_Docs::DX_User & " -responsefile " & Quote ( "C:Documents and SettingsNickLocal SettingsTemp1OEtempdxresponse…

Im not quite sure where to post this... We have a situation that I am not sure how to handle in file maker. We have a huge number of jpegs. Each JPEG is named for the sku or the assigned SKU concept number. For example, we have JPEGS named: 12001.jpg 93000.jpg K83923.jpg 182332.jpg 55332.jpg K22112.jpg Each jpg is stored in a set of organizational folders. we have a folders broken down by the first three Numbers 10000 11000 12000 13000 14000 ... 50000 51000 52000 all the way up to 99000 We also have a folder for anything starting with K. You can see this is a HUGE number of folders. 10000 to …

Since I can't figure out how to add an attachment, I'll try to explain what I need to do. I've got a script that takes the user from one layout to another if an account number is entered. The account number needs to be five numeric digits. I'm trying to put steps in the script that give a warning, prior to going to the next layout, that the correct entry wasn't make. Since there is a similar section on the first layout that requires the first couple of letters of a last name, I think it's important to let the user know he entered alpha characters where numbers should have been without discovering the fact after the script has sent him to the other layout and give him …

I have a table called 'Products' and I have created a relationship with itself in conjunction with a portal and a drop down list that enables me to pick related products to that product ie the main product might be a mobile phone and I can just click on a new record(s) in the portal row and select any other product(s) that might relate to the phone (carry case, in-car charger ect) I need to create regular csv files with my products on them for uploading to a new website. What I require is a field that contains all the related 'item codes' (which is a field in my Products table), in my Products table. any ideas? - i've tried a calc field and a Lis…

I have done this before but for some reason it is not working and I can't figure out why! I'm importing records from an old database into a new one using FM Pro 10 Advance. The old DB did not have an address table and the new one does. I have created a field that lets one choose which address type it is, i.e. Main, Billing, Shipping, Install. I'm importing records from the old DB in groups. So the first set I import the addresses that I know are the "main" ones. After I import that group I want to do a find and replace of the currently empty address type field and set it as "Main". I will do the same with each new group import, just changing the address type field to the …

I've got a field that has to have 2 numeric characters and 1 alpha character i.e 06B. I can't figure out how to set the Validation. Do I need a script for this field? If so, any pointers for this newbie would be appreciated.

I want to restrict access to a tab panel so only certain users can access that tab control. I see where i can restrict access to fields that are on the particular tab panel or a whole layout but I would like it to be so when they click on that tab control it doesn't allow them to see it... is that possible in Filemaker 9. Thanks in advance. Caroline

I want to create a calculation that will explode the contents of a field into an array, ie. field data = smith result = s sm smi smit smith any suggestions?

I have a layout with five number fields. I would like to have appear in each field in a record of that layout a random number from 1 to 50, making certain that no field in the record has a repeat of a number from another field in that record. In other words, each record contains 5 unique numbers, anywhere from 1 to and including 50. Can anyone assist me with a formula that accomplishes this task?

(Using FM PRO 10, Windows Vista) what I want to happen is for a field to do this: Count (db1::advisor_key when related records are (db1::date_field >= Mindate and db1::date_field <= Week 1) So I have two tables. One that is nothing more than a data dump from an excel file. the advisors are using a help desk service and I am measuring their usage. Each is represented by the unique advisor_key value and all of their help desk chats are listed by date. My second table is a portal to the list where I can see when they started using the service (MINDATE) and their last usage (MAXDATE), I can even show total number of records, however I need to break down t…