Calculation Engine (Define Fields)

Howdy I am converting some ISBN book numbers to the new 13 digit format. I will have a 12 digit string, and I will apply a simple calculation to each INDIVIDUAL digit, then add them up in a calc field. I think this will be some use of the Left () or Right () functions, but I can't get it right. Basically, I want to grab the 1st digit from the left, then multiply it times 3, and set that into a separate field. Then I move on the 2nd digit from the left, x 1, then the 3rd x 3, and so on. Or perhaps I can put it all into one long function, which would excellent. Help appreciated in nailing down the proper syntax here.

I am working on a database that will give reports from a POS system (cash register). It's been a few years since I've done any filemaker work and I can't seem to find an answer to my dilema. Last version i used was 6 or 7, before they added tables. Problem: FIELD1 This field gives the total amount of money the person has paid. FIELD2 This field is the section of the restaurant that the person is in. The data is numbers. ex "1"=section 1, "2"=section 2, etc... (the wanted data isn't that simple, but you get the idea.) I need a one page report that shows how much money, during a certain period, was received from each section. First, I'm assumin…

I'm going to give this a go--and give as much background as possible without boring you. Table I-Client Data Table II-Job Data (Includes charges and payments) I run reports/invoices that have the current due and running balances for the found sets. If I run June's report and there is a [balance due] at the end of that month (or whatever timeframe is involved), then I would want to be able to include that [balance due] plus the new balance due in the July report--but without showing all the June records. I had this working in FM7 but in a VERY round-about way with many many fields keeping track of a balance and lots of if/then statements in calculations th…

Hi All! I am here yet again for all your wonderful help! *I mean that sincerely! Anyway, I am trying to calculate a field depending on a date range. I searched up & down for an answer which makes me think its SOOO simple that no-one has needed to ask. If thats the case, oh well, here I go! Here is the calculation I created: If ( Cancellation Date = "11/1/2007" & "..." & "3/30/2008"; Total Cancellation Amount * .25 ; " " ) I have 3 different sets of dates that I need to calculate from. I have a cancel date field. Not sure what I'm doing wrong? I even tried changing the cancel date field to text. Help!

Hello, I have a portal with a field containing a duration (time). I would like to make a sum of this field for each entry of this portal, and forbid the user to enter duractions so that the total of the portal would be more thah 24 hours. Is this possible ? And how do you suggest to make it ? The problems I encounter are - The summary field does not automatically update, I need to change record and get back, or close the database or so. - In IWP, the updating of the summmary field seems ok, but the validation on the duration field seems to happen BEFORE the update of the field. Which means it doesn't really validate base on the new total but base o…

I have a need to lock a text field after text is added by the user, so that it can not be modified by anyone. Do I need a script to do this. What would be best is when the user clicks out of the field it is saved and can not be modified. Any advice?

Anyone know of a way to reset the Get(RecordID)? I've got a table that is extensively "wired" with a million relationships that does a bunch of processing (creating records, process and then delete) and the RecordID is up around 500,000,000. The only problem is that I'm using XML requests to get the data, and those large record ids are just adding a lot of extra data to the XML output. Plus I'm wondering if that large rec id is slowing down new record creation? I can easily delete all of the records in the table, but that doesn't seem to reset it, nor does doing a "recover", or "file maintenance". I'm open to any ideas! ...even if there is a program that…

Hello All you Brainy Filemaker people. Can anyone please advise me how to do the following: I am trying to ask filemaker to enter a new text string depending upon the value entered in a previous field, I have found the following works for a single value, but how do I tell it to enter something else if the Serial Number was 60 ?? Substitute ( Serial Number; 50 ; "handmade" ) Any help is most appreciated... thanks for Looking.

Hi people... I read some post in this section, but I can't find out the way... Here's the situation: I have the field Customer_ID and the field Order_date . I want to make a calculation field that tells me how many costumers called me. I mean, if, for example, the costumer 1, 3, 10, and 11 called me, I want the calculation tells 4, no matter how may times each customer had called me... I mean, maybe the customers 1 and 10 called me 4 times, and the others 7 times, but I only want to find out how many customers called, not how many calls they made. I hope my English would be understandable enough : Thanks a lot people

So, I have getsummary(student_count; department) working for when I have a subsummary section sorted by department. But if I then want to sub-sort by degree, I get wierd results. Is it even possible to use getsummary when sorting by multiple fields? Which field do you use for the break field? The last one or first one used for sorting? -Jeff

I want to count how many records in the database have a certain value in a certain field and display this on my layout. How would I do that? Thanks in advance

I have been importing .jpeg files into container fields as a file reference. I have approx 13k jpegs. I am adding .jpeg files to this folder on a regular basis. Is there anyway to import only the new .jpeg files without filemaker individually importing all 13k photos? It takes filemaker an incredibly long time to analyze that many pictures when importing. Is it possible for filemaker to import based on file date? maybe with a calculation?

hi, What is the best way to do a calculation to figure the sales tax based on 2 value lists, one for the type of project, residential or commerical and one if it is new construction or renovation. Maybe better explained, if the project is a residential, then only the materials are taxable and not the labor whether or not it is new construction or renovation. If the project is commerical and is a new project then it is taxed the same as a residential project, materials only. If it is a commerical project but is an existing building but there is renovation being done, then everything is taxable. Materials and labor. The sales tax is also based on what to…

Well, here it goes. this is my 1st attempt in posting a question for help. I hope I am posting this in the right forum. What I am trying to do is to pull information from a found set of records and calculating the quantity from each repeating field so I know the quantity of individual items that are needed for future jobs. Example; there are 10 invoices each with some of the same items and also different items, I want to know the total quantities of each item in a seperate layout for all other 10 invoices. I have tried summary fields with no solution. I'm stuck. Any help would be appreciated. lj

I have had a search around and have found custom funtions to return a fiscal year from a date , but I require something that can return the month number of the fiscal year. my fiscal year starts on March 1st so that would be month 1 ect. I have a field called month_number and Year aready in which to calculate from any help would be appreciated

Hi Been searching the site for this answer but can't find it so I'll ask here. Please forgive if I'm in the wrong forum. I have a date field I would like to pull the QUARTER and the Year. i.e. Date is May 5th, 2007. I would like to have a return of: 2nd qtr. 2007. I have been trying the "if" calcs but not having any luck. Thanks for your time. :shocked:

Alright, let me see if I can explain this. I have fields for both a Month and a Year for when someone Joins. Lets call them Join_Month and Join_Year fields. I also have a Yes/No boolean field as to whether or not they are active. Lets call it. I need to do reports that show who is a member for each month. For example, let's say its July and there are 30 members on the report. Now lets say 4 more people join in August. So for the August field in the report, it would show those 4 + the other 30 that have been members already, showing 34 members for August. On top of that I was wondering if it was possible to automatically generate new months as they come along…

Hey everyone, I know I have seen this done but I can;t find the sample file. Can anyone show the proper syntax for multiple set fields in one calculation? I am trying to learn how to tighten up mt scripts. Can the same thing be done with Set Variable? Thanks, Steve

I am trying to figure out how to make a calculation field.... I start with Balance = Job::Price - Invoices::Amount That gets me close but I need to figure out how to only include Invoices::Amount where Invoices::Paid Date is not null.... I'm sure this is possible... Can anyone help?

I was very excited when FM introduced global storage on calclations, oh, way back I think in FM7. However, I have yet to make it work. How have you used global storage on a calculation successfully? It seems that any time I've tried to use it, whatever value it has at startup is stuck there for the entire session and never changes regardless of the dependencies it may have. I found this particularly frustrating. What is the point of having a globally stored calc, if it never changed based on the dependencies. That puts you back to an UNstored calculation on every record to achieve something that was more dynamic--say a calculation that references a global that…

Hi, I'm stumped. Hope this is the best place to post this. In the process of converting an invoice layout from repeating fields to using portals, I have been unable to calculate totals for different line item types, ie. plants, hargoods and labor as I was able to using the repeating field format. The following calculation gave me the info desired and I need to be able to duplicate these same total using portals. If(Left(code;1)="p";line price;0) gave me a plant total If(Left(code;1)="h";line price;0) gave me a hardgood total If(Left(code;1)="l";line price;0) gave me a labor total. I need to be able to seperate these by these group for sales tax re…

I am trying to Create a Flag, as to notify me when a expiration date is flagged with some text. The Calc Statement I am using is If ( Get ( CurrentDate ) > Nurse Xdate + 340;"Expiring";If ( Get ( CurrentDate ) > Nurse Xdate + 365;"Expired";"") ) I am doing something wrong. Thanks in advance.

This may sound a bit odd, but can someone suggest a calculation to concatenate values of multiple fields in related records into one string in a single field. For my purpose this would be 5 fields of up to 80 characters each in each record. There would be up to 150 related records. So at most the field would contain 60,000 characters, which I think FM9 allows. Thanks

How would I parse out an array to get the current price of a product that has multiple prices based on Effective and End dates? ie... Price file Effective End Price 1/01/2005... 12/31/2005 $31.00 1/01/2006... 12/31/2006 $31.50 1/01/2007... 12/31/2007 $33.00 1/01/2008... 12/31/2008 $33.50 1/01/2009... 12/31/2009 $34.00 I tried working with an If statement to test the current date against the Effective and End dates, but I am not looking at it as an array.

New user and first-ever post, unsure whether I'm using Jaguar/Panther/Tiger/Leopard but know I have a MAC OSX. I would like to know explicitly how to create new bookkeeping fields such as DATE, # RECEIVED, INVOICE, PRICE, CONTACT, FOLLOW-UP DATE. I would want to add information into each of those fields when I make sales.

Am working with day/date calculations in which the length of an assignment (working period) may be 1.5 days. I have not previously had cause to deal with the decimal element in any projects I have done to date but now need to. Any help or insight would be greatly appreciated. Have been trying unsuccessfully to locate info on the right elements to modify my start and end date and duration of assignment fields so as to incorporate the decimal elements of my project requirements. 1. To calculate an end date based on the start date field and time assigned to an assignment field, I am using this calculation in a script: Let ( [ Array="23456034560145601256012360…

Greetings Would love to hear suggestions as to how to solve the following problem: Everybody prints to a networked printer. As such, it is imperative that we have stock of all consumables at any one time. I thus created a subsummary report which identifies the unused consumables in stock. The next step is to identify those consumables that aren't in the subsummary report so as to flag those as "super urgent" stock to be ordered. My mind is completely obsessed with combinatorics and I cannot see another way of identifying what consumables are missing from the subsum report. I'm looking for a change in direction! Surely there is an easier way to …

Hi, I have a calculated field that is doing something a little odd: the calculated field is showing as zero for particular records when it should be showing a value of above 0, and does in fact give the correct result in other records where the values used in the calculation are the same! Here is the calculation: If ( IsEmpty ( fieldA ) or IsEmpty ( FieldB ) or IsEmpty ( FieldC ), "", 170 * (( FieldA * 0) ^ -0) * ( fieldD ^ -0) * Case ( fieldE = "X", 0, fieldF = "Y", 1 , 0 ) * (( FieldB * 2) ^ -0) * (( FieldC / 10) ^ 0) ) Equals zero for some records and the correct result for others, even when the same field values are used in the calculation. …

hello all... its me again...:-/...i have an issue and need your help to fix it. i have 3 fields [2 date type, 1 radio type] and i want to make sure once a date is entered into field2, user cannot change / modify it until radio button is clicked [on/active]. only when radio button becomes active/on, user should be able to change date into field2. currently field 2 contains this piece of code [Let(Trigger=Field1;"")] which basically means if a date is entered into field1, field2 becomes empty/blank. but my only issue is with field2. i want to make it disable once it has a date in it and radio button is not on/clicked and when radio-button is clicked/on, field2 becomes …

Hey Guys, once again i enlist your help... I have a field that is exactly the same on 3 records.I have another field on those records that say if the individual record is "opened" or "closed". How can i set up a calculation so that it will first count how many of that first particular field are the same.... then add a number when all of them are "closed"... any ideas?

Hi! I have a simple database for my own use, constructed in FMPro 8, with everything in one table (like an old-fashioned flat-file database!), in order to control an upcoming event I'm responsible for running. I have a number of fields with either a numeric or an alphabetical value in them, and these obviously are present on every record. Let us say that one of these fields is called "InviteReplyReceived" containing either a number 1 if a person has replied "Yes", a letter "x" if they've replied "No", and no entry if they haven't yet replied. What I need is to display (on each record, I think) the total number of "1"s, "x's and "blanks", so that we can…

First I would like to point out that I am new to Filemaker, and pretty new to Databases period, so take it easy on me. But I have a client that uses Filemaker and wants Database changes, so I have to figure it out. What I need to do is have a record that has several totals. These totals need to read fields to see if they say things like "Yes" or "No", and I need the totals of Yes and No. There are also fields that are dropdowns with 4 or so choices, and I need total counts of each choice for all records. So my question is, how do I do this? I tried Count( Child_Active ) = "No" but that wasn't even close I guess. Any help would be greatly appreciated!

Hello I have posted elsewhere on this forum, and this seems more like the ideal place to ask it. I'm creating a database for my poker league and have the following tables. members venues tournaments points seasons regions In points I have the fields position points and then relate to tournament id, total players and member id, member name I would like points to equal a calculation dependant upon how many players have played in the tournament. For every player in the pot there will be 200 points in the pot and first place will be rewarded 30% of the points. This seemed like a basic calculation to me, but I have disco…

Hope this isn't a dumb question. I know how to get summary info...but I need to get the total of items of the same named field. Example... field 'item' has 3 records with the term 'guitars'. One record has 2 in the quantity # field, another has 3, and another has has 4...so the total would be 9 guitars. Three records with 'guitars' entered in 'item' field...how do I get the summary total of JUST guitars...not all others...pianos,etc. Does this make sense? Thanks.

Greets, Alright, I have a button that runs a script. This script opens another file. I want it to also open a specific layout in that new file. However the "Go to Layout" command only lists the Layouts from the file you are coming from, now the layout you are going to. How does one fix this?

Simple invoicing system with Parent table and LI table. In the LI table I have a txt field to indicate if the item is taxable. If taxable the user enters "T" otherwise leaves it empty. My problem is how (from the parent table) to sum taxable items only. I know I can create another TO of LI and link it to parent to obtain the total. I am wondering if there is a calc or custom function that can give me the total without resorting to an extra TO or extra fields in LI. I have tried the TypeSumField cf listed in CF library on the forum but it does not work all the time. Thks

Ok, I am pretty new to this. But my question is this, after entering a date into a field, if the Get(CurrentDate) is less than the entered date in the field, I would like to initiate a custom dialog(I can do this) and then reformat the text color. I have been unable to accomplish this using the scripts, I am pretty new to the scripting here. I need an example, then I am off. Thank You

As a hobby I work on antique radios, and I have a FMP file for the shop that includes all of the tube tester data. 2,900+ tubes. Anyway I've come across an interesting issue. The tube numbers are almost always combinations of numbers and letters with the numbers first - ex 12SK7. The problem is the normal way the computer sorts alphanumeric combos with i.e 1,11,12,13 etc then 2, 20, 21, etc. Is there any way to make it sort 1,2,3,4....9,10,11,12 etc? Leading zeros are not an option. If I can't find an easy solution then not a huge deal as once this is all done I am going to just search for the tube number, but the problem is that I had to manually enter a…

hi all... i have a field that has positive and negative dollar amounts in it. i was wondering if its possible to do a sum on only the positive ones and then only the negative ones. separately.... is it possible? thanks...

hi all.. i have a calculation field moo like: moo = Case ( f7 = "Miscellaneous Deposit" ; f8) and it does nothing. there are a lot of records that have "Miscellaneous Deposit" in f7. i tried the if statement too. same result. nothing shows up in moo... why? thanks...

How do I sort a list of values created in a calculation field. Using the List function, the calc field produces: 2XL 3XL 4XL L M S XL XS How do I sort the result to read? XS S M L ...

Good Morning, Is there a function or command within a Calculation dialog box to check if text has been changed in a specific field? If so, what is the command? Thanks, Zach

Greetings, all. Was curious to see if a calculation could be constructed in which records with a unique field or identifying value could be excluded from the total of the desired result. Specifically, I am creating data drawn from the same table that calculates time periods for milestones that have various phases of work (call these child records). Each milestone period has a single parent, call it a parent record, that represents the totality of time assigned to all milestones within that parent group. Presently, my calculation shows all time-related data, including the time assigned to the parent and the children (which means that the calculated value is inaccurate beca…

I'm trying to get a script with a logical operator to work. If i run the script without the "or" it works fine but when i use the or in the calculation it doesn't work. What i want it to do is when customernumber is not what i define it shall exit the script else run the script. i have attached an examble file. test.fp7.zip

Hi. I am looking for a way to combine fields into a single text string. eg a calculation to merge the first 5 characters of a last name field + the first 5 characters of a first name field + date of birth in YYYYMMDD format etc. This would all have to merge into one field and the number of characters merged into the field must be fixed. The purpose of this is to submit medical billing records in machine readable ascii format as specified by the payer. Any suggestions? Thanks

I need to create a new series of sequential invoice numbers for my existing database of several thousand records. Must start with a letter (i.e. A6001, A6002, etc. I know how to do it for new records, but how do I make this happen for exiting records?

I can't find anything on how to do this, and I'm not even sure if it's possible, but here's what I'd like... My company is implementing a slot system for locating items in our warehouse. To do so, we're giving each location in the warehouse a unique location ID. The ID number is generated as follows: First digit: Number -> Warehouse # (1, 2, etc...) Second digit: Letter -> Isle (A, B, etc...) Third Digit: Number -> Depth (Number of slots down the Isle) Forth Digit: Letter -> Height (Bottom shelf: A, Next up: B, etc...) Basically, If an item is in warehouse 2, Isle G, 12 items down and on the second row, its ID would be 2G12B. We woul…

Good Morning Guys, i got a quick one for you... i want to filter the instances of "-1"'s in a certain field then count how many there are total in the DB... how would this look? thanks in advance, Eukanuba

Greetings I have created a subsummary report to assist with product forecasting. I seem to have hit a brick wall. The subsummary report is based on product code and then "month as number", allowing one to view the purchases related to a product over x specified months. Some products are clearly not purchased on a monthly basis. While one generally takes the number of products purchased and averages those over the number of months being analysed, I would like to be able to flag those products that have been purchased in consecutive months. Thus, if I am looking at data for April - July, those products that are purchased every month can be flagged with a "4" and w…

Hi I have seen queries along similar lines to this on other posts but they were mainly to do with summary fields rather than creating serial numbers. What I want to do is to create serial numbers for a field B but reset it to start at 1 when another field A changes. Field A A A A A B B B B C C C C C Field B a b c d a b c d a b c d e Serial No 1 2 3 4 1 2 3 4 1 2 3 4 5 Any help will be gratefully received and I hope I've explained my question clearly enough. Thanks

I'm trying to have the contents of a field (container, global or otherwise) show an image based on the person logged in (username). If "bob" is the user, his associated image (stored on the server) will be shown on a given layout. I've read a bunch of the posts in here and in the container sub group, but can't seem to nail down this answer. My initial guess was a calculation "if" statement with the test results simply referencing the filename (bob.jpg), but can't seem to get that to work either.

[color:red]Hello, I am creating a simple picture browser and would like to be able to display the filename of the picture that I insert into a container field (as a reference). There is no obvious script step or function to do that. I'd love to get some help her, thanks 1M

I'm having trouble making calculations within a sub-summary panel. Example: a sub-summary panel lists total sales volume per salesman and the quantity of units sold, using two summary fields. I want the average sale price per salesman, so I create a calculation field that divides the summary field for dollar volume by the summary field for units sold. This works fine when I place it in the grand summary panel, but in the sub-summary panel it shows the grand total result. Same thing happens for any other type of calculation. Any suggestions would be greatly appreciated.

Hi! I've tied myself into knots again. Can anyone help, or is this just not possible: I would like to be able to determine whether a person has been assigned to work on five consecutive days. The problem is that several people are assigned each day, to one or more of altogether 48 shifts. So one person may appear 10 times, another person 6 times, etc. in a single day. The number and identity of people involved isn't static. They are listed in a "people" table. Their "shifts" are collected in a "day" table with one record for each shift. It contains, among other things, a field for the name and another for the date. Is there any way to establish whether an…

Is there a way to half the text inside the field display "Condense" if it does not fit into the field? Now if the field is out of boundary it displays a "?" instead it should condense the field. Maybe this works with conditional formating? Thanks

How do I delete a field's contents in a calculation? I have tried setting the field to "" and using LeftWords(Fieldname,0). Neither of these attempts wiped out the field. I tried " " but that holds a space. I want the field to be empty. Thank you.

Does anyone know how to extract the data in a field to the left of specific word? For example, would like to extract all the data in a field that appears to the left of the word "door". However, "door" is in a different position in each record. Thanks. Gary

Anyone know the formula for deleting the last 3 words at the end of a field? Thanks again. Gary

Hi All, Wondered if you may be able to help with a problem I have, I have to update lots of activities with a release date, the release date is important so we know we cannot schedule anything past that date. The release dates are in its own table and has the following fields filmID, Film Title, Territory, the release date and projectID. The filmID is related to the Films table and from the films layout I have a portal showing when that film is being shown in a particular territory. I also have a project table and I can schedule activities to a particular film, within this table I have a field called release date and I would like to have the field automatically …

Does anyone know how to extract the data in a field that begins after a specific word? For example, would like to extract all the data in a field that appears after the word "door". However, "door" is in a different position in each record. (Sometimes it is the third word and sometimes it is the fifth word) Any help would be appreciated. Thanks. Gary

I have a business meeting that occurs the third Wed. of every third month. (i.e. this year Jan. 16, April 16, July 16, and Oct. 15) I would like to find a way to automatically calculate the next three meeting dates in (01-01-08) format. Thanks in advance for your help Dave

Hello there, i am quite new to Filemaker, its a wonderful program, i am having much luck with it so far. I have it making invoices for me and everything! My question is, i have a report that shows work order numbers and costs from each work order number, one after another like in the standard report it gives you. I want to put a total for all the costs in the footer, what kind of script or calculation would i use for this? I have tried the usual "+ "field" =" thing but it does not add all the feilds in the report together just one, how do i get it to do all, even though they all have the same feild name?

Good Afternoon everyone.. Im trying to create a report, but im having trouble thinking of a way, and to be honest i don't even know if this is possible. I have 3 Tables Blanks Data Blanks Report Blanks Picture in Blanks Data table i have all my data, and i have a script that will create a report. The initial report is done and working great. The second part of my report is where im really stuck. The initial part which is done with the script will create a report showing the Jpeg of the blank, but in this case just any Jpeg, and then it'll contain the Item number, Description, color, and the Qty Available (the qty available is created by a …

What would be the calculation to display only the first letter in each word? ?? Left ( order_quantities::orders_name; 1 ) ?? Thanks

I have a calculation that has to output "-marks ie: XML-calc= "<?xml version="1.0" encoding="ISO-8859-1"?>" This won't work unless I: <?xml version="&"1.0"&" encoding="&"ISO-8859-1"&"?>" everything.. this is VERY annoying. Help anyone?

How is id possible to have a button trigger a openURL script in a related table? I have one field that displays a related value list. Beside the field there is a button that triggers a OpenURL Script from this related value list. The problem it does not show the corresponded url link. Please see sample. Thanks Weblink.fp7.zip

I'm trying to determine the number of times a particular value appears in a given field. ie: The field Students::BEHAVIOR_CODE will contain a number from 1 to 30. I want to create a field (RelatedDatabase::BEHAVIOR_17) in a related database that will look at BEHAVIOR_CODE and count the number of times 17 (for example) comes up. If it exists 50 times, I want BEHAVIOR_17 to return the value 50. Any ideas? Am I making sense?

Hi, I have a question in regards to field validation. I need to be able to validate whether a field has been changed. eg. user_name field should only be modifiable upon data entry. After the field has been set with data for the first time, it should no longer be modifiable. How could I do that in a validation of the field? Thanks in advance

I understand Filemaker about 1 % enough to make my privit file for collection. Now I have to put 500 pictures with [color:red]12 kinds of logo in to the solution. I understand that its possible with a script that it does the job. If (logo="1a";logo 1) If (logo="2a";logo 2) but as often it does not work.the file ( logo 1 etc until logo 12) is on the harddisk. I am still learning filemaker and I dont know how long it will take me to understand scripting but I am in need of the script soon. can somebody help me. Gerard

I'm sure this is a silly question but I can't find the solution in the help index..... I'm trying to set a value in a field based on a calculation involving the number of records in a related table but don't want the field to recalculate each time the number of records in the 2nd table changes. So, on day 1 there may be 100 records in the related table and I can record this and use it in my calculation. On day 2 there may be 120 records, and any new calculation will use this value, but I don't want the calcs done the previous day to change from the 100 record status. There must be a logical function to get the absolute value but I can't see it. I don't want to write a s…

When I add or edit a record in a portal (FM9adv) one particular field validation rule doesn't trigger until I click outside the portal on the layout (Clicking on another row or adding multiple records doesn't seem to trigger it). The validation is for uniqueness on a field that has an auto-entered calculation. Other validation rules triggger as expected. This field is defined to validate 'Always'. Anybody have any suggestions?

We have a daily report that has a summary field that adds together all the pmts we take in a day. We added a new text field next to each individual pmt that states what method was used to make the pmt (cash, check, credit card, money order). My boss wants me to add summary fields at the bottom of the report, that summarizes the total amount paid by each individual method. Please help. I am stumped.

Hello, This should be fairly easy but running into problems.. field remains blank. What I'm trying to is to confirm that the contents (a number) entered into a field is of certain length and if not return a text error message. Here is what I thought might work... If (Length(Field_name = 16); ""; "Please confirm the number") Is there something I may be overlooking and if so, can you point me in the right direction? TIA. Jack

I need to split the date and time contained in 1 field to 2 new seperate fields. I have created 2 new fields. One called date and the other called time. The current data formats like this 7/5/2008 11:27:32 Pm. Is ther an easy way to split that into the new fields I created. Any and all help greatly appreciated. I am using FM Pro 8 Advanced. Thanks

I have a field that contains variable text between consistent characters: "+" and "dy" How would i filter the text that falls between these values into a new field? Thanks for any help!

Hello! I am trying to make a simple timeline graph and would like to use a repeating field for simplicity's sake. The field would have 25 repetitions representing half hours from morning till evening. Each half hour can be filled with a name, from a drop-down value list. Unfortunately there a few simple calculations I need to do, and one is to count how often each name is assigned in the course of one day. In other words count the number of occurrences of a specific value within a repeating field. Is that possible without using a script or creating a report? Thanks in advance for your help!

Hello Everyone, I'm wondering how to calculate a variance in a report, which would calculate the variance from the subtotal based on one of two choices in another field. For example, I created a field which holds the type of amount (income or expense) and I have a separate field for the amount and I have a subtotal calculated in the report for each type (income & expense). How can I calculate in a separate field what the variance is between the two amounts (income & Expense)? Thanks, Monika

Hello, I'm working on a database that needs to document the time stamps of films. This includes a "start time code" and an "end time code" which are used to calculate a duration field (end time - start time = duration). I have succeeded in doing this using the standard hh:mm:ss format, but it was brought to my attention that they need me to extend this format to include a frame number. Example: hour:minute:second:frame (where "frame" is a number between 1 and 30) From my limited experience with FileMaker, I see two possible solutions and I think I would need help in performing either. I'm also open to any other potential solutions that are available.…

Hi, In one of my files I have a First Name field and Last Name field. I've had a problem with users frequently creating duplicate records for people, so I'd like it to inform them when they've entered a first and last name combo that is not unique. I wrote a quick calc field (first_name & " " & last_name), but unlike with regular fields I can't find a way to have this data validated. I'm sure there's an easy way to do this but at the moment it eludes me. Thanks!

This formula, designed to calculate an end date based on a stated value in the form of a duration of time, does NOT include the beginning or end day/date in the calculation - but needs to. The formula does makes calculations based on weekdays only. Could someone wise take a look and troubleshoot it, please. Here is the formula: Let ( [ Array="23456034560145601256012360123412345"; Array2="12345012340123601256014560345623456"; Days=Int(formula_check::time_assigned_to_milestone / 5) * 7; Offset=Middle(Array;(DayOfWeek(formula_check::start_date) - 1) * 5 + Mod(Abs(formula_check::time_assigned_to_milestone); 5) + 1;1) ; Offset2=Middle(Array2;(DayOfW…

Hi All, I have a container Field that is only used for Jpegs. the Problem that im having is that people are putting high and low resolution jpegs. What im trying to do is limit the size for the Jpeg or limit the jpegs to just to low, probably my best bet would be to find a way to limit a jpeg below 200KB for example. Anybody knows if this is possible. Thanks

I have a FM7 file(&table) "tableChooser" and file(table) "software". The user logs into "tableChooser", his/her name is set in a field "login", then s/he selects (check boxes) one or more files(tables) to open, clicks a button that then opens whatever file(s)(table(s)) are chosen. "tableChooser" has a global storage number field "gConstant" set to "1", which is related to (=) "software::gConstant" (with the same settings as in "tableChooser::gConstant"). I don't think there is anything in this relationship causing problems; just mentioning it. In "software", I have a field set up this way: software::created_by Text Auto-Enter Calculated value…

Hello all, I know this has to be simple but I'm having a calculation issue and I guess I'm thinking to hard. Basically I want a calculation field that takes multiple fields and make it comma separated. So what I would like to create a calculation with static labels for the first row and calculations for the other rows that create something like: Name,Address,Phone Bill Smith, 211 Main st,5551234567 Steve Jones, 300 W. Jackson,5553456789 I am using this calculation: Name,Address,Phone Namefld1 & "," & Addressfld1 & "," & Phonefld1¶ Namefld2 & "," & Addressfld2 & "," & Phonefld2¶ But that just isn't working. Can…

I am trying to make a calculation that compiles information into a repeating calculation in FMP 6. How do i jump from one line of the field to the next repeating field. For example Day & Start Time & Venue [[[next line]]] Day & (Start Time + 1) & Venue ...

I'm having a problem with hidden ¶s it seems like some kind of defect in filemaker, and i'm wondering if there's a way to work around it. text calculations keep producing results with hidden ¶s or sometimes they're even there when i import or copy and paste. these hidden ¶s can't be detected in calculations! for example Substitute ( ______ ; ¶ ; "") will not even get rid of them they also aren't visible when you are looking at the text field in browse mode, but if you copy and paste the text into notepad, you'll see the hidden ¶s as real line breaks. anyone know how to fix this? even if i could just detect them, somehow, …

(I took two years off doing filemaker work, and can't remember how to do this: ) let's say you have field A and field B, and you want to create a field that lists all field B values (compiled) for all records where there is the same value in field A. example: Record 1 Field A: Green Field B: John James Adam Record 2 Field A: Orange Field B: Adam Tyler Craig Record 3 Field A: Green Field B: Bill Steven I need to create a field (possibly a combination of summaries and calculations), so that whenever A is "Green", the new field will contain all the words in field B, for every record in which A …

Hi I need a calculation that finds invoices 6 months or younger connected to a certain customer number and displays the total invoiced for that period. I.e. that calculation will update daily. I'm thinking of something like this: sum of Invoice_ToBePayed where date > today - 6 months but there is no "where" function in filemaker, is it? How can this be solved?

Don't know if this belongs in relational design or here, but here goes... I have a student table related to an orders table for hot lunch. This orders table is simple with just a few fields: FoodDay Item Quantity Fee StudentID Related records might look like this: Domino's Day Cheese Pizza 2 $4.00 Domino's Day Pepperoni Pizza 1 $2.00 Subway Day Italian 1 $3.50 What I want to do is create a field for each day that codes the order (to be used on an ID tag). In the new Domino's field I'd like a calculation to generate "2C 1P". For Subway I'd like it to generate "1I". I've played around with some aggregate functions with no luck. Seems …

Hello. I have a filemaker database where I make reports. Lets say that I want to send those reports to some users that are recorded in a "members" table in my database through email, how can i do that? Thanks

I am trying create a script or calculation that will match zip codes in a data base to a specified city area. The zips are a full 5-4 and i don't know how to accomplish this. can someone give me help?

I am a little stuck here... In table A I have a field with AutoEnter (calculation-evaluate allways-replace existing values). It is supposed to get info from a field in table B, when ever the relation is true. The relation is set in table B and it seems that this is the problem. The AutoEnter field in table A will not get the info. The relation is: Table A Table B pk_TabelA.RecID = fk_TabelA.RecID constantText (calc field) = Type When I create a record in table B I set the ID from table A and Type, which match the constantText. Hereby the relation become true. But the AutoEnter Field in table A won't ge…

I'd like to have a developer layout that shows at a glance the Next Serial Values for all data tables. I'd like to see the values that actually display in the define fields Auto Enter tabs in the "next value" field rather than doing a SetNextSerialValue (TableName::Serial_#;Max_Serial# = 1). Is there a Get(NextSerialValue) function or equivalent that will do this? TIA FMA9 on Win XP

I have inherited a table with about 5000 records: In about half of them, the auto-entry serial (number) IDs are formatted “0000001234”. In the other half, the auto-entry serial (number) IDs are formatted “TE0000005678”. There are no duplicates, the only difference is that some have the “TE” in them. Question 1: Is there an easy way to strip away the “TE” from the IDs that have “TE” and not bother the others? Question 2: Is it a best practice to set the auto-entry serial number validation to “unique value”? TIA FMA9 on Win XP

Not sure if this is the best place for this thread, or if the layouts section would be better. Anyways... Our database system is great for showing the number of sales broken down by month for a range of items, years, and/or customers. The problem is displaying the total sales for each item. What I'm looking for is a way for the user to enter an ID range, such as 50...75, and Filemaker finds all those ID numbers (easy). Then, I need it to calculate the sub-total for each item in the range (easy) and display it for each item (not easy). Example: User enters 100...150 and does the find. Filemaker returns the sorted records for all the sales of each item,…

I need to find a way to compute due date from the start date and the number of months down the road For example: My start date is 5/31/2008. If I add 6 months to the date, the due date computed by FM is 12/01/2008. This is not what I want. I want my due date to show 11/30/2008 - within the month itself This becomes more complicated if the due date falls in February. For example, if I add 9 months to the start date, FM gives me 03/03/2009. What I want is 02/28/2009. Hope someone can help me to achieve this ? Thank you in advance

I have three fields in Filemaker Pro. Issued ++ Weeks ++ Field Date People enter a date (Drop Down Calendar) entry in the Issue Field and enter a number (e.g. 1, 2, 3, 4 etc.) in the Weeks field. I have the following calculation (Issued)+(Weeks * 432000) which then caluclates the Field Date. e.g. 20-07-08 ++ 2 ++ 30-07-08 The issue i come across is that this caluclation does not take weekends or holidays into account. Is there a way i can force the field date calculation to exclude falling on a weekend day or holiday? Thanks, Sully

Hello, I've got a field that I want to have validated to check to make sure that none of a certain character (< > : " / | ? *) are used in the field. I've been able to use a calculation to make sure that the field name is not just that character, but that is as far as I can get. Thanks, Matt

I'm am brand new to the filemaker and rusty on programming. I want to make a button on a form take two of the fields 'firstname' and 'lastname' send it into another field 'firstlastname' for another forms popdown menu. or upon entry send the two fields to 'firstlastname'. Thank you

Hey all, I'm running in some logical issues with fields that display converted volume formulas. For example I have 4 fields: tps, tsp, ml, oz If I enter for example 10 ml the others should enter the converted result automatically. I tried it with the zippit plug in so when I exit the "tps" field it triggers a tps script that enters the converted value. It would work with one field but with all fields it runs into a loop and it keeps calculating. Please Help Thanks Convert_All.fp7.zip

Hi! I just began using FM9 on WinXP and I have a real newbie question... How can I take two DB fields and merge their content into only one new field? I want a solution that affects the DB for once and for all, not only the display. Only one of both fields contains data for each entry, so there will be no data overlap issues. Thank you!