Calculation Engine (Define Fields)

I could really use some help. I use my database to track print projects. Some projects have 2 binding types Project Number | NumberofCopies | BindingType |NumberofCopies2 | BindingType2 Right now I can pull a report using summary field that says: BindingType Number of Copies 3-ring Binders 11 Spirals 15 And a separate report that says: BindingType2 NumberofCopies2 Folders 5 Spirals 7 But what I need to do is combine the two: Binder Qty 3-ring Binders 11 Spirals 22 Folders 5 Is this even possible? I've been reading though a lot of Filemaker documentation but I d…

Hi everyone! how do you do? I have this little issue: I use my solution to write letters (of different lenght) to my customers. I'd need to insert my signature (an jpeg image)at the end of the letter, but I don't know exactly how to do to make that the sign appears exactly at the end of the text, no matter the lenght of the letter. I look foward your help!!! THANKS A REALLY REALLY LOT PEOPLE!!!!

I'm trying to make a container field that is filled or not depending on a calculation. If I have a Table "MyTable" with a field "N25", I can do a calculation: Substitute ( GetFieldName( Self );"MyTable::N";"") and that will put the characters "25" into the field. It doesn't seem to matter if the field is number or text. But what I want is a container field that takes the contents of another container field if the numerical part of its name matches the contents of a third field: Example: I have 3 fields D25; N25; Dot. D25 is a number field. Dot is a global container field containing a graphic object. N25 is a calculated field that returns a c…

I am trying to have a list that would show the sum of records sorted by type. The DB has two tables "list" and "list line". "list line" is self joined to "List_line_Type" based on the fk ListID and Type. The sum field for Type is a calculation field: Case (Type = 1 ; List_line_Type::Total) and Case (Type = 2; List_line_Type::Total). Next I want the sum as sorted by type to show in the List table. The fields are defined as calculation filed = List_line_Type::Tot_type1 and ...Type 2. While the right results appears in the List_line table, they will not show correctly on the List table. It's as if FMP only evaluates the first related record and hence shows the calc…

I am using this calculation and want it to find Calc= name_id & "F" How would I write it to find name_id & "f".... OR R in the same calc.

I think I have a workaround to this, but it would involve me creating a lot of unnecessary fields. I have several fields that are simply counts of the types of calls that came in. They are grouped into their specific areas... orders, returns... etc... I need to create some summary fields for this data.. So that I can sum together certain fields in their respective groups.... based on the date of the record. I was thinking I would probably have to create a summary count of each field... then summarize count together those summaries to get the total for that group. That seems like a lot of extra fields... anyone have an alternative?

Hi I would be most grateful if you could help me with creating a calculation field. I have 3 tables related with an 'id'. The first table is the one that has all the 'real' fields and the two other tables only have the 'id' field while they are the ones whose non-existent fields are filled in and automatically entered into the first table. (does this make sense?). the first table is not visible to other users. I would like to create a calculation field in the first table, that when someone enters data into the 2nd table, the 'item' field in the first table will be auto-entered as 'one', while when the 3rd table is filled in, the 'item' field will be auto-…

I am using a script with repeating container fields. I would like to create a blinking effect in a container field. is it somehow possible to copy from 2 container fields in succession and have it loop. one containing a red image and one containing a blue image?The script below copies a red image into a container field and when the user clicks again it becomes empty. But i would like the user to click on the field and have filemaker copy the red to the new container field and then copy from another field containing a blue image and loop that process. Is this possible. Case ( IsEmpty ( blank::button 1 [ Get ( ScriptParameter ) ] ) ; blank::red ; "" ) …

i have an extraordinary date display problem. i have a simple date field (not a calculation field) displayed in a portal. regardless of what date i enter, it displays the 9th of that month (eg 27 august displays as 09/08/2009 (dd/mm/yyyy format). when i click on the field, it highlights and the correct date displays, but when i click off the field, it reverts to the 9th again. any suggestions?

I have a repeating field which has 40 repetitions and I would like to make a script which clears the contents of all the repetitions at once. Is there a calc to do this? Like (1-40)

I have a filemaker database with a field with a very large amount of text per record. I would like to analyze the whole field, for all records, for number of " word " occurrences for all text. Essentially what a keyword density tool does but for filemaker. I know that there are several calculations, and procedures for finding occurrences, but none that I know of, will work without specifying the text that you are looking for. Please help!

Hello All, I am looking to add a field which concatenates the "related" values of a multi key. I have 2 tables Appointments and Users. (Appointments) ID Start End User ID Function ID Description ShowUsers - Calc field I want to create (Users) ID Name Initials The User ID field in the Appointment table is a check box set so multiple users can be added to an appointment. All is splended except in one of my Layouts I want to show the user initials in one line (ie. RC,LP,DT). The problem is that I am storing the User ID for a lookup so I have this to deal with 1 3 6 I need to look up the related value of each line…

I'm using the mutlilanguage solution found in the filmaker 10 Bible. I have a table "I" with calculation fields. For example the field Country contains following calculation: Evaluate("LanguageResources::Country"; gLanguageID) Now you can imagine that it is a big work to change this calculation for every field (every Label, tooltip, dialog message, etc has a field about 200 in my solution) I tried to get the Name of the field and to use it for the evaluate function like this: Evaluate("LanguageResources::GetFieldName(self)"; gLanguageID) But GetFieldName returns I::Country (I is a table that contains the same fields like LanguageResources…

Hi there, Is there a way or a script that could edit a serial number? I have a field that increments by one for every new records. When I duplicate a record, a serial number is missing. If the current serial number is 005, the next serial number will be 007 after duplicating 005. Is it possible to set up a script that will change 007 for 006? Thanks for your help

I would like to be able to display the calculations (or How did you come up with this number?) for my managers. I have a solution with many calculations and I need for some of the managers to be able to see the calculations in order to verify them. I don't want to give them admin access to actually view the field info and options, just need to have a field that can be accessed with a button or script that will show the actual calculation that is used. How can I do this?

I have 10K+ records with a fields holding values similar to these; AAA_02_1234 AAA_102_3456 AAA_5102_9876 AAA_7_HAMBERGER ZZZ_22_12346 ZZZ_442_12346 PCDD_18_05 The majority of them follow a certain structure but the only thing absolute about all of them are the "_" (underscores). I'm trying to extract just the numbers between the underscores but I'm not having much luck. I'm trying two work with this Let function and was hoping for a little help. Right now it works to remove the first set of letter and the first underscore. I can't seem to get it to remove the last string and underscore. Let ( [ len = Length ( Lab_No ) ; pos = …

Hope I explain this correctly.... I am putting together a real estate application and I have a set of deed templates in containers in a table that I want to choose from based on two conditions: - State - Deed Template Type (A, B, C, D) I have a dropdown list on one layout where I have fields that I want to merge and a value list with template types to choose from. I am having trouble figuring out how I set up a calculation to choose the proper template type from the table with the containers that hold the templates and return the file name into a new field so I perform the merge. Is there a function that returns the name of the file in the containe…

Hi , I am developing a online test solution. in which i am assigning 30 min. to each student . in this solution what i want is that after 30 min it will show me a message that your time is over. how can i do this plz help me.

I'm a little stuck here. I'm trying to get a calculation to display a field from a particular related record, based on another field's content. UR_NUMBERS(3 fields) ur_id ur_hospital : ur_number The ur_id = patient_id. What the table could look like with one patient, but three hospitals and ur numbers: 1 : Hospital A : 123456 1 : Hospital B : abcdef 1 : Hostpial C : 654cba I want to create a calculation, for where ur_hospital is Hospital A, display the corresponding ur_number (123456). So far I have: Case ( ur_numbers::ur_hospital = "Hospital A"; ur_numbers::ur_number) However, this only works if Hospital A happe…

I need to take a field with the following content and break it up into a group of 7 digit item numbers. Recommended Accessories: * 8264608 - Hose * 8041543 - Holder * 8258600 - Accessories kit * 8001938 - Grease I have found this entry which was helpful in removing all the text: http://www.fmforums.com/forum/showtopicforreply.php?iframe/1/fid/36/tid/210907/pid/339589/ I now have a field with: 826460880415438258600 8001938. I would like to have the field formatted like this at the end: 8264608,8041543,8258600,8001938 So I have two questions: This field may have no items or multiple items, and may or n…

I'm having some issues with understanding container fields with file references and how they are updated (if they can be updated). I have a HUGE database of pictures. Each picture is roughly 100k and I have almost 10,000 of them. If I import these pictures into a container file. My guess is I'll have a DB roughly 2gb in size. Don't think navigation is going to be very responsive do you? I would like to import them as references where the database will be small and the files will be stored outside of FM. Problem: If I copy these files and the DB from one USB drive to another or If I attempt to use this DB on a Windows FM machine I loose the referenc…

I have a table with 380 fields. I want to experiment in marking them all global. Is there an easy way to do this rather than going into each field and changing them individually?

Does anyone know how use a calculation for the serial id; where if a record is deleted the next serial number would be the record last deleted or if a record has not been last deleted, calculation would just use next value? My solution creates invoices. Sometimes a user could make a mistake or simply the invoice needs to be fixed/deleted. In accounting, it is not good practice to have missing invoice numbers/ids, looks bad if audited. For example... 1, 2, 3, 4, 5, 6 = good 1, 2, 4, 5, 7, 8 = bad Should I worry, or just let live? Thanks for all your help!

Hi people I'm working on a student assessment database, where an assessment can be marked a number of times (these are called attempts) until the student scores 100% on their assessment. A student will have a number of different assessments (called units). Within my DB, I would like to have the attempt field automatically increment every time that unit was received. So for example: Unit - Date Received - Attempt 112 - 1/09/2009 - 1 111 - 2/09/2009 - 1 112 - 6/09/2009 - 2 112 - 10/09/2009 - 3 ...etc I've included the DB so you can see what I'm on about in further detail. Anyone have any suggestions? thanks Cory Daenis_DB…

I am trying to define a field that will display the maximum (highest) value recorded in another field - accounting across all records (ie; conceptually it will need to be be a "global" field BUT it must also be "unstored"...) To explain: I have an Activity_ID field (1 to n). Each time a new participant is added, they are assigned an Activity_ID. This could be an existing ID, OR it could be a new activity - in which case the ID assigned will be the next sequential number above the highest existing. Unfortunately the participants can also be in the database already, so we first must find a participant (by name and ID, I use a "find" script with a custom dia…

Hello. I have a table called "contacts". I want that the layout that has the contact info, to have a button that says "new invoice". When I click there I want to be able to fill the info for an invoice, but that the personal information to be filled already. Also, in the contacts layout, i want to be able to see all the contacts that I had already made.

Basically, I'm trying to get FileMaker to wash the dishes and walk the dog. Probably not possible, but I thought that the combined wisdom of this forum might have some tips and tricks. I'm registering teachers for a professional development seminar. Right now, I can register the teacher and all registrants show up as expected in a portal that shows name, school, attending status (yes, no, no response). Now I want to add 2 pieces of information to the portal. If the teacher is coming -- a definite YES -- I want to show a field called meals_lodging. This has checkboxes and a value list with values such as Breakfast_monday or Lodging_monday, etc for all the s…

I'm sure this is dead easy, but here goes... I have a database with a small number of records, and I want to be able to record a snapshot of the content of a few fields on each record as they appear at month-end, and compare them on a month-over-month and quarterly basis. Can someone suggest the most efficient way of doing this?

Hi. Amazing resource here, I hope someone could point me in the right direction, if i can explain my problem correctly. I have been asked to convert out company DB from Access to FM. Each of our stock items are unique but there are essentially 2 types of stock, type A and type B. In the old database the stock number ran from 2000 for type A and 6000 for type B. A year code was also appended to the front of the stock number (Y1, Y2 etc ). When you created a new record if you selected Type B then the stock number would be created by combining year code and the next number available, Y16001, Y16002 ,Y16003 and so on, Type A would result in Y12001, Y12002 etc. …

I have a script attached to a button that repeatedly uses the Insert from Last Visited function to add data from 15 or so fields (that can be common) to the record of a new client if that client is participating in the same activity as the previous client. This works fine... but some fields refuse to transfer to the new record. This no doubt has something to do with the complexity of my database structure - but WHY baffles me, especially when some data transferrs correctly and other data just does not. I have a tab structure. The Primary tab contains two sub-tabs (Subtab_1 and Subtab_2). Subtab_2 also contains a portal. The Primary tab is a "cli…

I am trying to gather the layout names in an IF statement. Below is what I am trying to do with multiple options, but can't get it right. Any help is appreciated. thanks! If [RightWords ( Get ( LayoutName ) ; 1 ) = "menu" or "detail" or "details" or "issues" or "edit" or "adminEdit" or "check" or "admin" or "list" or "MSLlist" or "status" ] ******************************* OK, I was able to make the following work. But is there a cleaner way to do this? If [ RightWords ( Get ( LayoutName ) ; 1 ) = "menu" or RightWords ( Get ( LayoutName ) ; 1 ) = "detail" or RightWords ( Get ( LayoutName ) ; 1 ) = "details" or RightWords ( Get ( LayoutName ) …

Hi, I am trying to calculate the results of a field ussing a CASE statement. I have the following fields: Quantity (text) Min Quantity (text) Max Quantity (text) LevelWarning LevelWarning is a calculation with the following: Case ( Quantiy ≤ Min Quantity-1; "Inventory is LOW"; Quantiy ≥ Max Quantity+1; "Inventory is High"; "Inventory level is OK." ) This is not write however as it does not return the expected result... just what seems to be random (tho im sure its logical in its own right) Any thoughts on ware im going wrong would be much appreciated. Thanks in advance.

I am trying to figure out how to make my script check the year and when the year changes, (ex 2010), it resets the auto incremental number. I tried the following: If [Year (Get (CurrentDate)) > Year ( Invoices::Date_Created )] Set Next Serial Value [invoices:Invoice_ID_Counter; 1001] End If But it resets everytime I open up my invoices layout. I want it to only reset when the year changes from 2009 to 2010, 2010 to 2011, etc. Any help would be greatly appreciated.

Hi, I'm a swim coach out in AZ who uses filemaker to keep track of my team. I've been using it for records and personal info, but now I'd like it to do some work at a swim meet. For each swimmer there are two stopwatches. The average time is what gets recorded for the meet. We have been calculating times by hand with a calculator and then entering them into filemaker. I know that seems crazy but I just can't figure out how to average times. The time format for swim is Min:Sec.Hundredths With help from the web I did this and it works most of the time. It rounds nicely and formats things with the correct M:S.H format, but it drops zeros that are leading and not signifi…

Hello! On the sample file I'm including, I've 3 fields: Username Date Time What I need is a calculation (or any other way) that marks records that are duplicated ON ALL THREE FIELDS. For example, in the attached file, it would be record 5 and 6... And also record 19 and 20. I need a calculation that marks all those duplicated records. I do not have much experience in calculations so an easy explanation would be appreciated (or a copy & paste of the calculation better :-) Best! Danny sample.zip

Hello! I have a portal which has an Item Cost field that points to another table. I would like to be able to overwrite this number by manually typing in a number if I want. I can already do this by having it point to a field that has an auto-enter calculation that points to the correct field. So when I create a new record, I see the correct Item Cost, and then I can go ahead and modify it without modifying the original Item Cost in my inventory. The problem is that I didn't set it up that way in the beginning, so if I do enable the Auto-enter field, the previous records have empty fields. How can I modify my records to add this capability or changing the Item Cost witho…

Hi all. I have been working on a solution for tracking monthly payments for various accounts. The problem I was having was that there is a 10 day grace period on payments, so a payment due on September 29 might be paid in November, but should still be associated with the month of September. I read over quite a few posts. Looked at various calendar solutions, and came up with something that seems to be functional, but I would like some feedback and thoughts. Soren (good man that he is) is looking over the project as a whole, but I am curious what others think of the basic idea here. At first, I was going to create record for every month on a "Months" table and th…

Hey everyone, could use your help with this problem. I have a mileage db where i have to keep a running total of miles ran through each state, so I can print out a quarterly report for my boss. I have an "newbie" idea of how to go about this, but it doesn't work. (Go figure ,huh?) Not sure where to go from here. Your help would be greatly appreciated. I have attached my example file for your review. mileagedb.zip

Hi there, I need to add some special validation to a Postcode field so that it forbids entry of the following three strings: "0000" "@@@@" "OSPC" I haven't been able to figure out a calculation that will exclude these 3 strings. Any help gratefully received. Cheers, Steven

Hi Guys, Dont know the most efficient way of doing this however, my calculation is kinda working, but drops out if the first person doesn't meet the criteria. Can anyone help, I would basically like the results of all the case statements - first,second, third child to display underneath. My code is underneath, basically stops if the first record in my portal EVPup::Term of entry field has a lower value than Academic Administration::Year, although the third child or fourth could have a higher value hence I would like them displayed. Hope that makes sense. Many Thanks Jalz Let ([ sixthname = Case((PatternCount(GetNthRecord ( EVp…

I would like filemaker to open the web browser in another window but my problem is that I need it to frame an exact location on a web page. If there is a picture on the web site always in the same location I would rather my users would not have to scroll the web page in the web view but instead have filemaker do the work and isolate a part of a web page. Is this possible? No scroll bars and it frames the exact location. If anyone has a sample of having done this I would appreciate seeing that.

Hi Please help with the following: I want to have a field that makes a calculation with a date from another field and subtracts 60 days or 2 months. How do I do that??? Ex: 15-11-2009 - 2 months (or 60 days) = 15-09-2009 Does anyone know how I do that?

Is there a function in Filemaker to count the number of days when given a date range (similar to Excel's Days360 function)? Thanks for your help.

I hope you can help. I have three dbfs-one is the parent (one to many), objective and intervention. The trouble I am having is the objective dbf counter. The user creates a parent file-then jumps to the objective database to create multiple objectives for one parent record for example Parent dbf unique id Objective 1 (this is counter field) Intervention "1" (in intervention dbf) Intervention "2" (in intervention dbf) Objective 2 (this is counter field) Intervention "1" etc..... This is what I have that is not working Perform Find (based of Primary ID) Set Field [FFSObjective::counter=0] Go to R…

I need our postcode to display in the correct order. I've broken the first part down into 2 fields so I can sort on the text and numbers, but I need to sort the second part of the postcode too. The UK postcode format is usually TTNN TNN, however then first part can consist of just one letter or just one number. There is always a space in between the two parts.

Hi all, New to filemaker and have (what seems like) a simple question. How do I make a field that numbers the entries for the day. I have multiple transactions everyday and need them to be numbered 1-X everyday. For example my fields are: Date, Client name, Item name, Price, Multiplier, and Daily transaction #. I am using Filemaker 10 and Mac os X thanks for the help, Booker

The above is the url where I have made a movie of what I am having difficulty trying to do. I would like the user to be able to click on the yellow repeating field and automate the pasting of the graphic residing into the purple container field.When the user clicks again where it was just pasted it would clear the field. I welcome you to tamper with my field to see if you can make this happen. I posted this earlier but I addressed this differently. This is an attempt to clarify that. I also have included the file. autotrigger_paste_pic.fp7.zip

Hi Im trying to add a pie chart to my solution (not the one attached that just an example for on here) I need to to be able to layout the data in rows and im gettign confused on how to do it without hard coding anything. My example has cities and numbers I want to be able to create two variables that looks like the following birmingham|Manchester|Bristol and say 10|80|10 Where the first is just the unique values from that field and the second is the totals from the number field. I guess it has to be a loop of sorts to make sure both line up correctly but i dont know how how to do this in a calculation. If anyone is bored and could show me that woul…

Hi all, I have a problem with the database I'm crating. Not sure how to do this exactly. Would appreciate any help / suggestions. Situation is as follows: User requested in invoice database to include invoice number field so hi can enter invoice number. I done that, then he additional requested that number in the field should be increased by one every time that he opens new record(that's not a big deal). Main problem is that he don't know for sure from which number will count start and want me to leave it optional for him to write it down by himself and only to do it 1 time in 1st record and when he opens new record and so on each time to go automatically( wri…

I am looking for a way to change the color of text in a field based on a number value. I am a diabetic and when my test result is 200 or more I would like the text to turn red. If it is 199 or less then remain black. I have tried this: Case ( BreakfastReading ≥ "200" ; TextColor ( BreakfastReading ; RGB ( 255; 0; 0 ) ) ) but nothing changes. If it can be done easier please let me know. Any help would be appreciated.

This is the first time I have tried to use the Let function. I have been through my reverences and looked through the forums, but I can't see why this isn't working. Any help is appreciated. Let ( [ field = Get ( ActiveFieldTableName ) & "::" & Get ( ActiveFieldName ) ; length = Length ( field ) ; open_length = Position ( field; " _ " ; length ; -1 ) ] ; Left ( field ; open_length ) ) The idea is to identify the current field and then to cut it back to the last underscore symbol. The next steps, I will add some additional text, ie, "user" & "date" that identify two other related fields. When I pulled it apart into e separate varia…

Hi I have a series of 50 objects (not fields) on a layout. I want to format each as a button to run the same script with the exception of one variable. That variable happens to be 1 through to 50. So I named each object 1 through to 50 and used GetAsNumber (Get (ActiveLayoutObjectName) ) to set a variable, hoping to get the number I wanted into the script. It only returned a "?". Reading related threads (Post#271689, topic id 189716) I noticed someone advised that an object is only active if the cursor is in it, so if this is the case the object would have to be a field and entered to return the object name? Is there an easier way without having 50 alm…

Hi all, I'm trying to convert this function from excel to filemaker: "SUMIF(J21:J36,"=8",K21:K36)" I have 2 rows and each have 15 columns. Columns in row 1 have names from "A1" to "A15" and are all different value numbers that user specify. Columns in row 2 have names from "B1" to "B15" and for them user can chose value "8" or "18". Thing that I'm trying to do is: I'm trying to calculate sum of all values in row 1 (A1...A15) if User specified value "8" in row 2 (B1...B15) but if for example user specify for "B4", "B6", "B12" value "18" then "A4", "A6" and "A12" would be stated as false and would not be counted. Can someone help me with…

Please throw a fried brain a bone! Background: I have a summary field reporting total hours in a given week. I'd like the report to show the week number for the summarized records, plus the date of the monday the week started on "week beginning" would be the head of this date column. Fields: dDate: date field for each record cDateWeekNumber = WeekOfYearFiscal(dDate;2) cMondayofDateWeek = dDate-(DayOfWeek(dDate)-1)+1 Report: subsummary on cDateWeekNumber(trailing) The Problem: sometimes, not always, the report will repeat a date in the "week beginning" column across two different week numbers - perplexing! week beginning week 8.03…

I updated a db from fp5 to fp7 and am having trouble with the auto enter serial number. the db has an item# Field(auto enter serial, under the hood, unique,no user mod) that has never been on any layout in either version. Now that I've imported all my records and done tons of expansion work, the most basic thing is preventing this from working. When I try to add a new inventory record, I get a message about that Field not being on this layout. It's never been on a layout, and shouldn't be. it's under the hood. every other db I have does has an auto enter serial under the hood and not on a layout. no problems. So, I put it on a layout and try to add a new …

I'm looking to see if anyone knows a way to import a map so we can note the locations of our customers. For instance when we enter a zip code in our customer file layout it will place a pin on the map that correlates to that zip code. My boss wants a page that will identify the location of all our customers with a pin on the US map.

Hi Have searched for the answer but can't find. I have a number field that returns a decimal. I would like to round 1.5 and under to 1 and 1.6 and over to 2 etc. I think this should be easy but haven't found the ceiling, floor, round calcs or any combination to work. Thanks so much! Note: Using XP 8.5 Advanced (at work)

Greetings all; In the Database i have a Field called "Wholesale ID". using the product 3322 for example there are two enteries in the field Called "3322" and "3322X" 3322X is the Plus Size selection of the Lingerie. There is also the Manafacture called "Dreamgirl" What i would like to know is if it is possible for the field called "Plussize" to do a calculation to see if there is another entery in the database with the Wholesaler ID = "Wholesaler ID" & X so for example on the record with the wholesaler ID of "3322" it would search to see if there is a record with the Wholesaler ID = "3322X". If there is it returns "Yes" if Not it returns …

Hi, Is there a way to get all the table fieldnames in a list? The only way I found was creating a layout with all the fields from a table in it and call the Fieldnames () function.

Greetings; I was using the formular "Sum(Shows)" to sum the total of shows, However i was wondering if there was a way to make it so that it would count the amount of shows for the calandar month before.

Is there a way to make a button (Script) to lock a record thereby safeguarding the editing of that record? Thanks

I have a student table related to a teacher table. If a student is a male, he gets a 0. If a student is a girl, she gets a 1. In my teacher table, I wanted to create three basic calculations to determine the # of boys and girls in each teacher's class, however, what I thought would be straightforward calculations just aren't resulting in ANY data whatsoever. There are three fields I have calcs for in my teacher table: Total male students Total female students Total Students My thought process was: Total_Female_Students = Sum (Students::Gender) Total_Students = Count (Students::Teacher_IDfk) Total Male Students = Total_Students…

Hello all, I'm trying to build a calc formula that is aware of the number of times a particular value appears in a portal. I've attached a sample file which demonstrates what I mean. Basically I need a formula in a calc field in the child table that says something like, "if this is the first or second time this employee's name appears in the portal, then . Else, if this is the third or fourth instance of this employee, then . I will gladly elaborate on if necessary, but the main issue is how to make my formula aware of how many times the employee's name appears in the portal. I'm thinking it may involve the List or PatternCount function, but I've been unable to…

Hi -- I'm trying something new for me, which is building up the html code for a fairly simple web-page of theater listings from data held in a 3-table relational hierarchy: STATE > CITY > THEATER. The page will simply have one a major State heading, then Sub-headings for Cities in each state, and then several Theaters listed under the cities. I have the 3 tables set up 1-to-many top to bottom, and have created a calculation in the Theater table concatenating all the html Code and theater data in a "Theater_HTML" field. I have a similar field on the City and State tables containing the code to create those headings. I've used Global fields to hold t…

Hi All, I am developing a hotel Booking system in which i have a certain fields. 1.Room No. 2.Check In date 2. Check out date Etc. Suppose i booked a room for any customer between 5-09-09 to 9-09-09. Then the room is booked between 5 to 9 . The problem is when i am searching the status of that room between 6-09-09 to 08-09-09 its shows that room is not booked. Plz assist me how can i handle this issue. Thanks, John

Greets, all: Admittedly, I'm in Dense mode: I want a user to be able to enter a string of numbers for the time, e.g., 123, and have it auto-corrected in a Text field to 1:23...or 1028 would auto-correct to 10:28. Simple enough, right? But nooooooooo...no matter how much I've played with the Left, Middle, and Right commands I can't put a simple calc together to make it work since we're talking three or four digits, here. So I need your help. Now, I thought setting the Field Type to Time instead of Text, but after playing with the settings for that in the Format menu I wasn't getting anywhere. So, am I missing something with Time field type or...? Good thing …

I have a calculation that does not seems to work, I need to get some help with. I pasted the calculation below, what is going wrong is it shows Division 1 and i have 249 Points in the sumary Field. Case(GetAsNumber(Total Point Joint) ≤ 59; "Division 5"; GetAsNumber(Total Point Joint) ≤ 179; "Division 4"; GetAsNumber(Total Point Joint) ≤ 259 and GetAsNumber(varsityGame) ≥ 40; "Division 3"; GetAsNumber(Total Point Joint) ≤ 559 and GetAsNumber(varsityGame) ≥ 75; "Division 2"; "Division 1" ) thanks in advance. Randy

I found a model for using an audit trail to show who made what changes and when. I was able to adapt it to a file I am developing, but I ran into one problem. The solution calls for a global text field which, according to the description, contains one tab character. Obviously, if you enter the field and hit the tab key, it just goes to the next field. I have the solution working sort of, but I had to change the global to a calculation field and use a definition of "....". This gives me a correct result but the output looks a little weird. Does anyone know how to enter a tab character?

Alright, here's the problem. I'm using the contact management template. I have 40 records and I'm using one of the list layouts. I have a field in the standard layout labled "total amount invested." This field is a sum of each individuals various investments that they have made over time Example: John Doe Amt1: $75,000 Date: 9/1/09 Amt2: $25,000 Date: 9/2/09 Amt3: $25,000 Date: 9/3/09 Total_Amt_Invested: $125,000 (sum of three previous entries) Susie Q Amt1: $10,000 Date: 9/1/09 Amt2: $20,000 Date: 9/2/09 Amt3: $20,000 Date: 9/3/09 Total_Amt_Invested: $50,000 (sum of three previous entries) Amt1, Amt2, Amt3 and the corresponding date entr…

This feels like a newbie question, so thanks in advance for your patience. I have a student table related to a teacher table. Multiple students for each teacher. The student table is simple, it captures: Gender (0 = Boy; 1 = Girl) Test Score: 0 = flunked; 1-4.99 = redo year; 5+ = graduates to next grade I already did a calculation that automatically creates a category (flunk, redo, graduate) based on the test score. 0 = flunk 1 = redo 2 = graduate However, now that that's done, I need to summarize all the students data for their respective teachers. I'm simply at a loss at how to calculate the following (looking in the attachment might …

Hello Folks My brain is melting on this hot friday and I can't find the right way to make this calculation. I have many many products that look like that: id --Name -- Price $ -- Extra 001-- wall 2x4 -- 0.30 002 -- partition -- 0.50 (...) 097 -- wall 2x6 -- 0.45 -- .15 What I want is to find the PRICE difference between id 097 and ID 001 and put in in the extra field. Price Id 097 - price Id 001 = .15 How can I do this? I tried with filterValues, and Get(field) .... : I will have many other to do so I guess I'm gonna start with a Case function. The goal …

Filemaker 7 Windows XP AND OSX 10.4 I have a table named [OrderCharges]. In this table are the following fields: ChargeID: Unique ID OrderID: Foreign Unique ID Type: Text field Amount: Number field In the Type field, there can only be Tax or Shipping. I need to calculate the total amount of Tax and also the total amount of shipping based on the current found set. Would this be a calculaton or part of a report that I would solve this? Thank you in advance

Is there a calculation to get the filename of a graphic that is stored in a container field? I've tried, but cannot find one that works. Thanks

I have a client database where work orders are entered and pricing is calculated depending on what kind of visit it is (scheduled, nscheduled, after-hours, etc). Each client record has a field for price since prices can be different for each client. each visit type is Linked to a rate. There is a rate field so if i select "scheduled" as the visit type, the rate field becomes the amount on the "rate_scheduled" field via case statement. Problem: If a client is @ 100/hr rate for scheduled visits and they are invoiced, further in the future if that rate changed, invoiced records reflect the new rate, i would like to have it in a way where once a rate is on a work …

Hi. I was wondering how to separate a word into a combination of letters. For example, I have the word "ORANGE" in one text field and I want to automatically separate it into a combination of three letters... like "ORA" (in one field); "RAN" (in another field); "ANG" (in another field) and "NGE" (in another field) ORA-RAN-ANG-NGE (ORANGE) Any help is appreciated.

I am dealing with insurance taxes, some of which will be allowed(deducted from the premium) and others will be charged (added to the premium). I have a field which will specified "Charge" or "Allow". I want the result (the value of the tax as a percentage of the premium) to be negative if "Allow" selected and positive if "Charge" selected. How do I make the result field show the negative?

Hi I create a layout call Invoice and another layout call Product Cost. On Invoice layout, i had 4 field, Run Format, Run Qty, Extent and Cost. On Product Cost layout i had Run Format, Base Run Qty, Extent and Cost. My problem is that when i key in the Run Qty and the Extent, the Cost field only show one cost for the product cost layout, did not show the second and third cost. Here also attach a example file Need some help for this. Thanks Cheers gregory Re.fp7.zip

I am trying to find a calculation that will convert a frame count to Timecode for 24fps but no such luck. I do not want to use a plug in so that solution is out of the question. Any ideas???

Hi there, I could really use some help with an issue I'm having... I'm might be overcomplicating things as I'm an FM newbie. I'm trying to get "Date of Death: DD.MM.YYYY" to display within a block of text as a merge field if a date of death has been entered into the relevant field. My issue is, how can I get FM to remove "Date of Death: " from my block of text if the field is empty? I've tried using conditional formatting but my merge fields are within a large text block and I can't find a way to just conditionally format the words "Date of Death: " to disappear - I have to select the entire text block, resulting in the entire text block disappearing. I'v…

I am just asking if filemaker has any capability to automate the upload of video from one of its container field with any type of plug-in

I'm an FM noob starting to learn to develop with it. I appreciate any help. Please avoid acronyms and use many small words in any replies (thanks). Table A is a typical inventory table with a WidgetName and several other fields describing the widget. Table B is a VendorList that includes VendorName, WidgetName, WidgetCost. It contains one record for each widget a vendor sells. The same widgets are sold by many different vendors at different prices. I want to get an lowest, highest and average cost for each WidgetName from all the VendorList records that show that WidgetName. Then display that information in 3 fields in Table A. A secondary goal is…

I've been working with dates recently and I've wondering about a few things. In FileMaker when a field is defined as being a date are the following true; 10/5/71 = 10/05/1971 and if that is true what about 10/5/71 = 10/5/2071 This is more of curiosity than anything.

I dont know if this is possible and I am very new to if statements in filemaker but here is kind of what I am looking for: I have a field called FinalPrice which has the final retail price for all the products in my database that have been manually put in. I am also using this field to display the price for the products on my website. I want to change this field to a calculated value which depends on another field(jobberprice) being filled up. So basically the if statement in pseudo code would be: if jobberprice has content in it perform a calculation if jobber price is empty leave finalprice as is (do nothing) Is something like this even be pos…

Hi Hope this is in the correct forum. Please excuse if not. I have a table with a field "Date Received". These records span several years. I want to know how many days have passed from one Date recieved to another. In other words Clients send in jobs to be processed. I record the date. I want to know how long between shipments. Thanks in advance. You guys always are a help. PS This is on XP w/8.5 Advanced.

Hi everyone. I need help displaying Date Modified (Record). Here is the Simple setup I have: Main_Page (Displays Company information; ie Name, Adress etc..) This page has a portal on it which shows the employess of this company Relation ship between the both is MainID = Rel_MainID How do I get the Main_Page modified date do change when an new employee is added to the portal on the page? Right now it only changes when a field on the actual page is changes not when it is changes in the portal? Thank you, Joseph

I have a field that is populated by a checkbox therefore allowing multiple entries into this field. I need to be able to count the number of entries some of which may be more than one word. eg: A field that contained the data number one two four would return the value 3. Can anyone point me in the right direction of how to do this. Many thanks John

I have 2 related tables and on table 1 & 2 there are several dates that are entered. On Table two I want a layout to display the two dates (1 from table 1 and 1 from table 2)and next to it the number of days that have elapsed. I just cannot seen to figure it out. Thanks!

Can anyone give me an idea of what I should do: I am going to start building an inventory / daily sales solution that will run a little different that the norm. what I am trying to figure out is how I should go about correctly figuring out profits if the cost of my items change from time to time? Initially there will be set up costs added to the cost of the first order. But after time there is a possibility the unit cost will change after a few orders of the same item. say 5.00 the first time 4.75 the next couple of times and then maybe 4.00 there after. If this happens is it smarter to make a new record for an identical item at a cheaper price and "disc…

Hi. I am trying to create a small DB for our school raffle that manages tickets sold. I've added a repeating field (30 reps.)for tickets assigned to each student. Is there a way to auto enter the ticket numbers in each repetition and across all student records? Thanks.

I am trying to figure out how to get select data based off 2 other drop downs. I get as far as seeing the list but need it more selective. Here is what happens. I select a customer from a drop down of current customers. Once that is selected I select from another drop down the types of projects available for that specific customer. In the final drop down I select the material (Serial Number) used for that project. But right now it shows all of the materials (all serial numbers) for that customer in the drop down list. However I only want the specific material (serial numbers)for that job. Material is listed in Inventory Table (Carries a 3 digit Alpha …

Hi, I want to caculate for each order my total hours worked on it (this works already) an see a list of the activities and the hours spend on it. In total the hours spend in activities have to add up to the total hours spend on the order. To make it more clear I have a file attached. test.zip

Hey all, I have a contacts table with all names and addresses. I have a second table with location specific information. Every location has a contact from the contacts database so I have placed fields accordingly from the contacts table onto the locations layout. The full name (calculation) field from the contacts database = on-site contact in the locations database. What I'd like to achieve is that when somebody enters an on-site contact it auto-fills the rest of the info (address, phone numbers) automatically based on the info in the contacts database. I think I have all my relationships right but I still can't get it to work and haven't had much luck wit…

Hello everyone, I'm pretty new with filmmaker and need some assistance getting the following done. I have two files one which contains inventory the other contains work orders. I want to create a script which I'm prompted the qty of a part# being given to a person. So let’s say I give a person 10 of part# x. I want to automatically deduct from inventory whenever that person uses one of the 10 part# x I just assigned to him. I hope my question is clear and someone is nice enough to help. Thanks in advance.

Not sure where to put this question. Is there a way to "lock" a record so that nobody can make a change to it unless the record has been "unlocked"?

I have to separate my names in a record . Sometimes there is a "Mrs" or Mr" in the field "firstname" due to an import. I am trying to remove either Mrs. or Mr. from the field firstname and place whichever of the 2 is there in a field called aftername. "Mrs. or Mr. can be followed by the first name so I would like to bring over the first name too. For example the firstname field contains Jordan Mrs. Bessie L. I would like to take Mrs. Bessie L. out of the field firstname and put it into the field aftername. Can someone show me the best way to do this. Thank you

Hi there forum I'm building a grant tracking database for a non-profit organisation and have a field called "allocation balance" that looks at the "amount" and minuses line item expenses in the Expenditure Sub File (portal). Calculation "Total amount - Sum ( Expenditure Sub File::Amount )" I may very well have to attach a sample of the database. Where the "amount" is $275.15, I allocate this full amount in the Expenditure Sub File and the "allocation balance" reads -$0.00 instead of just $0.00. Any thoughts and contributions welcomed. Kind regards Tim

I am messing myself (and you) around with edits to this this post but...)I am trying to do Std Dev by hand because the summary function seems to get it wrong. As you all know : StdDev = sqrt(Sum((score- mean)squared)/N-1) Prospectively I can operationalise this by creating sequential variables to represent each phase of the above StdDev formula, as follows: var_1 = score minus mean (calc. unstored) var_2 = (score minus mean)squared ...or (var_1 * var_1 - as FMP does not have a sqr function)(calc. unstored) var_3 = Sum(var_2) (global) var_4 = var_3/N-1 (calc. global) var_5 = sqrt(var_4) (calc. global) var_1 (score minus mean) works perfectly, …

Hello, I am totally new to FM. I need help,please to have a field on one layout "Contact1" equal a field on another layout "contact" without loosing the records. That means the field contact will have the records on "Contact1" from one layout and the records on "contact". Thank you for helping a new user.

I'm having a mental lapse and can't figure out how to do something that I swear I've done before. The recordset is a bunch of books: how many pages they are and how much it cost to edit them. For each record we want the per page cost. That's simple: cost / pages. When sorted by division we want totals for cost and pages as well as the average cost per page. If I do the average of the individual per_page_cost fields the number isn't correct. If I create a calculation field with total cost / total pages (where they are summary total fields) I get the same result for each division. What am I missing? I've attached a simple file that shows what I'm talk…

Hello and thanks for your help. I am attempting to create a timesheet for the office. I have the layout done and everything works as needed but I would like some of the input to be automatic. Can someone help with a calculation? I have a couple of hidden fields and a few calculation fields: - One is "Regular Hours" and the other is "Overtime". I would like the "Regular hours" field to show actual hours worked up to 8 and the "Overtime" hours field to show the overtime hours. This is based upon the total in a hidden calculation field that is fed by three sets of "Time" fields. I just read this and I hope it makes sense. I will try and uploa…