Calculation Engine (Define Fields)

I am trying to set up a contact database for someone who knows a little bit about Filemaker Pro and they have asked me a stupid question, but I now I like the challenge of trying to figure out a way to do it. The question is - in my contact database I have a calculation field that creates an account number based on a company name when it is input, but there is also a unique contact ID number for each contact in the databse. Is it possible to generate a serial value for contact ID number for each contact within a company that is unique to that company. For instance if I have three records in my file: Jim at ABC company Steve at BCD company And Ralph at CDE c…

Hi All, I am new to filemaker and Scripting. Could someone possibly tell me the script i need to combine the text from three separate text fields and insert it into a fourth text field with each result on a new line. Many Thanks Giles

Hi - I'm using a container for QT movs. Browsing a record just shows the field as being 'blank' until clicked on. How do I get around this? Thanks! Frank.

I have a flat file of hematological hospital data, imported into FM11. Each lab result is a separate record, and there are multiple types of result, eg total white cell count, % lymphocyte count, grams of hemoglobin etc, and fields for patient ID and collection dates. I need to calculate the total cell type count (eg, lymphocyte) for this dataset, which is basically the total cell count times the % of that cell type, but obviously has to combine data from 2 related records, having the patient ID and the Date in common. I can use a selfjoin to create a portal showing all values of a patient on the same day, but I can't figure out how to get access to "related" record…

Hi all, No matter what user/permission set I am logged in as, this calculation ALWAYS evaluates to 1. The weird thing is, I can take each individual case, and evaluate them in the Data Viewer, and they all evaluate to a big fat boolean 0. Logged in under the user I am, this value should be 0, but it's not. (It's an unstored calculation) Help? Case( Get(PrivilegeSetName) = "SL20Accounting" ; 1 ; Get(PrivilegeSetName) = "SL30VPPublishing" ; 1 ; Get(PrivilegeSetName) = "[Full Access]" ; 1 ; 0 )

I am using the method described at FMLayoutMode.com to allow my users to select multiple portal rows. In a nutshell, they use a button in the portal to set a global field that contains the id of the highlighted portals; Case ( FilterValues ( names:_kg_keyHolder ; names::__kp_ID ) ; Substitute ( ¶ & names::_kg_keyHolder & ¶ ; ¶ & names::__kp_ID & ¶ ; ¶ ) ; names::_kg_keyHolder & ¶ & names::__kp_ID ) One issue with this method is that when a user unmarks all of their selections, it leaves the carriage returns in the field global. Because I am using a script to populate the global field to a child record's field, I want to b…

How can I test the state of multiple fields and come up with a true/false or 1/0? My dumb dumb calculation looks like this: Case ( Get (CurrentDate) ≥ date_invoice_by_field, [color:red]and approve_field = "Approved", [color:red]and not IsEmpty(_mf_invoice); 1; 0 ) Basically I'm trying to test to see if an Assignment is available to be invoiced. Thanks for the help!

Hi, I am an FMP Novice, I'm struggling with what should be a fairly simple calculation. I have a master table (Inventory) containing a list of chemicals and a related table (Item) listing each container of each chemical (uniquely identified by a serial number), the quantity in each container and the status of the container (i.e Stock, In use or empty). I want to be able to record on my master table the total volume of each chemical we have. I have been trying to perform the following calculation in the stock volume field; if(Product Name = Item::Item and Item::Status ≠ "Empty";Sum(Item::Container Volume)) The result i get is a total calculation inc…

I have a couple of DBs - one is a price file, the other is an assessment file. So, in the price file I have product A price $X, B price $Y, C price $Z. I have created a summary average field which averages the values of X, Y and Z, say $BB in one instance. Depending how I find my records it correctly calculates the average for the records found. In my other file I have various products attached to a region - China, Scotland and Canada, say. Under each I have different products: A, B C etc. Each product can only exist in one country. In the product file I have imported a related field which is the price. I have produced a report sorted by country which shows…

How can I apply validation to a field so that it accepts numbers only. My issue is a decimal point. 123 is a valid entry. 1.23 should fail validation.

Hi, Im trying to substitute the lower case letter i for the uppercase letter I in a text field. But i only want the letters to be substituted when the letter i is by itself and not in another word. Here is an example: Before: "Hi my name is Ian, i want to substitute text." After: "Hi my name is Ian, I want to substitute text." This is for an email field that will change when a user enters the text manually. I hope that makes sense. Thanks, Ian

Given the following two sample words: 1. Party 2. Animal What 1 calculation can be used to make these words plural like this: 1. Parties 2. Animal I have tried for 30 minutes and I give up.

This is a simple question, but searching the internet and looking at my books have yet to give me a simple answer. If I set a field as an auto-calc field (instead of a regular/unstored calc) and unclick "Do not replace existing value", will Filemaker automatically update the value when a related field is modified? I just want to make sure I don't stick myself with field that won't re-calculate if/when I change a related value in my database. Thanks!

Hi all, I hope you can steer me here - I would like to perform what I imagined would be a simple function of automatically adding a year to the current date to create a renewal date. However, I am finding this tricky - can anyone advise me? Thanks for your time.

I'm designing a database in order to track the ratings of certain skills in four different zones on a volleyball court and across six rotations. Now, I've got my six rotation layouts done with buttons and the fields for the four zones. An explanation of what I'd like to record is whether a players contact on a ball is rated as a 3, 2, 1, or 0 within a specific zone in that specific rotation. I'd then do that on every serve and for every rotation. I know that I can get this done by just creating separate fields for every zone, every rotation and every rating, then creating calculation fields that just do the averaging math for me. Ex. Rot1Zone13Pass, R…

Is there something like an opposite version of the "Last" function that will find the FIRST non-empty record? I am trying to calculate the time between my first record (with an associated timestamp) and my last record (with an associated timestamp.

I'm trying to find a way to set a file path for a script that is dependent upon current date, month, and year. The script would eventually save PDF's into this folder automatically. The file path I am trying to replicate is "/2010-Exams and Reports/6-June 2010 Reports/1 - Report Preparation/" Of course, I want the year and month to always be up-to-date. Basically, the syntax would look like /(CurrentYear) & " - Exams and Reports/"(numeric number of month) & "-"(Name of month)& " " (Current Year)& " Reports/ 1 - Report Preparation/" Of course, there are no functions to get the current number of month or year that I'm aware of, but if somebo…

hello all, I'm trying to create the perfect calculation for the following situation. I want to have a mailing address block field for every person in my database. I have a state field for people in the US and a province field for people abroad. I want fmp to use state and province according to what is available. I've tried several calculations: first of all, here's the basic one if I were to just compile info for people residing in the U.S. Mailing Address 1 = AddressLine1 & ¶ & AddressLine2 & ¶ & City& If(not IsEmpty(state);","&" "&state)& " " & PostalCode & ¶ &Country (I used this formula speci…

Hard one to explain. I have 2 DBs - File A and B. One of which is a price file (B)and the other which contains amongst other fields a couple of calculation fields one of which uses a price found in the price file. In DB File A I have certain fields which relate to a product. e.g. Product: [field] XYZ Rated by me:[field] 80 {outof 100} Rated by my partner:[field] 82 {outof 100} av score: [field] 81.5 {summary field} However, since some of the scores don't have such a nice easy number, I have another field into which av score is inserted which rounds them up or down to the nearest .5. To do this I use the formula GetSummary(summary field,break field)…

Good day. I have searched for an answer to my question, but have come up short. I'm a novice FileMaker user and am trying to create a name calculation field. The calculation is composed of four fields: 1. First_Name 2. Last_Name 3. Alt_First_Name 4. Alt_Last_Name I'm trying to present names so that you get something like: If only one person is listed, it lists: First_Name Last_Name If two people are listed with the same name: First_Name "and" Alt_First_Name Last_Name If two people are listed with different last names: First_Name Last_Name "and" Alt_First_Name Alt_Last_Name So far my calculation is: If (Last_Name=Alt_La…

I have a calendar program that calculates the elapsed time from a concurrent event until the focus event. For example, while I was 19, I went to disneyland. If the trip to disneyland happened on 1Jun2008, the calculation would tell me that I had been 19 for 0 years, 2 months, 13 days. This works fine. However, when I try to add other events that happened during that same time period (the year that I was 19) the events are displayed in a portal but the elapsed time only calculates for the first line item. Any ideas why. I have attached an image with a different example. In this example you see the title, start date, and end date. On the right are the events…

Hi everyone. I need to generate some auto numbers as the primary key IDs for some tables in my database. How can I do so in Filmaker10? For example, I defined a field called BabyID and set it as "serial number" and "on creation", but nothing happened. Please advice. Thank you! ???

Hi -- I set up a database recently using FMP 11 Advanced but need to work for a client running FMP8. I thought I was keeping things simple, but it turns out that a calc function I used to create an important list made up of a certain field's across all child recors doesn't work in 8? The function is simply List (ChildTable: Field1) How does one achieve the same effect without using this function? Also, now I seem to recall that there's some way of checking a file for compatibility with earlier version of FMP? Is that right? Where's that located? Thanks

HI All, Basically I need a stored field to be recalculated every time the file opens. I have table1 with calculated field1=0 then I have table2 and calculated field2=If(someField>Get ( CurrentDate );0;"") Field2 has to be stored because table1 and table2 are related by table::field1 and table2::field2 The problem however is that because field2 is stored it will always give back the same value (as it was when it was created) regardless of "If(someField>Get ( CurrentDate )" So basically I want to force a recalculation of field2 every time the file is open Any help will be very much appreciated! Amram Chayim Eirinberg

Hi, I'm stuck with a real simple issue - I cannot auto enter values in fields.. I did: double click on a text field, and in the options dialogue, specify the text in the "data" section, and click ok. But the data is not showing up in the field.. So strange... Anyone knows what is going on? Thank you! ???

I have a data-entry layout that includes a button for toggling a boolean field (Number, 0 or 1). Superimposed on the button is an Unstored Calculation that evaluates to a Container with either an image of a checkmark, if the boolean is 1, or nothing otherwise. Clicking the button XOR's the boolean with 1. In Browse mode this triggers the Unstored Calc to show or hide the checkmark. But in Find mode, while the button still works and sets the boolean, the UC apparently isn't being triggered - at any rate, no checkmark appears. Do UC's in fact not recalculate in Find mode? Is there a way of accomplishing what I'm after? (I'm really hoping to avoid having separ…

Not sure if this is in the right place? I am trying to move over a database curently in FilePro to Filemaker. I need to automatically insert data into a field dependent on the current date. EG we add £10.00 to a field for an amount due and a date to another field (Or these could be in a portal record) if the current date is higher than the 'due date' on file, as in a monthly amount adding each month to an account.We need to keep approx 1 or 2 years worth of amounts due and amounts paid along with the dates. I can do this easily in Excel. Any ponters would be appreciated. (Only starting out with Filemaker so a bit of a newbie) < Mike

Probably not a good topic name, but I couldn't think of anything more descriptive. Here is my story: Every day I follow chimpanzees. I follow one from 7am to 12pm and another from 12pm to 7pm. During that time, I collect many samples of their urine. I test each sample for various hormones, one being LH (to check for ovulation). I have a table for urine (Urine Samples) that includes information specific to each one of those samples (what color the urine is, whether there was LH hormone in the sample, etc). I also have a table that summarizes days (Follows). It has information that pertains to an entire follow (how many other chimps they met that day, whethe…

I would like to create a form to display and pay invoices in the style used by Quickbooks. A client's invoices are displayed with balances greater then zero one payment is listed for the client (see hihlighted)then in the payment column the payments are calculated across the open invoices on a line by line basis till the balance reaches zero of any or all invoices reaches zero. A overpayment can happen or an under payment of one or more Line item (invoices) can happen. Can this be done in Filemaker 11? How would this be calculated? Thank you Rich .. QuickbooksPaymentsExample.pdf

In the process of trying to build a custom function, I've come across an issue I can't wrap my head around. I've blown nearly two hours to get to this point... What would you expect this expression to return? //Find the position of the 3rd 4. Position( ¶ & "1¶2¶3¶4¶14¶4¶4" & ¶ ; ¶ & "4" & ¶ ; 1; 3) Is it just me or is something screwy going on? This doesn't seem to be a problem with text, just numbers. I haven't tried other data types. This is FMP10.0.3 Advanced on OSX 10.5.8.

I'm trying to find out if there is a way to determine if a parent record has any child records via a calculation. I know I can do this by creating a script and setting a variable of the parent record and then doing a search on the child table, but to me that just seems like a convoluted way to go about this.

Hi All, Coldfusion Programmer new to FM. I created a table with a dropdown where the user can choose "Live" or "Silent". I am creating a Script that prints the data but I want to be able to SORT so the output is one or the other, but not both. Any help would be greatly appreciated. Thanks...Mike G

First of all I just want to say how helpful this forum has been for someone learning Filemaker. I have created a time sheet database but I need to add some validation. Time sheet records are all stored in the same table. Each record covers a period of two weeks and begins on a Monday. Everything is working but I realized that I need a way to prevent people from creating time sheets that include any of the dates from a previous pay period. Currently, employees choose the starting Monday of the pay period from a drop down calendar and then click on the create button which launches a script that creates the record and fills in all the dates of the two week pay…

Hi all I was just wondering if there is anyway to change the way Filemaker validates records. I would like FM to validate each field as I tab through my layout. At the moment Filemaker seems to validate the fields on submission of the record. The order in which the fields are validated after record submission does not take place in the order they appear on the current layout. Also changing the order the fields appear in a table does not seem to make a difference. Could anyone please advise? Thanks in advance

Hi -- Let's say I have a Team table in which each team gets a unique number, like Team 101, 102, 103 etc. I have a simple child table for Players, so that each player is a member of one team (each team has many members). Each player gets a number, too, 01, 02, 03 etc. and these numbers must be unique *within* the team. I want each player to have a unique let's call it "League Number" that is a concatenation of his Team # and Player # joined by a period: 101.01 101.02 101.03 102.01 102.02 etc . . . My question is how to create the validation calc on the Player # field so that it allows many player to have the '01' number (since every t…

I am not sure if I can explain this well enough but here goes... I have a database where a number of clients are recorded in a Master table. Each client can participate in a number of different programs (for which a child Program table is created). Each client can then have a number of separate contacts with the company in each program, (which are recorded in a Sessions table via a portal in the Program Layout). Thus one client could for example have 10 sessions in Program A and 4 sessions in Program B (each on a different Date). Each session also records the Duration (mins) for that session. I want to be able to summarise the number of sessions over a spe…

I am doing a mailing label layout which I do not want my logo to print unless their is data entered in the addressing fields. I can make a text field contain data by using a length calculation on the addressing field. But if I use the same calculation on a container field aka my logo it does not appear to work. There also does not seem to be any script or conditional format that will change the "hide when printing" status of a field. Any help is appreciated

I know I have found once a topic about this but can't find it again: How can you limit the available values from a list in order to prevent duplicates? I would like to use this in a field (person ID) using a value list inside a portal where I select the name of a person but hide it afterwards in such a way that the next related record will be impossible to have the same person ID.

Hi all. We enter orders the day after they are sold. I would like to auto populate a field to accurately reflect the date sold. So Tuesday through Friday I need it to populate current date - 1. But the tricky part is that on Mondays I would like it to populate current date - 3 to show that it was sold on Friday. Could someone help me out with a formula to do this? Thanks

I have a set of records that are sorted alphabetically from A to Z by the value of the field CLIENT and need to assign a sales rep by CLIENT for each location. For example, I might have 10 records for Company X, but they are 10 separate locations that need to be managed by the same sales REP. I need to assign a sales rep's first, middle & last initials (e.g. FML) per CLIENT group for all locations. After the records are sorted, I want to popup a dialouge to ask me for the sales rep's initials for the first CLIENT. Once I enter the initials, I want it to go to the next record and use the previous value entered if the CLIENT is the same, regardless of the location. WHen…

Hi All I've been trying to count the number of times a text fields content appears in a related databases field (it's a repeating field). I'm beggining to think that you just can't do it with a repeating field even if you "GetRepetition ( repeatingField ; number )" and count the number of times it occurs. Can someone please help, I'm going mad counting the number of ways I've tried to do this. Flook

Greetings, I have some ideas on how to do this, but I wanted to hear what others have done to accomplish this. I normally use Calculated fields to just have the data show up from a related table. One-to-many relationship usually. Recently, someone asked me to make it possible for him to Override the field if he wanted to. I wasn't sure how to proceed.... A calculated field, will always stay in sync with its related data... The only way I can think of producing this override feature would require it to be an "auto-fill" text field. If I were to change it to an "auto-fill" field it just fills in once, so if the other table was updated I would neve…

I have a table containing date fields, which are basically holiday dates. In an unrelated table I want a calculation field which is a flag to say whether a date entered is listed in the holiday table. The method i'm using is by 1, creating a valuelist based on the holidaydate field for all values in the holiday table. I then created a calculation field in the holiday table using valuelistitems to have all the values in a field. I was then going to to a test If(not IsEmpty ( FilterValues ( Bank Holidays::holidays all ; Working Date ));1;0) But is doesnt seem to work. I apologise if this has been answered elsewhere, but i searched the forums an…

I often use the GetSummary calculation but I'm trying to figure out how to do the following. GetSummary(summaryField;breakField) I would like to allow to be able to "choose" from a value list of field names in the table for the breakField. So if in my table I have 3 fields CUST.NUM PROD.NUM SALES The summaryField in the calculation will of course be SALES. I've created a field called BRKField that references a value list with the field names. CUST.NUM PROD.NUM I could would like to choose one of these fields as the breakField in the GetSummary calculation. Any help on how to do this would be appreciated.

Hi everybody, I am new to Filemaker and currently struggling to set up a calculation field to display photo based on the photo link from another field. The database I am working on containing over 100,000 jpegs and each record has a photo as proof of service. Manually importing those photos would take several month to complete. I have 2 photo fields: Field A contain the link or path to the location of a photo stored on my hard disk. This information is imported from Excel data spreadsheet. Field B is set to display the photo according to the link from field A. Example from the field A :C:photosMay2010IMG_2121.jpg Working out the calculation …

Hi there! I am trying to find a way to create abbreviations semi-automatically according to the way a user enters text in a field. For example, a table (which will be used as a VL) with two fields: staff specialty, specialty abbreviation. I would like the user to enter let's say TeChnician (in the 1st field) and a calculation produce an abbreviation (for the 2nd field) i.e. TC - taking into account the uppercase letters of the words entered. Is there any way to implement this? I couldn't find an expression to evaluate whether the letter typed is upper or lowercase. Perhaps someone could enter a special character before every letter to be considered as part o…

How have a field that is a sum a various fields. One of them is a percentage fee that needs to be applied 50% of the time. the percentage is calculated automatically, and I'd like to know how to be able to toggle that field on or off. is this possible? thanks

Hi There, Sory I have been looking forever, and all I can find is how to add todays date to a current date. We run a training facility and ever 12 or 36 months thier certification runs out, I have created a formula If (Attended = 1 ; Date ( Month ( Event Date ) + 36 ; Day ( (Event Date) ) ; Year ( ( Event Date)) ); "") Attended ; That they attended Event Date: The day of the Course 36 = months this certification is good for ( I would like to be able to change is say as a varible depending on the course... IE First Aid is good for 36 Months, WHMIS is good for 12 Months, etc.) And I keep coming up with todays date + 36 Months any help?

I would like to have a calculation that would could calculate elapsed time, so that when x number of minutes pass a conditional format would be applied. So far, the calculations that I have attempted do not keep up with realtime, they perform the elapsed time calculation based on dates and times already in a field. Any help would be greatly appreciated.. Thanks Chad

Hi all, I have table1 and I would like to be able to see all of the records of table1 from all other tables in my database. Here is the tricky bit: I want to accomplish this without needing to establish a relationship between table1 and each of the other tables. My approach was that in table1 I created a calculated global field (gListItems) that returns the values of all the related records from a self joined relationship (using the List function). (Global fields can be viewed in any table without a relationship) My problem is that when I show this field in the other tables or even in the native table (table1) the field only returns the value of 1 recor…

Heres my issue.. I have created a database to enter competitors in a car show what I need to do is take the points for each competitor and add ( SUM) them so I can award the Club Participation award for the highest amount of point.. So if club A has 3 cars with 100, 200, & 300 Points I want a field to total the 600 Points in a field and if club b has 100, 100 & 100 Points the field will reflect that that club has 300 Points.. Does Filemaker have the capability to sum the total from a particular field from multiple records? If it does. Please respond with a simple explanation of the calculations... Thanks and Regards! …

Is there a way to encrypt certain fields (e.g., credit card info) in a database, and then display them normally when appropriate?

I'm starting with modifying the Starter Solution Time Card. I've added more entries to the day types and added a second set of In and Out times to each day. No problem getting the calculations for work hours to function correctly for this. The only stumbling block I've encountered so far is I want to generate hours worked using two sets of conditions. Condition 1 calculates work hours based on "day_type" = "Regular" Condition 2 calculates work hours based on "day_type" ≠ "Regular" A bonus is calculating overtime as anything over 40 "Regular" hours, which excludes any other type of "day_type" since those don't contribute to overtime pay. I haven't b…

I'll try to describe my problem as detailed as possible... I got 2 tables. Clients and Products. On the client layout i got a 3 dropdown lists "EmailProd1", "EmailProd2" and "EmailProd3" and they are getting their values from the Value List ProductList from the Product Field "ProductName" so that the dropdown list contains of all my products. I want a button now that sends an Email to the clients email and IN the email, it should write out the "PruductName" AND a short description from the Products field "ProductDesc" In the calculation, I said to write out "EmailProd1", "EmailProd2" and "EmailProd3" but how do I make it write out the 3 different descr…

I have a load of room records with Area and Capacity fields and summary totals (sArea and sCapacity). I'd also like another field to summarise the ratio of sArea/sCapacity but this doesnt work. I tried average of area/capacity too. Getsummary seems to work but only for the subsummary defined in the break field. Thanks for any help.

Hi -- Lets say I have 2 tables an Employee Table and a Department Table. The Employee has the following fields (pkEmpID, fkDeptID, Name, Title) and the Department table has the following fields (pkDeptID, DeptName, DefaultTitle). Assuming I have Table Occurences the same name as the table and have the 2 TOs joined by fkDeptID. What are the benefits of having the following: Employee::Title ... Auto-Enter calculated value Lookup(Department::DefaultTitle) over Employee::Title ... Auto-Enter calculated value Department::DefaultTitle :)

Hi all, I have 2 fields, one field the user fills in, the other I would like auto populated with an “X” whenever the first field is not empty. I think on another day I might be able to figure this out but not today. :help:

Hi I have a small database which collect inf. of selling, in the invoice layout I have a field(number) that calculate sum of sold value. I need to make the calculation which can convert this number to be text and put into another field but I have no idea to do that (ex. 1205 to be one thousand two hundred and five dollars). I need help from you all.

Hello, everyone. At the place I work, we have a database for the invoices that we send to customers, and another database for the payments we receive for those invoices. We are interested in figuring out how long, on average, it takes each of our customers to pay us back. I've added a field to our invoice database, which is calculated as follows Invoices::DaysToPay = Payments::Date - Invoices::Date This should result in the number of days between when the invoice was issued, and when it was paid. Next, in our customer file, we have this calculated field Customer::AvgPaymentDays = Avg( Invoices::DaysToPay ) Where Customer is related to Invoices b…

First off, my Filemaker Pro "Skills"... I know how to create some relations, simple scripts and calculations but nothing advanced really... I got a database with several Layouts and Tables. 1 Layout and Table, Staff Management, is for adding staff and Database users. Then I have a layout and table, Client Management, in which I want to create 3 Value List based from the Staff Management In the Staff Management i got a field "Name" and a Checkbox "Department". The Checkbox gets its values from a Value List "Departments" and has the custom values: "Sales", "Marketing" and "Admin". Now, I want 3 different dropdown lists, Sales Rep:, Marketing Rep: and…

I have a field with two numbers in it (nfield1) which are separated with a dash e.g. 19-20. I want to add these numbers together and take and average and post to another empty field (nfield2). Not sure how to do this... Some imported records have the numbers in a format 19/20 - same thing. Having obtained this av. number e.g. 19.5 - I wish to convert it to a number on a 100 pt scale e.g. in this case 99. Is this just a question of using the Substitute function? Thx for any help.

We are trying to find a way to set how much to pay for mileage based on date. Example: anything before May 1st 2010 is 46 cents per mile and anything after that date is 50 cents a mile. I'm thinking that we can use an If function to determine if the date is before or after and then based on which it is pay a certain amount for the mileage. Before we would have to create two seperate mileage reports and manually go through and assign them by date and its honestly a big pain, any help would be greatly appreciated.

I think a specific answer would be a lot to ask for so I'm looking more for opinion and maybe some probing questions... The attachment shows a wall with plywood splices and for reference is laid out using spreadsheet type columns and rows. Note that the portal doesn't match the drawing. In this case the parent table, containing the Wall as the parent already includes over 2000 fields. I've spent 8 years trying to break up this table and after many attempts have given up. Here I need to be able to: - Calculate and create these plywood pieces based on the size of the wall (up to 3 pc. x 15 pc. or 24' high x 60' long) - Identify the upper left corner of each pi…

Hi all I have created a layout that creates an order within my database. I would like to create a button on that layout so that when I press 'Submit' Filemaker will create an invoice that is printable and reduce my stock levels by the appropriate amount. I'm sure that this is a function that Filemaker has been used for many times. Does anyone have any resources they know of that I can look at, i.e a tutorial or a demo database? Or can anybody point me in the right direction on what I should be doing? Your help would be greatly appreciated. Lee

I have inherited a database with six text fields each of which can have one of 30 two letter values. I would like to combine (concatenate) the values for each record into a new field and then perform a find for a specific value within the newly constructed field. I am not sure if one of the FM Text Functions can search a string and find a pattern. Any help would be appreciated. I may be wrong but this seemed a better option than constructing a dynamic find on each of the original six fields.

I have a problem extracting the earliest date in a set of filtered and sorted records. Both the 'GetRecord' and 'Min' functions return the lowest record number, rather than the record with the earliest date. I have to sort by by name by month, quarter and year (separate layouts) and i want to show the actual earliest and latest dates in the Trailing Grand Summary. Thanks for any help

Hi, i have a invoice database with a portal , in the portal the field "item number" which show me inventory records from another database. what i want is that the field " item number" shows me the inventory items and also another inventory that i have in another database. Can i do that? can the field shows me the records from two different databases? Thank you

My database includes records for Korean "clients." I would like to find a calculation that will add a space between each character in their names. For example, go from this 안중태 To this: 안 중 태 Anyone know a way to do this? Thanks in advance for your input. Lisette

Interesting that the calculation: "=" = "≠" returns true, not false. Exact("=";"≠") returns false as you would expect. It appears that the equals and not-equals symbols are treated as the same by FM.

How can I create a date field (called latest date), such that the field will evaluate a known set of dates (date1, date2, such that it will pick the date that is the most recent and populate the 'latest date' field with it. The 'latest date' may be 'date 1' for one record, but it could be 'date 9' for another record. Thanks so much. Thanks edited, i figured it out, I was using the max command improperly, it works now

I have a field in which I enter a 0 or 1 as the result I only want to enter data in the second field if a 1 is the result of the field prior In other words "High School graduate?" - 0 or 1 would be the result "College Name?" - i only want it to pop up if HS graduate is a 1 How do i do this?

Hello and I really appreciate any help you can provide. I have a FileMaker app that is broken into two files: an interface file and a DB file. The UI file is using the DB file tables as external datasources. The versions are FileMaker Pro 11 files hosted on FileMaker Pro 10 Advanced Server. I have an ACCOUNT table occurrence that has about 6 "to many" relationship to a TRANSACTION table occurrence. The 6 occurrences form "transaction type" filtered relationships such as travel transactions, training transactions, labor transactions etc. I also have a "to one" relationship with an ACCOUNT_SUMMARY table. The ACCOUNT_SUMMARY table has many unstored calcu…

I have a database where the numerical results of a subject's blood test is created. The test is the same, just offered at different times (which are random times). So there is a field bloodtest1 and Daysafterappt1. (so the blood test1 is the result of the test, and the daysafterappt1 is the days after an office visit). The same are paired for bloodtest2, daysafterappt2, etc and so on up to 6 blood test results and 6 "days after appt". OK, here is what I want to do. I want to categorize the results in each record as a range of the following. 0-5 days after appt, 6-15 days after appt, 16-45 days after appt, 46-100 days after appt, 101 to 200 days after appt, …

I have a report that has a subsummary part. There are a couple of summary fields that are in the subsummary that are summing records that are sorted into that part. I need to be able to write a calculation that uses those summary fields and displays the result in the subsummary part. What seems to be happening is that even though the calculation field is located in the subsummary part it is calculating on all records in the found set. So my result is across the entire found set and not constrained to the records sorting into the subsummary part. Anyone have any suggestions? Thanks in advance.

I need to create a calculation with several repetitions. It would appear that this is possible, since there is an option to make a calculation repeating. However, I can't seem to find information on the function and/or notation that will define the parameters of the repetitions within the calculation itself. Specifically, I want to split up a single field with 1-20 alpha-numeric "words" into a calculation where each "word" sits in its own repetition. I have no problem isolating each "word" using the MiddleWords function, but what notation do I use that tells it that each of these "words" needs its own repetition?

Hi, as the title says, I am wondering if this is possible. I need to get the sum of several repeating columns, i.e. Repeating-COLUMN-1 field-1 + Repeating-COLUMN-2 field-1 + Repeating-COLUMN-3 field-1 = Repeating-COLUMN-4 field-1 Repeating-COLUMN-1 field-2 + Repeating-COLUMN-2 field-2 + Repeating-COLUMN-3 field-2 = Repeating-COLUMN-4 field-2 At the end, I need to sum the Repeating-COLUMN-4 which is not a problem. I've attached the table I built for this and the base model XLS file. http://www.filesavr.com/archive_5 FM file user name: admin no password

I have 4 tables of records, I then have a 5th table of records that these relate to, a summary record if you want to call it that. In my 4 tables each record has a date. What I want to do is have the summary record in the 5th table show thr highest record date taking into account the date in each 4 tables. Table 1 01/01/2011 Table 2 10/02/2011 Table 3 02/01/2011 Table 4 05/01/2011 Table 5 would display the record from Table 2. I know and have been using the Max function to get these into 4 seprate calculations in table 5. I just can't wokrout how I can get them into one field, this field showing the highest date from the related reco…

I found an example of how to print a booklet in post #159084 that I thought would be helpful to my situation, but isn't exactly what I need to accomplish. I can see needing some of that logic, but can't figure out what I need to do. I need to pring 3 "forms" on a 8.5 x 11 page with the order going "through" the job. In other words: if I have 12 forms (typically there would be several thousand) to print, page 1 would have forms 1, 5 and 9. Page 2 would have form 2, 6 and 10. Page 3 would have form 3, 7, 11. Page 4 would have form 4, 8 and 12. After printing, the pages would be cut into thirds and the forms would all be in the correct order, 1, 2, 3, 4, 5, 6,…

There is a function "IsEmpty" how can I do the opposite the filed is NotEmpty?

I basically need an address label. I have the fields: firstName, lastName, address, city, state, zip Instead of individually copying each field to an email i want to create a field that has all those fields already in it combined. Is this possible through a calculation? i tried a merge field but for some horrible reason you cannot select its contents. This would be of great help.

I'm seeking a function or calculation (explained) that, given: filterStartTime filterEndTime startTime endTime returns the quantity of hours/time present between the start time and the end time within the filter range. So, for example, in a timesheet, we are only looking for 'overtime' hours worked between 1 am and 6 am... filterStartTime = 1:00am filterEndTime = 6:00am employeeStartTime = 12:30am employeeEndTime = 11:00am This function would return 5 hours (the time between the filter values). Excuse my abusive labor practices, this is only a concept. Any thoughts/functions/insights that anyone has used?

Greetings, I have a feeling I can find a workaround for this... but at this point, I am curious what others have done. I have a series of calculations that must be performed to get an ultimate result. The issue is, it is about 9 calculation fields deep.. and each subsequent calculated fields, relies on a previous field. So by the ninth calculation... all 8 other calcs must be evaluated before it can perform the ninth. This is causing major slow downs by the end of all this. It handles the first 6 with ease.. but once I start getting near the end... it is a snails pace. I know this has to do with indexing... and since these are from relat…

Greetings, I'm having a problem, hope you can help me. I have 3 tables, 2 of the related | num | | >event | inevent | | >Event inevent And i would like to take the value of one::event and put it into two::event, but when i do that calculation in the field in two it doesn't put anything. I have tried making it global and it doesn't put anything on the field, and when i don't make it global it says . I see this in the Data Viewer window. I see that one::event has X value but i don't see it shared to two::event. What I'm a missing? Thanks for your help Faustino Cantu

I am trying to set a field to pull a random number each time a record is created. I have a field named "Dice" with a calculation "1" - "100" * Random And a field named "randam_number" with the calculation Middle (Dice; Position (Dice; "-"; 1; 1) + 1; Position (Dice; "."; 1; 1)-2) At first this seams to work just fine. I get numbers like 59, 18, 89 or 7. Buy every once in a wile I get a blank field. Any ideas.

In a block of text, I'd like to use substitute to swap out every other occurrence of a text string, or every one within the paragraph except the first. Ideally, I'd like to vary by number of occurrences (every [fourth] is unchanged). Are any of these possible? Example Google stock is through the roof. Will Google exceed all expectations? How high do you think Google will go? Go, Google! could become Google stock is through the roof. Will GOOG exceed all expectations? How high do you think GOOG will go? Go, Google!

I am trying to create an array of dates that are a week apart and list for x weeks. So if a starting date is 4/25/2010, and x = 4, I would end up with: 4/25/2010 5/2/2010 5/9/2010 5/16/2010 Struggling with how to do this.. any insights would be most welcome.. TIA

after leaving school, i thought i'd be finished with complex math and suddenly i find myself having to evaluate the following formula in fm: Floor(32768*POWER(10;(-3*Field1)/(16000*$B$1))+0.5;1;1) all i was able to accomplish is to replace the expression power with the ^ symbol, but fm complains that there are too many parameters in this function. i have no idea where to fix it. i do know that the result should be 30760 if the value in Field1 is 293. reading my post, i must say i completely empathize with everyone not responding to this question :

Hi, Is it possible to create a repeting calculated field? The table has already a field with 999 repetitions, I want to create a calculation that takes in consideration each repetition, sort of a mirror calculated field. Is it possible?

Looking for a little help on how to approach this. I have a field that automatically generates the age of a person based on their date of birth. What I'm trying to figure out is how to generate a string of text on the condition that said person is over 18 and then place the string of text into a field before their full name. The text string would consist of the parent's name (located in another field), the text "A/N/F of" and then the child's first name, middle name (if they have one) and last name. So the end result if the child is a minor would be " John Smith A/N/F of Jane D. Smith" Any help would be appreciated. Thanks

This is perhaps a simple inquiry. If Address 2 does not exist in a record, how can I tell the calculation to ignore the line break command? If I have no state (for example if I want to send something to Washington, D.C.), how can I tell the calculation to ignore the comma and space command? I'm new at this and have tried several things, but have yet to find the solution. Thank you in advance for your assistance.

Hi all... I have just started to explore the wonderful world of calculations in FMP. I hope that you can help me with the following inquiries. So I work for a educational grant organization and we have just started building a database for all of our past recipients. Right now I'm trying to figure out a way to split grant information for recipients who have received more than one grant. This is a sample of the type or information and format that I have now: Name: John Grant Year: 1961/1999 Grant Category: Research/Degree Study This is what I'm aiming for: Name: John Grant Year 1: 1961 Grant Category 1: Research Grant Year 2: 1999…

This is probably a really simple request, and one which someone can probably answer in 10 seconds without any thought, but... I am self-employed as a freelance translator and my actual file organisation system works in the following manner, recording the file path of the file as follows: [3 letter client/agency ID code]/[YYYY]/[MM]/[my own reference no.] This is how I identify where the file is stored and it also serves as my own reference for putting on invoices. Previously I used to enter all of this information manually, e.g. ABC/2010/04/21a-6608.50, where ABC is the client code, 2010 is the year, 04 is the month, and the remainder is my own refe…

Hey, so I'm new to this program, or databases in general. I'm trying to just make myself a personal database for all the sheet music that I own to organize and easily find them. One field that I'm trying to check off is the instrumentation of the sheet music, but I want the checkbox to pop out. There are like a billion different instruments in these scores, and this field would take up like a quarter of a page on its own. Any way to do this?

I have 193 students. I rearrange them in different orders to show their status in different subjects. How do I let them see their rankings in the group by associating a number each time I rearrange them. For example, in math it may be 1. Salley 2. Joe 3. Sarah But in English it may be 1. Joe 2. Sarah 3. Sally

I have a field that when modified I would like the db to show the name and date of the person who modified the field. I can't seem to figure out how to get this to work properly. Any help would be appreciated. Thanks

I would like to calculate a text field that would compile a list of "Names" with "Mode" = enabled: Fields Name Mode Calculation Text ----------------- For example, Record 1 Name = Fred Mode = Enabled Record 2 Name = Jim Mode = Enabled Record 3 Name = Mary Mode = Disabled Calculation text would be Fred, Jim Any advice or assistance is greatly appreciated, thanks in advance !

Hi all, I'm trying to use 'PatternCount' function to give me the count of text in a field from all records. My syntax is PatternCount(text;List(TableName::FieldName)), However it only counts the text in the active field - How do I get the count of text from all records? BTW The records are native, not related. Any help will be very much appreciated! Amram Chayim Eirinberg

I know how to change the way a date displays in FileMaker -- 4/17/2010 and April 17, 2010. That's NOT my question. I want to send a email from FM with date displayed as April 17, 2010 so that international customers can easily understand it.

I am trying to achieve the following. I have a field called Number. In this field there can be any positive integer. If the integer consists of 2 or more digits then I need to split them and sum them. This I need to repeat until I have a single digit number. For example: Number = 37 Result should be 3 + 7 = 11 -> 1 + 1 = 2 (final result) or Number 134 Result 1 + 3 + 4= 8 (final result) Which functions can I use to split numbers? Which function can I use to repeat this process until I have a single digit?

I've seen the file path and file size (custom function which used length (container))of a container file displayed in a separate field and I was wondering what other pieces of metadata can be displayed. Last modified would be pretty useful but even more so would be a few user-defined custom metadata fields I have in some of the documents. Basically it would save me having to open the document, look at the fields and type in what they say. The main format I am using is .odt (open document text). If anyone knows whether this is possible, i'd appreciate a yay/nay Thanks! Zig13