Calculation Engine (Define Fields)

I've tried several things that I could think of but with no luck. I'm turning to you good people for some help. Here in my example I have 2 tables Contacts and Contact Details. Each contact can have many ways to be contacted but I want them to only have one main contact. How can I keep people from having more than one? I tried to validate by a calculation but no calculation I made would work. If you have any suggestion they are greatly appreciated. one_main.fp7.zip

Is it possible to get the filesize for a file stored in a container field so it can be inserted into another field? From what I can tell FileMaker's "get(filesize)" function only gets the size of the entire database and there's no way to tell it you just want the size of a file stored in a particular record. Thanks! Helo

I have a simple calculation that calculates the number of days between two dates. Get(currentDate) - Broker_Date Is there a way to have this not count weekends? Ie: Saturday and Sunday? I am using FM Server 8 Thank you

Need calculation to automatically label Project record “WORKING” or “COMPLETE” depending on the status of Project child Assignments. For example: If any of the child Assignments are labeled anything other then “COMPLETE” then Project calculation field clc_status will be result in “WORKING”. If all assignments are labeled “COMPLETE” then clc_status will result in “COMPLETE”. I have attached a sample file outlining this question. Thank you all in advance, I look forward to your comments! AutoStatus.fp7.zip

I have many grade fields that all require a calculation to get the subject grade transformed into a number so they can be averaged for the different semesters. The calculation I am using is: Position("FDCBA"; Left(fieldLet1; 1); 1; 1) - 1 + Choose(Position("+-"; Right(fieldLet1; 1); 1; 1); 0; .33; -.33). This works fine but I don't want to duplicate it for each subject. Is there a way to write it once (maybe as a custom function) and use it for all the subject grades?

Hi, I have a calculation that I can put together by combining several fields but I know there has to be a better way. I am trying to learn to be a better developer so I thought I would ask the group for help as I cannot find anything that specifically addresses this type of calculation. I am working on a DB for a Wholesale Contract Embroidery company. They charge for their services by the number of stitches that are in a pattern combined with a sliding base price determined by the size of the order. A snapshot of their price list is below. The Base price is for the first 3,000 stitches in a given embroidery pattern. # of Garments...Base Price...per E…

I have a evidence database that I need to invoice. If the field PAID THRU is greater than the current date and the fields DATE DESTROYED and DATE TRANSFERRED are empty I need it to return a “Yes” in order to invoice the evidence. I can’t figure out why the following calculation for the field “TIME TO INVOICE EVIDENCE” will not work. It also needs to return a “Yes” if the field paid thru is empty and there is no data in the fields DATE DESTROYED and DATE TRANSFERRED. Please help. If ( Cases Evidence CASENO::Paid Thru < Get ( CurrentDate ) ; "Yes" ; If (not IsEmpty(Cases Evidence CASENO::Date Destroyed) and not IsEmpty(Cases Evidence CASENO::Date Destroyed) ; "N…

Hi all, How does one perform a PatternCount( ) that's case-sensitive? Thanks in advance for any help!!!

I'd like to have the auto enter feature add a "/" character when entering into a date field automatically. Typing "0322" would become "03/22" and then FileMaker would add the year as it normally does. I am keying hundreds of records a day and I am looking to make it more efficient. Reaching for the "/" key is slowing me down. I tried Case ( PatternCount ( GetAsText( date worked ); "/" ) = 0; Replace ( GetAsText ( date worked ); 3; 0; "/"); date worked) but that didn't work. I was surprised that this did not work. What can I do?

Apologies if this posts twice - I thought it went through - but now do not see it. Given a dataset of Donors and Payments on one flat file. So you might have: Steve Smith - $200 Steve Smith - $150 Steve Smith - $100 We need to total the amounts for each donor. Steve Smith - $450 Can do with a sub summary field when sorted by "Donor" and it looks good in FileMaker - but want a field that holds total so this can be exported to an Excel file that simply has one record for "donor" and total "amount". Is there an easy calc field to total the amounts by Donor? TIA - Dataslinger

I have a couple of fields that I would like to relate to each other is some way. Will try to explain. Field1; status, field2; payment received %, field3; score. Values in these fields: Status field has 6 prefixed values (E/P/O/F/M/C). Payment received values between 0-100 and score also values between 0-100. Now what I would like is that filemaker will automatically fill in the value F(Finished) into the status field if 100% is filled into the payment received field. The same thing for P/E,etc. only with a different scores and payment percentages. I really don't know where I have to look to make this happen. Can anybody help or point me in the right direction? If…

Hello, I'm working on a database that manages plant specimens. I have this calculation in my database Lookup ( PCRSamples::Sample Genus ) & " " & Lookup ( PCRSamples::Sample Species ) & " " & Lookup ( PCRSamples::Sample Sub Species ) This field combines all of the taxonomy into one calculation. However, when I enter the data I use the full name of the Genus and Species. What I want to do is truncate the first two lookups to two letters: Ex: Pteridium Aquilinum Japonicum turns into Pt. Aq. Japonicum Does anyone have any ideas on how to do this. Thanks js22

is it possible to tell a field not to allow certian characters. i know i could set up a filter...but i need the users to not even be allowed to in put characters like / @ & any help is appreciated

First off, I am a complete novice. I have been using FMP for about 5 days now I am trying to create a database that calculates all yes/no fields for a reporting function. My job (small callcenter) has tasked me with creating a database that allows for me to input customer service agent monitoring results. It's a series of yes no questions on a form. Example "yes,the agent was helpful" or "no, the agent did not verify information" I guess it needs to be like a quiz and it outputs a score based on the agents skill? Anyone have any idea's for a viable method?

I am creating a membership database and I want try and elimante the creation of duplicate records. What i am trying to do is when the user creates a new record they enter the members email address. If the email address already exists it takes them to the record if it does not exist then it creates a new record. Any suggestions on how I might accomplish this?

Hi! I need an advice for a database I have build. I am including a sample of that. The idea is, every page when printed must have a specific number of records included in a table. The number is 17. At every page are needed three types of SUM, the fist is the Sum of the records on the current page, the second is the Summ imported from the previous page/pages, the third is the Grand Summary meaning the total of all. I have solved this with summary fields. I would appriciate if any will get the time to check it out and possibly find a better solution if any. On the attached file some of the headings are not in english, hope this is not a problem. Also, in or…

Hello, i have a field called "Name" that is a calculation of "first name" and "last name", when i go into find mode i cannot use that field, is there a way a calculated field can be used in find mode? thanks!

Hi, I do a command with insert picture -> into a global, and everything works on my computer (running advanced), but once I try to see it on other computer it doesn't show up anymore in the field, how can I make it that it shows up on all computers? Thanks Robin

Hey! I'm trying to make a simple calculation, but i don't know how the IF works in filemaker, i'm still new at this. Basicly I have a Yes/No drop down field, and I wan't to save the current account user in another field whenever the Yes/No field is changed. Can anyone help me? Thanks

Hello everyone, I am a beginner. I am pretty much clueless. I want to set a field only if the radio button is set to "yes". This is what I did: If[Property::Inspection="yes"] Set Field [Property::Repair; Property::RepairCost*1] End If Can anyone tell me what I did wrong? Plese help.

I have a calculation field in the parent table where I need to have an average of values in a field from the related records in a child table. But I need to only limit the calculation of the average to the LOWEST 5 values of the field FROM ONLY the last 10 records entered. Is there a simple way to do this that I am missing?

Hi, i am using FM Adv 8.5, in a network. i need to creat a new record (like a new invoice) and provide it with a new number as a result of a given parameter. in my case the use of the built in function "next serial number" is not possible because the numbers are not always consecutive. i use a GLOBAL field in the file, into it a script sets the last value after each time i create a new record. and a field for the RECORD NUMBER. when a new record is created - the script goes to that RECORD NUMBER field and Set the new record number as: record number = Last GLOBAL + 1. then it sets the global field as last GLOBAL = GLOBAL +1. the problem: the scri…

Hi, I am not sure if this is the right forum, but I want to create a summary or calculation with the following: I have records for clients that owe me payment on invoices completed, while the same clients have outstanding invoices to be completed. I want to create a field where I can put in the customer details layout where it show's me what they currently owe. The fields I want to use are: [fields] [Client Name] [Job Status] = Completed [Amount Owing] How can I create a total for each client when I look at the client records? Many Thanks, Dean

Hello, thanks in advance for any help. I'm trying to get a field to look up information based on the relation. Ex: I have 2 databases, one person contact and one company contact. I need the company database to pull a person's name but only if it meets a requirement. There will be multiple persons that belong to a single company. But there will only be one Key Contact person. I'd like to tell the company database to pull from these related batch of records, but only display the key contact name in the field. I've got it set up as an If statement, but it only works for the first record. If any record after that is the key contact, it doesn't work. Any t…

Hi, I'm using a portal for my jobs to display all the tasks for a job. Now I want a field (container) that displays the latest date for all the tasks for that job that have a type of: "M3". I'm totally stuck, can someone maybe help me with this? Thanks Robin

I have a calc field called cFoundCount and the formula is simply Get (FoundCount). cFoundCount is in my JobNumber table and I have a layout called JobNumber. when I go to that layout and look in Data Viewer for my cFoundCount value it shows 76 records which is the total amount of records in that table. good so far. Then I go to find mode and search for a specific JobNumberID and I find one record. I refresh Data Viewer and it still shows 76. refresh, refresh, refresh! *******. I guess it's really not going to show me 1. Why is it not displaying the number of records in the current found set?

I want to create an A-Z navigation bar that will allow users to "jump to" a record in the found set, based on the first letter of the <> field. The purpose of the nav bar is so that users can avoid scrolling through lots of records, or typing in criteria for finds. I envision it looking like this: A | B | C | D |... |XYZ, with each letter as a button/script. When the user clicks on "C" I want the script to sort the found set in alpha order and then make the active record the first record in the found set that begins with the letter "C". I should be able to write the script, but I can't figure out the calculation to move to a different record in the found s…

Hi, I have a repeating text field, and I'd like to use the concatenation of the repetitions as a single string (maybe with a given separator string inserted between the values, but this is less important). Any suggestions? I counldn't figure it out, without having to loop through the repetitions in a script and collecting the value in a variable.

I want to format phone numbers entered as 12345678901 as (234)567-8901 2345678901 as (234)567-8901 234-567-8901 as (234)567-8901 (234)5678901 as (234)567-8901

Hi, I need to create a field which would be used to get password where the characters will be replace by some other character, let’s say “*”. Any idea, suggestion how to do this ? thank you

I have a database that contains people and a related table that has their flight information in it. There are reports that need to be run where I pull information regarding their originating, arrival or departure flight information. I have created a field which tells us what type of field it is. It contains words originating, arrival or departure, with direct flights the field could contain both originating and arrival. This is the calculation I am using to pull the name of the airline for the arrival flight. If the field contains both originating and arrival it doesn't work. Is there a different operator or calculation that I can use to evaluate the entire field …

Hi. Do calculations that are referencing global fields still give user specific data? If there was a calculation field with gUserName and gUserName was a global field with that was set with the user's name what would that result be? What about global calculations?

Dont know if this is in the right area but does anyone know if it is possible to have some text that is typed into a text field linked to a website or document. I know that if you put in http://www.??.com it will allow you to open up a web site but I want to have text like "This is a link" and for that text to be the link.

I'm a new user of File Maker and I need a little help. Will someone please explain to me how to use data from a prior record in a calculation in the next record? Thanks

I've been using FileMaker for the past 3 years to generate a random number for lottery applicants to our charter school. It has worked really well for that purpose. Problem I keep having is that as our application pool grows bigger, so does my occurance of repeating "random numbers". I can't figure out how to make the random numbers discrete, so that it doesn't look as though we've rigged the results. Calculation I'm using for the number is Int(Random*10) & Int(Random*10) & Int(Random*10) Is there something I can add to that to make the resulting 3 digit number unable to repeat, or is there something else I need to define elsewhere? Thanks.....…

I need to calculate time as a number. i.e I have a time field... which outpute 3 hours. I have a number field which says 45. I need to calculate 3 x 45 = 135 How can I output a number calc from this to give me the result of 135? B

Hello, I am seeking assistance with a calculation problem. This involves a search within one record based on the values entered in a text field called, "Classification." I have a script that prints to a particular layout, based on the text entered in the classification field. This classification field has a value list of six, "Class A" "Class B" and so on. I tried the following. IF[involved layout::Classification = "Class A" or "Class B" or "Class C" Go to print layout one. The calculation works with only one Class entered. I have been at this problem for a while now and have decided to ask the experts.

I have a string named strStartEndDate that looks like this: StartDate&" - "&EndDate In some cases the start and end dates are the same so I needed a calc to make sure that only one date in the string shows up if they are the same dates. So, I created this calc: If(StartDate=EndDate; StartDate; strStartEndDate) The format for the date needs to be Month Date, Year (August 7, 2006). Here's my problem. If the two dates do equal one another the calc works great and the date shows up formatted as I need it. However, if the two dates don't equal the only format that will display is as such: 3/11/2007 - 3/14/2007 I need it formatted a…

I have three fields that I need combined into one. The fieds are 'Title', 'First Name' and 'Last Name'. I want them to be presented in the new field in that order with spaces in between, but if one or more of those fields are blank, I don't want blank spaces to show up. Make sense? I tried making the calculation with several variables, but I keep getting stuck. I know the answer must be a simple one, but I keep getting two 'Last Names', etc because I can't use "or" in the calculation only "&" - otherwise I get a big ol' "0" in the field instead of text. Any examples of a calculation?

I'm creating a database that will be used by an organization that will, among other things, be used for registering people for a symposium. They want to know how many people have registered in any given year. E.g., 2007, 2008, etc. So, I want to count the number of times 2007 has been entered in one field and next year when it's 2008 (in the same field) the count will have to be restarted. To add a bit of compliation to this, spouses will not be counted, only members--but they will be entered into the same database. Thus there may be 200 entries for 2007, only 150 might be the ones to count. I've looked through all of the forums and didn't find one that matched …

I was wondering if anyone knows of a trick/work-around that would allow a user to set their preference for a particular field to toggle on and off auto-complete? Thank you, Mitch

Hello, I have a database that I'm working on that I need some help with. The database manages procedures that we run in the lab where I work. My database has 3 main tables: Samples, PCR, and PUR. Samples has the vital stats about the plants we collect (species, genus ...). PCR is a table to show which samples we tried to amplify the DNA for. PUR is the purification of the PCR products. [sorry for the vague descriptions] On my PUR layout (list) I have 5 fields. The first is an ID for the lane number in the purification gel (1,2,3, ...) The second field is the PCR# that they reaction comes from. (1,2,3,...) The third field is what PCR reaction …

I have a Point of Sale system that I made. I have a quantity field for the inventory items. It is a calulation of QTYsold and ShelfCount. QtySold is a summary field of all the times that item sold and count is an editable field that is the count of shelf items. It continues to get added to the more we sell an item. Example: We have 9 pcs in stock, and sell them all. Count is at 9, QTY sold is at -9, QTY on hand field now shows 0. We get in 5 pcs count is now 14, QTY SOld is at -9, QTY on hand field now shows 5. Any better ideas? This one seems crappy.

Hi, I have a song and invoice databases for a company which licenses usage of songs in different tv shows. I enter DURATION of a song by DATE OF AIR and by SEGMENTS in a SHOW. I want to generate a total/summary of duration by DATE OF AIR & SHOW. e.g. the Primetime show that aired on 3/20/07 might have 5 different segments. i want the total duration for that Primetime show on 3/20/07 (adding all the segments). What calc would I use? Thanks!

First off, I apologize if it has been answered, but I couldnt find this specifically. I need to be able to calculate a loan end date from a start date and a term: Start Date: 04/01/04 Term: 36 months Calculated end date: 03/31/07 The problem that I am having, is of course that depending on the years that are entered, sometimes I will get a day before, or a day after...i.e 03/30/07 or 04/01/07. Is there a way around this, so I can actually get the right date all the time?

I'm having trouble wrapping my head around the following (I have this problem alot!!) I am creating a mailing list database. There are fields for address, and email address, among others. I have created 3 radio buttons, all with yes/no values. The button are to indicate "send by mail", "send by email", "never send". My trouble is, I want my users to only be able to select one of the 3, so that if they set one to "yes", it automatically re-sets the others to "no". At the same time, it needs to confirm that the address field is not empty if the user selects "send by mail" or the email address is not empty if the user selects.. you guessed it.. "send by email" and perve…

I have created a database with Quicktime (.mov) references in a container. The database is hosted on a G5 Xserve and the quicktimes are stored on an attached Xraid. When I launch the database on a Mac client its fine but not on a PC (with QT installed). I'm assuming it's some filepath / naming problem but I can't understand why. Please Help.

Hello, all you smartypants. I'd be most grateful if you could help me with this one. I'm trying to create a replace calculation based on text, but it only works if the EXACT text is the only thing in the field. In other words, it replaces "wrappers" with "softcover" but it does not replace "illustrated wrappers" or "self-wrappers" with "softcover", as I would like it to. Can anyone tell me what to put in the calculation to make the replacement whenever the given text exits in the field even if there are other words in the field, or even if it is part of another word? I've used nesting before, but there are so many variations It'd be a mile long! T…

Hi, I have a button in my layout and I want it to change colour with an if statement, what would you suggest doing, I've been trying a lot of things so far.. Looks like I have make another button in the back of the front button, and make it appear upfront,.. Can anyone give me some advice please? Robin

Does anybody know how to write a script to export the filemaker file to a PDF format on FM5.0 Windows to a 8 1/2 x 5 1/2 file size.

FM8 Pro Trying to summarize precinct results for a particular race. Most records are listed as follows: Precinct# Rep Dem 2102 34 87 2103 56 98 2104 88 53 The above is what I want..but sometimes the data is as follows: Precinct Rep Dem 2103 55 50 2103a 25 15 2103bg 10 5 There really isn't too much of limit of the extension of the precinct. The sub precinct might be denoted with the letter "a" or "ab" or "bg" or "cde". Not a whole lot of ryhme or reason. How can I summarize each sub-precinct into a single precinct tally? So that the end result is: Precinct# Rep Dem 2103…

Hi there, i'm fairly new at FileMaker. I would like to generate a custom serial number. Basically, I would like the text/number generated to have the first 4 character in the AdvertiserName field + a 6 digit number (automatically generated). I don't know how to calculate this. please help

What I'm attempting to create a unique number for each record based on another field. That is, I have 2 fields, "type" and "ID". Type has 2 choices "A" & "B" the ID will get a number based the the choice made in "Type". So when a new record is created, if "A" is chosen, ID will be Filled with 1, 2, 3, etc. If "B" is chosen, It will be filled with 1, 2, 3, etc. I've gotten this to work using the count function, but, if an earlier record is deleted, then a number is repeated, due to the fact that it's just counting the number of records containing "A" or "B". Any suggestions?? Also, I'm using FMP V6 would I be better off upgrading to V8.5 to resolve this?? TIA…

Hi, I am looking how to auto enter some information in a field upon whats entered in a specific field. Here is the example: and and so forth. Probably simple, but I don't find the way.

I have tried Sliding/Printing, calculation fields, container fields and nothing works. There seems to be no way to make a signture graphic move up and down the page to fit with the length of a letter. Please prove me wrong!

I have a table where each record contains an editable value (along with associated information). At runtime, I need access to the values as a one-dimensional array, so I transfer them at startup into a global repeating field. All this works fine, but I got to thinking: Is there a way to define an unstored calculated repeating field that effectively resolves its contents in realtime, whenever another calculation references the field? I put together a test file using text fields in place of numbers (easier to see what's happening). I've tried a couple of different approaches but all I've been able to get is a value in the array's first position. What I need is for…

:) How can insert or import MP3 or WAV sound file into a container from a list that contains the actual name of the filexx.mp3? Using Filemaker 8.5 on Windows xp. Thanks

Hi: I have imported data from an old HyperCard file into FM Pro Advanced v8.5. Data in one of the fields is for date and another is for time. Both were imported as text fields. Data in the Time field appears like so (for example) 12:10 PM. I changed this field's type from text to time. Viewing all the data as a table and clicking the column header to sort seems to work fine. Data in the Date field appears like so (for example): Friday, March 16, 2007. What I want to do is convert this exact data (that is keep the date as--in the example above--Friday, March 17, 2007), but convert the field type from text to date to allow for easy sorting. I s…

I have been working on a project very similar to a checkbook registry. I have Balance for each entry as a Running total and this works fine when all records are shown. The problem comes in when I display the records for the current month, the Running total does not show the correct Balance. Is there a way around this? Brian Cupp

Hi, I am racking my brain trying to calculate the hours between 2 times. For example, I want to know how many hours between 7pm and 12am. I know it's 5 hours, but I am getting -7:00 hours. The calculation is not realized that it has changed days. Any calculations before 12am is correct, but it won't add properly when I go after 12am. Can anyone help me please?

I am attempting to come up with a calculation that allows a field to automatically be inserted with a number when another field is filled in, example would be a number amount that is put in to a field lets say 1,000 and what i would like to have happen is the next field would automatically be filled in with the appropriate number lets say if you insert a number between 200-1000 "it would vary between that number" the second field would need to say 5 as an example, i have played around with this for a while but cannot get it. any ideas would be greatley appreciated. Thanks

Let we have a result after calculation. I want to round this result without decimal places to the next or the previous nearest integer. Example: If we have 9,98654712 to 10 If we have 10,0456789 to 10 Pascal

I have a field that is based on a calculation of (Date Received - Date Sent). As long as both fields are entered, no problem. But if the Date received field is empty, a negative number is displayed. How can I write a calculation for that field that test to see if the Date Received field is empty. If so, displays a zero in the Actual Response field. If not, perform (Date Received - Date Sent).

I have a field that has 5 possible text values. How do I perform a count to see the total number of times each value appears in the db?

How can we express z in this function to be any integer in order to be applied in the if function; Let([z=any integer]; If(Year(MyDate)=2004+4.z) Pascal

Until I convert to 8.5, I must complete an important project using Version 6.0. I need to calculate biweekly pay dates for employee contracts for a 3 month period, with 1st pay date being the next date in the biweekly cycle after their contract date and "Base Date" is the 1st pay date of the year. I figured that if I use Mod(Contract Date - Base Date, 14) and add that to their Contract date, I would have the 1st Pay date for that employee. I tested the math in Excel and got it to work. I found a problem in Filemaker (V6). The MOD and INT functions give different results from Excel. (Ver 8.5 is fine). FM6: Int(-3.4)=-3, Excel & V8.5: Int(-3.4)=-4. Also, FM6: Mod(4…

I am importing data from an excel spreadsheet. The first name field may or may not contain the middle initial. I have a calc in excel to parse the middle initial and put it into the proper cell, however, I can not delete the middle initial in the first name field, because that nullifies the calc in the middle initial cell. Also, in FM I am using the "UniversalCapitalizer" CF. So, can I add a case function to remove the middle initial, if there is one, from the First name field? The data comes in as: John P. Mary J. Joseph Argyle S. etc. Your help is appreciated. Thanks, Al

FileMaker 8 Adv. and QuickTime (versions 6.5.2 and up) are not compatible with File Maker on Windows XP. Importing a sound (.aif) into a container field and a subsequent attempt to play it results in a crash of FM. The situation is particularly severe with QT 7. With QT 6 the crash does not happen every time I attempt to play the sound, whereas with QT 7 the crash happens every time I try to play the sound. If the first layout of an opening database contains a sound field the crash occurs right at the startup of FM. I tested it on 3 separate Windows machines (both XP Home and Pro), with many different databases and sound files. Did anybody encounter and knows a workaround…

I have a timeclock solution where I need to show the elapsed time of a clock-in...... Example...clocked in at 8am, it is now 113pm and not clocked out - how long have they been clocked in? Just need the calculation

Dear All I need to create a record numbering system which combines Year + Code + Number. The number increases by 1 for each new record created, and resets the number to 0 at the beginning of each year, eg: 2007-ABB-01 2007-ABB-02 2008-ABB-01 2008-ABB-02 My query is how to have the Number automatically reset to 0 at the beginning of each new Year. I would appreciate any help on this; I have searched the forum but have not retrieved quite the right answers. Thanks Anne

I would like to calculate how many days it takes to finish a project. It is an easy formula: shipDate - invoiceDate. What I would like to be able to do is eliminate any weekend days that would be contained in that formula. If I'm working on a project for two days I don't want it to show up as four days because I worked on it Friday and Monday.

Solution / Reports and Basic Arithmetic Description: Hi, I have just started learning FileMaker Pro. I started with this basic arithmetic and adding of data into a report. An automated Tally Sheet that sums up 3 numbers and be able to add another record. However, Is there a way to make the program more functional by Entering Records of Candidates with the corresponding list of votes per Room (ie. 100 Rooms), inputs the Date/Time it was recorded and the partial Tally per candidate. I was able to add Candidates and sum up votes per room but in a manual manner. I hope somebody could help me with my problem. Thank you. Here's a sample of the code i've previously d…

Hi there, Im relatively new to filemaker pro. Im creating a database to take and process orders, and i have most of the database in place part from one thing i cant seem to fix; The database has a stock list, with each item defined by product code, and a stock level (quantity). What i need to happen, is for the Quantity in stock to be calculated based on the quantity of that product that has been processed in the purchase orders. For example; Purchase order 1 - product 000001, quantity 10 Purchase order 2 - product 000001, quantity 15 Stock level = current stock level - purchase order 1 - purchase order 2, based on the product code (000001 in this exam…

hi, i'm trying to find an alternative for this current calculation i'm using for Field 2: If (Field 1; "*"; "BI") The problem with this is is that if Field 1 contains any numbers, e.g. "pop song 2", the calculation doesn't work and "BI" is not entered into Field 2. Is there a way around this? Thanks

I am looking for a good solution for switching between languages in my FM8.5 application. I just want to switch in a various number languages starting with two. All field labels must be changeable and all the messages, I want to do this in a separate file so I can use it in all my application files. This makes it easier to add a new language or to correct a translation error. So I started to make a language module file, with a repeating (10) text field, for each language one repeating position. I fill this field for each label or written text in a layout, a new record. In the file itself its very easy to make a global field as a language selector, and let a calc…

I have two fields (Order_Status, Quote_Status) and each of them is populated by a value list item selected by the user. The default setting for each field is "Quote". Once the Quote_Status is changed to "Approved" I would like to be notified in some way. The user is entering her data via IWP and after clicking submit the change is committed to the DB, but I can't figure out how to have it let me know she has changed the field to "Approved". I am often times out of the office on sales calls and I forget to look through all of her orders (50-75) to see which ones have changed. And she forgets to email me all of the ones she has changed. Remember, she is using IWP.…

I have a calculation field that returns a number. Can I make the display change the color of the number returned depending on the value? For example if the number is less than zero, the text (number)is red. If less than 30, it would be yellow and if more than 30 would be green. How do I set up the color command for that?

I am trying to calculate the number of days between the date I created an invoice and the current (today's) date. I'm baffled and have spent hours trying to figure this out. Help?

I want to set a field in a different file using the current record from source file. I am using a file reference to second file. I tried to set variable(sourcefield) set field secondary field,$variable. get unrelated error. Is there a way to do this with out creating a new layout in source file using records from second file?

Hi, Is there a way to clean-up the database by removing all the unwanted carriage returns (CR) and line feeds (LF). I don't want to do it manually by going to each value and deleting this unwanted special lower level characters. Some kind of script or option in Filemaker would be good. Thanks, Max

I have a field that calculates a member's current age based on their date of birth and the current date. My problem is that this works fine on initial data entry, but I would like this age field to update whenever someone looks at a record. I am sure somebody here knows how to do this. Thanks

Hello below is my calculation: If(Print Total < 20.00, Freight 2006 A B Total + 0.50, If(Print Total = "20.00" <> "40.00" , Freight 2006 A B Total + 3.00, If(Print Total > 40.00, Freight 2006 A B Total + 4.00, Freight 2006 A B Total + 4.00))) What I'm trying to configure is: 1. If my Print Total is < 20.00 add 0.50 to my freight cost. 2. If my Print Total is >20.00 <40.00 add 3.00 to my freight cost. 3. If my Print Total is >40.00 add 4.00 to my freight cost. Please help!

Hello everyone: I am having trouble getting a total by project at a specific date. In nutshell what I need is an output (onscreen or columnar, no preference) that shows the Total amount for each project at a specic date (June 30 2006 or Oct 31 2006) omitting projects with zero balance. See attached file. Thanks for any feedback and help. AmtByProj.zip

Hey all, I don't know if this is the right section, so please move if incorrect. I'm also not really sure how best to describe this so bear with me. I work for a custom steel manufacturer. We need to track our plate inventory very acurately for code work. We have a system in place that uses a 3 character string of the alphabet. Example "AAA". This is marked on the plate and then to our job cards so we know what plate/grade of material was used. When we buy the plate, the mill that produced it will include paperwork that verifies chemical and physical specifications. We use "AAA" to quickly find paperwork related to the plate. The system works well and ha…

Can you help me in defining a calculation that transforms a date from YYYY/MM/DD to DD/MM/YYYY. Kind Regards

Hi, little problem and can't seem to find a solution, can some point me in the right direction? Made a report (list view) to have a quick overview over the information per client or project. The problem is that I don't want anyone to be able to accidentally change any information in the list view. Can I lock the list view information and let people only make changes in the form view? Dutchy

I've got invoices being stored in temporary tables prior to actually being created in our real invoices table. I have fields called InvoiceCreated, InvoiceDeclined, InvoiceOnHold. Each has a value of 1 or 0 effectively marking the status. No record would have a 1 for more than 1 of these fields. All of that is working great. What I'd like to do now, though, is be able to figure a total amount for the declined orders. So I just need a total of all GrandTotal fields where InvoiceDeclined = 1. I could do it in a heart beat with SQL in a web app but I can't figure it out within FileMaker. I'd like to display "We've declined worth of orders." on my layout. An…

How would you implement the following specification in FM 8.5 (Win)? On a typical Quote screen, a user can select a product to quote on. When a product is selected, the price of the product is automatically inserted into the quote. Then the user selects one or more options for that particular product, each having their own price. As each product option is selected, the option price is added to the product price on the quote. Then the user may override the final price with whatever (for example they may decide to bump up the price or knock it down for whatever reason depending on who the customer is). That's the spec given to me - and I tried to solve with a …

WARNING WARNING! DANGER NEW FM USER ALERT! So I have taken just an Intro and Intermediate FM class, and quickly get stuck on functions and how to set up appropriate tables. Here is the type of data I have for selling golf clubs, all set up in one table (UnitTable): Type Club Qtr Yr UnitsSold Iron Wedge 1 2006 80 Iron 5-iron 1 2006 20 Wood Driver 1 2006 60 … I have this data for 12 quarters starting in Q1 of 2005. I would like the user to do a Find based on Type and then the layout would split back: Iro…

I am trying to set a range of numbers to validate an entry into a field. When I set the range for example 009001000001 to 009001999999 it will allow 009001123. How can I set the range only to accept numbers with 11 digits. Sorry I am a part time user and totally lost

Designed a database for 8.5, now I find out it's going to be used in 8. I have a calculation field that determines an overall status of these related records by using List() function (depressingly native to only 8.5). Any ideas how to revamp this field definition? Field Definition: Let( [ $BinStatusList = List( OrBin_OrderNumber::Status_Bin ) ]; Case( IsEmpty( $BinStatusList ) or PatternCount( $BinStatusList; "Needs Duplication" ) = PatternCount( $BinStatusList; "¶" ) + 1; .........blablablablabla....restofcasefunction.......... ) ) There's not a way to have a Get( RelatedRecordCount ) or something, is there? With that I suppose I coul…

I inherited a database that, in addition to other fields, contains a numeric field identifying a meeting and fields describing services generated from that meeting. The problem is one meeting might generate one service or five and each service is denoted in a separate record - with the same meeting number. is there a manageable way to deal with this data so I can see in a layout one meeting (number) and all the services associated with it? Thanks, Rick

First off, forgive me for my ignorance! I have created a Real Estate database which includes a contact layout, as well as a new purchase property layout. I need to create a script that after a certain date (closing date) copies the address details from the new purchase layout to the contact details (as the client will now have moved to the new house) I can create a "copy and paste" script, but how do I add the trigger mechanism associated with the closing date? I'm guessing its something like: If [buying Info::Closing Date > Get ( CurrentDate )] Perform Script (Closing Day Script) End If Am I close??

OK, I'm doing a simple check compared the the current date so my data set can expand as time passes. I'm doing them in quarterly increments. When I'm doing my arguments, it works fine for April and July, but when I get to October it reports as true. Get ( CurrentDate ) > "4/1/2007" result 0 Get ( CurrentDate ) > "7/1/2007" result 0 Get ( CurrentDate ) > "10/1/2007" result 1 ?? Can someone else try these and see what you get? I've tried it on two clients and both do the same thing.

Is there a way to manually add entries into a calculation field? I have a list of vehicles and when I select from a drop down menu, another calc field opens with the specific details of that vehicle parameters. If I use a vehicle not in the drop down list, I select "Other" and enter the ID manually, but when I try to enter that vehicle parameters in the calc field, it will not let me. How can I override that? The parameters themselves are used to make a summary so I need the information on the "other" vehicle to be included.

Hi there, We have a drop down list of letters for a template. i am trying to find a way to make a container field that holds a signture graphic to positio itself at the end of a letter without leaving any space if the letter is short or eclipsing the letter if it is long. The Sliding/Printing function doesnt help - how can I make the signature move up or down the page so it fits with the length of the letter? All advice gratefully received. Cheers, Steven

I'm sure this has already been covered numerous times, but I just can't seem to figure out what I'm doing wrong. When defining a client database, one of the fields I have is phone numbers. I defined the field as a "Number" type. When I type in a 10-digit phone number in this format "8883334444" it automatically changes into "8.8833e+09". I don't want it to display in this form. What am I doing wrong? Thank you and I apologize if this has been covered already. I did a search but I couldn't find anything related to my problem.

I have a field where people have already entered lots of comments with a date next to the comment in the date format x/xx/xxxx. I made a new layout where i want to display the data from this field but I want to have any dates show up in red. I Know the calculation for styling text red, and I know how to filter to find the date but I don't see how to make them work together. Any leads to a solution is much appreciated! Thanks!

i've gone to define database. i'm trying to get the tax acknowledgement field to produce an "X" if the item value is greater than $250.00 of the sold for price. i don't know how to do this. i don't know what type of option to give each field name or what formula to use so that i can get this result. so basically, i need the field to read: If "item value" > $250.00 of "sold for" then insert "x" in "tax acknowledgement"

I have 3 fields in a layout where you can type text as long as you wish.Also i made a same layout for printing with only these 3 fields.What i want is that the fields in printout layout be able to autosize so that the text in that fields to be visible when i print.