Calculation Engine (Define Fields)

I have created my business contact database with around 200 fields. All of these fields are in one database table. My different tabs are for example Director 1, Director 2, Co sec, Subscriber, etc. How do I create more tables then link these all up so I can seperate my tab list a lot more easily. So say that my Director 1 tab fields of say 20 boxes are in one database table. Hope someone can help. Thanks Martin

Hi, How can a field be checked for valid numeric value? I used database validation tab to set 'strict data type' as numeric only. But during import (using script) it added the records with field empty for invalid values. Isvalid() function takes value as 'B3' as valid. Another step I tried : GetAsText($TEMP_1) ≠ (($TEMP_1 + 1) - 1) where $TEMP_1 contained the field value. This worked for all cases except where zeros are prefixed with number. Example: '011'. I need to throw a error if any invalid numeric value is encountered. Any suggestions? Regards Tushar

I know filemaker server is the real way to do this, but our IT isn't budging. Is there a way to have a script or something that will create a backup (copy) of the DB file at a certain time each day? thanks in advance!

Hi there, I'm using the new List function, and it's really sped one of my scripts up, but to pass data between several fields, I need to work with a concatenated field. That's no problem. The problem is that when I parse the data from the concatenated field, using the MiddleWords() script step, negative numbers lose their sign. I'm sure there are plenty of ways around this, but does anyone know the best practice/easiest way? Thanks, Dave

I do not use Filemaker much - so please excuse me if I have put this in the wrong area I have a database of artist that is used to export an xml for flash, I need a way to create a balanced index from the names. the index needs to have 6 divisions — a-d, e-f etc which if I had a normal person asking for this - it would be easy and i would just divide the alpha and call it done instead - and I am not sure it can be easily done - I need the splits to be so that the number of artist in each split is as close to being equal as possible with out splitting up the artist in a letter group ie: if there are 300 artist there should be as close to 50 artist …

Help, I've tried to import JPEG files into container fields both ways, picture in FM and using the file reference option. I can import them fine however if I try and click on the field it will not open the picture in 'preview' or any other program. Any ideas? GBR2L

Hi everyone, I am trying to highlight certain words in a text field. The idea is that the user will type a word into a global field and that word will be highlighted in the text. I am trying to make the word capitalized, bold and change colors. This is the script I have. Case( PatternCount(Lower(Contents);Lower(xFind)); Substitute(Lower(Contents);Lower(xFind); TextStyleAdd(TextColor(Upper(xFind);RGB(128 ; 0 ; 128));Bold));Contents) I am using the lower function so the user doesn't have to know if the word is upper lower or proper, they can enter "word" and it will find "word" "WORD" or "Word". The problem is, that although it will find all the words …

I"m having a tough time determining client's ages. I've pasted what I'm using below... I tried a few different things... however depending if I'm looking before Dec 31st in the year previous to the future date (ie Dec 30/2006) - the date as of July 1st/2007 isn't right. However, I think after January 1st it's ok... Can someone please offer some help? CALCULATION 1(Age) 2007- Year (dob) - If ( Date(Month (dob) ; Day (dob) ; "2007") > Date (7;1;2007);1;0) + If ( Month ( dob )=1; .05; If ( Month ( dob )=2; .04; If ( Month ( dob )=3; .03; If ( Month ( dob )=4; .02; If ( Month ( dob )=5; .01; If ( Month ( dob )=6; 0; If ( Month ( dob )=7; .11; …

Hey there- I have a table in my database designed to maintain all of my company's contacts. The table contains a checkbox field with the options "Client", "Consultant", "Vendor" and "Other". Because we have contacts that are both a client and a consultant, I have to maintain the field as a checkbox set. In my projects table, though, I want to be able to show values lists of clients and consultants separately. i.e. Where the user can choose a client for any given project, I want a drop down list of clients only. My idea was to create a calculation field in the Contacts Table, which I could then use to create a dynamic value list. e.g. Case(Contact_Type = …

Hi there, First post on here for quite a while, and I'm hopeful I won't embarrass myself too much with this fairly simple question. I've done a couple of searches but can't seem to turn anything up. I'm building a database that will track tasks for employees, a cross between a scheduler and a to-do list. I need a field with today's date in it, so that portals with tasks display appropriately. The problem is that it's quite commonplace for someone to still be working on today's tasks after midnight, and Filemaker thinks that it's tomorrow. So my solution was to use a calculated field, but I'm having trouble with the syntax. The logic I want to use is this: …

Help please! I have a lineItems table, which contains stockTable items and costs via lookups. The lineItems table has a calculated Summary field - Sum(lineTotal) - so when I do a find on a particular lineDescription, I can see the total value of sales for that particular item - great! However, I would like to show - dynamically if possible - the top 10 selling items. I'm stuck on the best way to do this!? I have a few Heath-Robinson-esque theories, but I'd like to do it right!

Hello all, I am making a dispatch database for a volunteer ambulance organization and in the db I have: A list of radio codes (aka 10-codes) where each code corresponds to its own value... example: 10-63 = Responding to location: 10-98 = Available in the area. When a radio code is selected I would like a description to go into the addjacent text feild with the ability to type further information... example: 10-63 | Responding to location: QB/63DRIVE for mortor vehicle accident 10-98 | Available in area, from (whatever) area I already have the feilds set up all I think I need is a script to insert the corresponding description into the text box.…

I'm trying to create a conditional sum but I'm having difficulty with the calculation. I have an Administration layout that gives a company overview by a variety of calculation fields. I have work orders recurring by month for multiple route men. Each month has a different grand total value due to the following conditions: number of tickets, number of call backs and number of one timers. I want to sum the total ticket value of the current month, omit the call backs and one timers and return the result to a portal field. I know I can write a script to sort the records omitting the conditionals to return a value, but I'd rather use a calculation so that…

Global Field I have a layout, A, using global fields to verify first before saving into the table. There is a 'New' button scripted to clear all gFields so that user can input new entry. There is another layout, B, with similar functions, sharing some gfields too, which the 'new' button script includes. So saying two users are each using one layout. If the 'New' script is performed while layout B is ongoing, will it affects? Have been building the database on a comp, no chance to try it on multiple comps. Need to know this before proceeding. Timetable Which is a good way to create a timetable? Its an optional to my current database but will be more eff…

I want the user to enter a number into a text box, then have a timestamp field update right behind it. I will need several of these on same page. I can make buttons that timestamp, but cant seem to relate a text entry to a timestamp. Prob a easy one, just learning this thing. My next prob is to creat a grahic container that changes if the text field update time is past 12hours.. thanks in advance for your patiance,

This if for John or anyone else familiar with his calc field for restarting numbering for subsummary sections: Case( GetNthRecord(Category; Get(RecordNumber)) ≠ GetNthRecord(Category; Get(RecordNumber) - 1); 1; GetNthRecord(RestartNumbers; Get(RecordNumber) - 1) + 1 ) This calc works great BTW...my question is this: Is it possible to add a calc field to each record that will take a field and divide by the number of records in the subsummary section? For instance if my field NUMBERS is 50 and i have a report that uses your calc to number say 10 records in a subsummary section 1-10 then the calc would be 50/10. this would help me…

I have two fields marked with the Storage option "Use global storage (one value for all records)" One is a date type field and the other is a number type field. When I close and reopen the db, FMP "remembers" the date field, but "forgets" the number field (it goes back to being blank). Why is this and how do I make FMP remember the number field? I know I could create these two fields in a new Table that would have only one record, but I'd rather KISS (keep it super simple) and limit the number of Tables.

I have a customer db that has a serial number which is based on the month & yr i.e. Jan 06 = 2601xxx. Right now I have to go in and change the serial number at the start of each month....any ideas on how to automate this would be appreaciated!

I am a beginning FM Pro user working for a manufacturing company. I am trying to create a calculation that shows how many assemblies are available based on the number of parts that are in inventory. Ex: 1 assembly has 4 parts using 1 part each. Parts in inventory (each part is a number field in FM) part 1--qty 5 part 2--qty 17 part 3--qty 10 part 4--qty 20 Obviously, 5 assemblies are available because the lowest part qty is 5. Does anyone have any suggestions as to a calculation I can use to display that lowest number? Thanks, Greg

Is there a way to make a field required based on the "populated" status of a related field? Sometimes I want to make sure that if a record is revisted to add a piece of information that an a related field is also handled approriatly. Does that make sense? I think it could be accomplished through a second layout that is only accessed as "data update" type form with the newly required field on that form only? I think that would work but is there an easier way, and that would only work in an update mode, not on the fly during data entry? If X-field(not is empty) then Y-Field (Is Required) Thanks, Steve

I am trying to put links to various documents into container fields in a database. (With links instead of storing actual documents, I understand I can then click to open the documents ini Browse mode in whichever software they were created in - all of mine are either Excel or Word.). As long as I don't shut down FileMaker (8.5 Advanced), everythng works fine. If I close out the file and come back later, [color:red]the links are GONE!! [color:green]The icons have literally disappeared!! The blank container field is still on the lsyout but it is empty. . HELP!!!!

I have a table called Projects, and for each project, there is a Start Date and an End Date. I would like to generate reports with a line for each month of the project's duration. Each of those lines would have calculation fields based on related tables. I've thought of doing this with a portal on the Projects layout. In that case, I guess I'd have a related table called Months. But how would I populate the related table? One option might be to write a script that creates new records in Months based on the date range, but ideally, I'd have it dynamically update when the user changes Projects::Start Date/End Date. Is it possible to do this dynamically? Or would I…

Hi, I'm using a container field to view PDFs. It seems that a container field will only show the first page of a PDF. What workarounds can anyone suggest, in order to see more of the PDF's pages? Grant

Hi, I need a field that, upon creating a new record: - Autocompletes with a combination of 3 characters - The 3 characters are roman alphabet letters (A-Z). For instance ATN, VBR, DEX, etc. - The combination must be unique for each record - The combination must be consecutive (for instance AAA, AAB, AAC...BAA, BAB...BBA...CAA, CAB, etc) - I need to be able to define in the initial function or calculation certain combinations that must be omitted (I am upgrading a database from 6 to 8.5 and some combinations are already present). I have a list of codes to omit (comma separated values) that I could include in the function. The secuence must follow a s…

Does anyone know how to use variables in a function? Basically, I want to calculate a value, based on a parameter, to use multiple times in the function. Has anyone else tried this? Never Mind. I did not realize that Filemaker has a Let function. Thanks Anyway.

Hi... amateur of FM here. Trying to design a database Let's say if we were trying to find the average age of car of different colours let's say for example sake.... how can you calculate the for example the average age of red cars.... so more precisely.... A database that has variable of age of car and colour of cars... you have n numbers of red car and y numbers of blue cars and z number of green cars.. how can you summarise the data and find out the average age of each colours of car? Thanks

I have a database with eight fields (S_a - S_h) set up as checkboxes drawn from eight separate value lists (S_a - S_h). I created a separate layout to display the selected contents of the fields with a calculation of, for example, GetAsText (S_a). The problem is that some of the displayed content does not match the boxes checked (a checkbox field may have 3 of 8 values selected but displays 6) and, in one case, contains a value deleted from the list. Any idea of a better way to see only the values checked?

Hi everyone, I want to design suppliers id and its inventory list shown as above. when i click desired supplier i want to see its inventory info. my two lists have already been ready but i couldn't combine them that i want... i prefer table view for two lists. if you are assistance to me, i' ll be very happy...

I am trying to build onto a simple FM application to record inventory transactions for our small manufacturering facility. Records for each batch of components we make are entered into a table including the quantity, departmental source and destination of the components. I am trying to find a simple way to total quantities of each component in each department at any moment. The basic calculation is totalling the quantities of each component with a particular departmental destination and subtract from this, the total of the same component with the same department as a source. Then I want to display the total quantity of each each component as a line item for each com…

Hi, I'm trying to get a field to add the total sales (money count) for the last 7 days from another database, eventually I would like to the same per month. Has anyone done this?

This must be easy, but I just can't figure out the best way to do it. I'm creating an invoicing db, and I want to assign the next sequential invoice number in a field. Like this: 1. New record created 2. invoice_number field is populated with the next available invoice number (largest number from this field in the entire data base + 1) Is there an easy way?

Hi, Suposse you have a field with the following code in the Calculated value > Auto-enter dialog: Let ( [ //define variables: Filtered_Text = Filter (VAT; "0123456789ABCDEFGHIJKLMNÑOPQRSTUVWXYZabcdefghijklmnñopqrstuvwxyz") ; length_Text = Length (Text); length_VAT = Length (VAT); Not_filtered_Text = ? error = If ( length_Text ≠ length_VAT ; "The content of the field would be automatically corrected. Please do not use the following characters: " & ? ; "") ]; If ( error ≠ ""; error; Filtered_Text I would like the value of Not_filtered_Text to be equal to whatever characters have not been filtered(carriage return, b…

I've been using FMPro for a while with no formal training, so I am a bit of a beginner when it comes to a lot of the topics discussed here, so please bear with me. I have two related databases. One is a job database that contains all the processing details of each of the production runs we have. Each record in this database is called a Shop Order, and each has a SO# which is a serialized number auto-entered on creation. The other is an invoicing database that creates invoices for these Shop Orders. Is there a way I can have a field in the invoicing database which will show me which shop order numbers are not in the invoicing database? I look forward to…

Hello, is there a way to calculate the amount of days in a Month? Thanks,

I have a DB with shared data and four tabs below. Each Tab contains 8 Rows by 8 Columns of unique data. The information is used to calculate delivered costs for shipment to 8 different locations from one central point. Each Tab has a field that represents the base cost, without freight, for all the calculations and is placed outside the rows. My problem: 1. I want to be able to "clear" all the fields in a specific Tab without clearing anything else on the page or in other Tabs. 2. I am using an "update" button to handle the calculations for each row (8x8) but if one of the rows does not have anything in field "ID" then I want to clear all the fields in that ro…