Calculation Engine (Define Fields)

I'm looking for a way to create a serial# in an orderdatabase, based upon costumerID and total of records from the same costumer. Example: Company1 has costumerID 523, and has 3 orders (records in DB) booked. So serial# 1 = 5231, serial# 2 = 5232, serial# 3 = 5233, etc. Company2 has costumerID 631, and has 5 orders (records in DB) booked. So serial# 1 = 6311, serial# 2 = 6312, serial# 3 = 6313, etc. I guess it's something with a calculation, but I can't figure it out...

The main case: I have a invoice system that is based upon discount if quantity is over certain steps. Lets say one product costs 470, and every items over 10 costs 430, and every item over 20 costs 400 and so on. The number of steps is not the same for every product. So the calculation should be like this (for one item) Quantity: 27 The 10 first items (1...10) = 4700 (10*470) The 10 next items (11...20) = 4300 (10*430) The 7 next items (21...27) = 2800 (7*400) The total amount for 27 of this product would be would be: 11800 (4700+4300+2800) Total discound: 890 (11800 - [470*27]) Price per item: 437,04 (11800/27) How on earth can I m…

I'dd like to have a numberfield that shows a timecalculation in numbers from a timefield. Example: 'total working hours: 3:15', and then have '3,25' in a numberfield.

I have an Service db. Staff enters Procedure Code and Length of Service and the Procedure rate is displayed from the related Procedure db. Then the c_LineItemTotal (calc field) displays the total. However, there are times that 'special circumstances' arise that won't fit the formula. I can't add an item to Procedures, because I want the proper Procedure to display and be tracked. I also have a n_LineItemTotal field (num) and, when invoices are generated from the Service db, the c_LineItemTotal calc SETS the n_LineItemTotal num field (for posting). I thought of having an 'override' function (I know it's dangerous but a necessity) which would allow a user in the Servi…

hello , this is what i am trying to do . i have a field with a value list of 1-50 .depending on what value i choose i want another field to divide that by 8 and then round it up to the nearest 1 eg 8 =1 good 12= 1.75 bad ,i would like 9 thru 15 to =2 and so on i can get the calculation to give me the BAD results but not the GOOD i am sure it is dead simple thanks paul Apted

Im sorry for bothering you guys with such a simple matter, but in one of my files in FM scrolling records using a mouse stopped working. Any ideas why and how can I bring that useful feature back? It works for rest of my files. Thank you for your tips.

We are using FMP 6.0v3 on Win 2000. I have a date field that users put a date to follow up on items, it is stored as a date. I compare that to " status(currentdate)" in an IF statement and return either "Due" or "Not Due" based on the situation in a calculation. The user can then run a script that returns any items marked as "Due " . Sounds like it should work without fail if the calculation field evaluates every time that the referenced field changes its value, but it doesn't. There are some records that do and some that don't. I can't even see a pattern in when it does or doesn't evaluate. I can periodically fix it by running a script that I create…

Hi, I'm pretty new to FileMaker, having worked mainly with SQL DB's in the past. I'm trying to do something that would be simple (to me) with SQL but it really baffling me with FileMaker. I have a database of Orders, which has fields like OrderID, Date, Placed, and Shipped. Then I have another database called OrderItems, which has OrderID, PruductNumber, Pieces, and OnOrder. OnOrder is supposed to be the sum of OrderItems::Pieces for all Orders that have been placed, but not shipped. I have a Layout in Orders with a portal to OrderItems, the portal has the fields ProductNumber, OnOrder, and Pieces. The idea is that when I type in a product number int…

I am very new to this forum and this App, so please, use 4th grade english if possible. If I were creating something like a movie rating survey where each record had a rating (1-5) and I wanted to see the average that all respondants gave for each movie, how would I do that? Example: 5 forms were returned to create 5 records. D-Day was one movie that each respondant rated. Record 1 gave it a 5, Record 2,3,4 gave it a 4 and record 5 gave it a 3. Each record has a field called Average which gives a rating of 4 since it is the average of 5,4,4,4,3. I assume that the Field 'Average' is a calculation and I have found the 'Sum' formula but it only works on each …

I am having a problem. I need to create a report of sales on Items. The total sales of an Item is sum(saleitemsQty). But how do I report the sum of sales this year, Last year, this quarter, Last quarter etc. the only way I can see to do it is to find and set field with sum of sales but this would be very very long winded. How should I go about this?

Just a quick question: I have a global container (Text). It is in a database FMP Pro 5.5 that is served up on our local network. If I make changes to the global field on the host machine, not a problem. But if I access the database from any other machines on the network and make a change to the global field, the change does not seem to be "Global" across all the records. Is this just the nature of the beast? LR

I need an elegant solution for appending sequential serial numbers to parent file ids in a child file. Scenario: I am trying to calculate investment performance for multiple investment portfolios. I have two files: a portfolios.fp5 (parent) and a performance.fp5 (child). The performance file keeps monthly performance data and has cumultive performance calculations, requiring me to use a self-join so that I can use data from previous records (ie., 1 month back, 6 months back, 120 months back, etc.). This is no problem really. The problem arises when I attempt try to track more than one portfolio in the file. I can track the performance of one portfolio in one file and…

I need to parse several fields from a screen dump. The word count changes so MiddleWords doesn't always work. Here's some examples: CBEFDKR Work Order Display Page 1 of 2 Site ID 001 Account Number 619832-19 @CM Cust Info MARIE EALY Status ACTV 1/04/02 Maj Min CBEFDKR Work Order Display Page 1 of 2 Site ID : 001 Account Number 631748-01 @CM Cust Info ANTHONY J BIONCI Status ACTV 12/28/01 Maj Min I need to parse the account …

I have a field sitting in a header area that I cannot modify. It is a numeric filed that i want to be able to enter an inventory number in but cannot get the field activated. This is being done in a DB that was already existing that I hve edited. Has anyone else had this problem?

I have a database with First Name and Last Name. When users enter a new name I want it to check to see if that First Name and Last Name have already been entered. I know you can check the unique box in the field definitions, but how do you check 2 fields at the same time.

I'm making a db in which video editors can log video according to time-code, which is in the format HH:MM:SS;FF (hours, secs, mins, frames.) How can I force their entry into that format without requiring them to enter anything other than numbers? I'd like to keep the whole entry as one field - that's where I'm having a problem

This is very hard for me to explain. So I need to give specific examples. I have a field called Program Name. It's a pop-up list, and an example would be, "SHP Level III, Oct 29-Nov 1, 2002". I need a calculation that will separate Oct 29, the beginning date, so that I can then calculate for individual records when a balance is due. Can I do this without having to create two separate fields-one for Program Name and one for Program Date? I hope this question is clear. Thanks!

Hi, I am hosting databases in a separate computer using FileMaker Server. I have defined some global fields, and I need to change their value quite often. The thing is that when I close FMPro and open the databases again, the value of the global fields hasn't been updated. (Goes back to the original value). How can I solve this?? Thank you.

Hi I am using Windows 2000 FMP 5.5 Let's say I have three text field name called A, B, and C. The business rule is that I can have one, two, or more the combination of B and C as long as the records that contain of A is the same, but I can not duplicated that combination again if the record has diff data of A. For example: Record # contain A contain B contain C 1 01 B1 C1 2 01 B1 C1 3 01 B1 C1 4 02 B2 C1 5 …

I do realize this is probably an extremely dumb question, but here goes. I am following a step-by-step instruction on how to create a calendar. The instructions say to put my weekdays as a global field with 7 repetitions. I did that just fine. But then it says not to forget to enter the days so I only have to do it once. I see in the example they give that they have the days of the week at the top of their calendar, but I see no place to enter days! It doesn't give me an option for that in define fields, it's not a pop-up list. So where do I type the information?

Oh I got sooooooo many questions today! OK! I would like to create a field that would contain all the results found from another field. In other words If I have a e-mail address field, how do I have a another field conatin all the results of all records found in this field? I not exactly doing this but this was my best example. RET

How can I do this? I have two databases that use the first name and last name of the other databse. I would like to pick the first and last at the same time in this new layout. can this be done? RET

Dear users, I am sorry if this subject was explained already, but I could not find anything. I have a database with users working over the network (both mac and pc), win2000 and FM servers. Each record contains at least one picture and they are stored on a server as a reference to a picture. Problem: I have a headache asking users to mark "Store only a reference to the file" while attaching pictures. But they keep forgeting. Is there any way to automatically mark that feature? Or is there at least a possibilty to detect whether a picture was attached as reference or not? All I want to do is to disable users attaching pictures directly into the FM database. Thank you…

I have a first Name field and a last name field, can I have a third field with both of these values combine? First name: Bob Last Name: Smith Third: Bob Smith Thanks, RET

I have a database with a series of layouts that are worked through in sequence when entering data. In the last layout, there are 3 fields with a "not empty" validation option. The problem is that as I enter new data into one of the earlier layouts, I am constantly getting a validation error message telling me to change layouts and fill in the fields that require a value. I only want this validation error message to come up when I am actually in the layout that contains the validated fields. Is this possible to do? Thanks in advance. Cooperj

Our billing data entry person makes frequent mistakes. Each therapist enters the 'time' the service took, in a different format. The manager doesn't want the Therapists to change. It slows data entry down and causes errors. Some Therapists list Units (15 minutes); some list total minutes and some list % of an hour. If they enter 75, she doesn't know if they mean 75 minutes (5 Units) or 3/4 of an hour (3 Units). I want to attach a 'Method' to each Therapist in the Staff db. U for Units, M for total minutes, P for percent of an hour. Then, data entry can simply type what they list, and I want it to revert to units for her. If a Therapist lists 255, she has to figure h…

i thought i'd share with you how i spent my last couple of hours. this is a calc (result = time) which, for simple entry, converts a number into a time with proper separaters, observing plus/minus. i'm overlaying the entry with the calc which is set to not allow entry, so on exit you see the format filtered result. the number field is validated to allow values between -235959 and 235959. for example: 5 becomes 0:00:05 -324 becomes -0:03:24 this makes for nice data entry and it works, but in case i didn't see the forest for the trees, perhaps one of you gurus can think of a less convoluted way of accomplishing the same thing. Cnv is the number field to be…

I have a new Service DB with 6,000 new records. I was unhappy with the existing ID and want to use Capkurt's formula to reassign sn's to all records; and then begin auto-entering it. I cut and pasted the formula into a script. SN is a text field. But it keeps saying it can't find field '6' from the first line. Is it because I'm not actually in a record and it refers to CurrentRecordID? Is there a way to run a script through all my records and assign according to this formula?

I have been constructing a suite to help track my graphic arts business. My problem is that my job numbering scheme uses an alphanumeric set-up based on the client such as Joe's Garage would be JG-001. I have created a client list that contains an entry that stores what the prefix is and I am able to have my job list relate to that field and automatically enter the prefix, but I would like the job number field to look at the prefix and then create a sequential number for that prefix only. In other words each client has their own running sequence like JG-001 or BTI-123 depending on how many jobs have been done for that client. Any help would be greatly appreciated.

I have a custom message to be displayed if a validation failed. The message is look liked below: 1. Only authorized user may edit data 2. Unlock CWC before editing 3. Select Labor Code from List 4. Estimate is in Approved/Hold status 5. Labor Code & Org Code has been used on other CWC The length of the above message is 188 (chars) I understand that I can have up to 255 chars in my custom message, however it just displays only (when validation failed) 1. Only authorized user may edit data 2. Unlock CWC before editing 3. Select Labor Code from List 4. Estimate is in Approved/Hold status 5. Labor Code & Org Code has been used on That

I am creating some fields like phone number. Here is what I have phone1 , phone2, phone3. The calc is phone1 & -phone2 & -phone3 if one of the numbers begin with a 0 it elimates its. Example: fiedls 800-XXX-0XXX Views 800-XXX-XXX How can I get around it? All fields are number fields. I tried text fields with same results.

Is it possible to define a calculation (or script) that finds records based on a given search criteria and then automatically pastes the results from one field into another field that is repeating? I have a database of 500 individuals that can be grouped into about 300 families according to their family id number. I want to divide the families into two categories (let's say "A" and "Z"). The category to which any given family belongs is based on information only found in the head-parent (be it the mother or father)- which makes it very difficult to apply the category to the children. The only way I can think of applying the same category to the children is to create a…

Okay...here is... I have a database called CDROMS. Each record represents one CDROM. There are 8 fields in each record that represent the individual songs in each CDROM. The titles are something like jazzsong, bluessong, happysong, sadsong etc. The problem is that the user that created this data base did not assign separate fields to each song (ie. song1, song2 song3) but instead assigned a relational field to another database. So all eight titles of the song are contained in 8 fields called :title. How do I do a FIND based on each title to the other database if I cannot distinguish the fields in the record? Is there an internal array or dataname that can be referenced…

I'm converting to .png files for my database. On two seemingly identical systems, one has the .png filter in the import picture dialog and one doesn't. What gives? Both running FMP6 on XP pro.

Okay, it seems simple enough, but I give up. How does one define the following fields and get them to play together nicely. The fields are: Gross price = Net * 1.15 Net price = Gross * .85 Sometimes Gross price will be entered first into the database and sometimes Net price. How do I get them to behave and not tell me I'm attempting to make a circular definition? I've fumbled long enough with the "If" function to know that I don't know enough. Can someone please help me, immediately if not sooner. :crazy:

Can anyone answer either of the following: 1. How would I craft a Calculation that returns every Thursday date? 2. How about a Calculation that returns a sunrise/sunset times associated with that particular date? Or would I have to manually construct 365 records?

I know from experience not to put calculated fields on a list layout. Especially ones that calculate sums over a relationship. I do not use them on lists. Now in a products DB, on the Inventory layout I have 4 fields to hold 'Items Sold', 'Items Rcvd', 'Backorders' & 'Inventory'. I have a button for the user to click on to run a group of Set Field Commands and recalculate those fields. For reports I use a script to Replace those fields with calculations before printing. I have never liked that arrangement. Now today I am questioning whether my logic is correct in this case. Could users please share their wisdom concerning the use and placement of calcs (especially one…

I am having a problem that is crashing filemaker on a semi-regular basis. I am taking photographs of the students in the college using a web camera and troi grabber (plug-in), the photographs are being stored in a container field in a photographs file that has two fields student id (text field) and photograph (Container field). My student file and others are looking in to this file to allow photographs to be printed on forms etc but for some reason certain photographs are crashing the system. Has anyone else had this problem?? Is this a filemaker problem a Troi Grabber problem? Any help is greatly appreciated???

I'm trying to enter a number (ie. 1.25) in a number field but I keep getting the message that I need to enter a positive number...so I enter 1,25 and it accepts it. The field is formatted as a decimal with 2 fixed decimal digits and with a decimal separator. Any ideas how I can make the field accept my entry of 1.25?

Hi, I'm trying to create a relationship that will allow for one file to count a certain subset of records from another file, which in turn is pulling the information for its records from another file. For instance, I want file B to count all records in File A that have pulled in the ID "A" from its related file AND that have then been designated as "option 1". When I try to create the relationship, it fails because the concatenated field ID.option which registers "A option 1" can't be indexed. I don't understand why some calculations can be indexed and some can't - and what do I do to make this one index so my relationship works? Thanks in advance for help! …

I have created a field - checkbox(number) it is formatted as a checkbox with a value list of 0 (zero). I also have a field countcheckbox(summary) count of checkbox. All works well until I click checkboxes within that layout then the summary field goes blank until I click somewhere in the header, then the correct count shows. I have tried to refresh the summary field using a button command to force the check mark and then refresh the screen to get the correct count and still keep me in the same record but that doesn't work either. Does anyone have any suggestions. I just want a running count in the header of checked items, am I going about it in the wrong way? …

Another simple thing (I think). I've searched and can't find info on this. I need to import 28,000 names from db1. They are displayed as LAST, FIRST. I want them in two fields as they should be. I created a FULL NAME text field and I've imported them, but I don't want to re-type every name in the two fields. Surely a script can split them for me? I have another db2 with FIRST LAST full name entries. I don't want to import them until I've figure out how to group-apply a script to fix the first 28,000.

I have a DB in which a single record contains the times of events happening in the course of a day. From set up times to meals. I need to create a list of all the events happening in a day chronologically and I don't want the list in a table form. Is there a way to create fields which determine and display chronological order?

Hi All, I'm very new to FMP, so please excuse me if this is a really simple question. What I'm looking to do is have a field that displays a running count of the total number of times a value entered in another field within the same database. Example: Field: Project Number - There will be multiple records within the same db that contain this same number "1" ,"2", "3",etc. Field: Project Count - This is the field that would have a running total of number of time "1" appears in field "project number" …

I need to parse out a 5 digit number out of a text string. It can appear anywhere within the text so it can't be a left or right word calc. Is there a way to parse a 5 digit number out? It would seem simple. Also it would be nice to be able to get the 3 letter code following the number. Some example strings. zJobs1 Archive:34939-oul11-2:IT - PDF Folder: zRip2Disk Archive:35727-HOR-2(RSIdata):Default Job:Primex12.eps:Stats: Anchor:55587-afp-02

I have a database that I want to link to scanned images from a book. Actually they are marriage records from an old book that I have scanned and indexed by name, date and witnesses. How can I create a link to the images? What might be the best way for the user (on a CD) to search the data, find the record they want and click to view the image (which is currently a JPG). I was thinking of putting them all in Acrobat but I don't think I can add a database to Acrobat. thanks.

This should be simple, but I can't make it work ... Premise: I need to know how many years an employee has worked for us, to calculate their monthly benefits. From 1 yr to 10, they get a certain number of vacation days (1=1, 2=2, etc. After 10 years, they get the flat 10 year vacation benefit (10 days). I want the number of vacation days they get, to be in a field, so if the 'benefit package' changes, a user can change the 'formula' easily witout redefining the field formula. I can't even get the formula to work to calculate number of years employed. Geeeezzzz. Both dates are date fields. Vacation benefit day is numeric. And, how do I specify to stop calcu…

I have a basic data entry screen. I always test things on my 70-year-old mother who knows 'nothing' about computers! If she can't mess it up, and can figure it out, then I know all users can easily use it. Here's the thing ... after she entered data in a field, she hit [ENTER] and it kept wrapping to the next line. Geeeezzzz. Question: When to [TAB] or [ENTER] to move through fields in records, or do I have any control? If at least her cursor didn't move anywhere, she would remember to tab; but I've tried 'locking' the field -- it still spreads. Not only does it look ugly, but it could scare some users!! Any way to stop the wrapping in set fields?

I have a client that wants to find the Mode of a high price of his found information. Does FMP have a Mode function? If not - what can I use to do this within FMP. Thanks

How do I, through a script or some other means, define a field named "date" type "date" and have that field autoenter itself on every new record I create, bypassing the File>define>field>route. I would like to be able to enter the date into a field on the layout that will replicate itself on every new record. I know it is easy going the file>define>field route and entering the date into the autoenter>data field but I do not want anyone to have to go through that route. any ideas? Do I need to clarify this post? thanks for your continued help. KKalvin26

Hi, it's me again. I have this database for my foreign exchange operations and I'm trying to make it as user friendly as I can, amongst the things I wish to include is a scanned image of my supplier's invoices. I've created a container field to show a thumbnail of the invoices, because the full sized images are just too large for this layout. What I would like to do is that when the user clicks on the thumbnail, it opens in full size, so he can view it with more detail. How can I do it?? Thanks from Chile Matias

Can someone point me in the direction of a good tutorial or instructions on how to build Parent-Child relationships in FM? I have a number of general business types (Retail, Restaurant) that I want to subdivide into more specific groups (Retail:Mens Clothing, Retail:Womens Clothing), but I still want to be able to search and sort on the Parent cat... and I would like expansion flexibility to further narrow the Child cats (Retail:Mens Clothing:Shirts, Retail:Mens Clothing:Slacks). (I know how to do this in MySQL, but FM stumps me).

Hi, I'm new to FileMaker and I've just created a database to keep track of my company's bank operations (loans and stuff). The problem I have now is that we used at least 3 different curriencies due to our international market. What I'm trying to do (and I hope some of you will help me accomplish) is the followin: Whenever I open my "Banks" database I want Filemaker to ask me for the current currency conversion value for that day (I work mostly on chilean pesos, but I also use US dollars and euros), after that I want Filemaker to calculate the total balances of my debts in chilean pesos, according to the currency value I entered at the start. I could do this enter…

I have a text field called Name, and a text calculation field called Get_the_Name defined as Status(CurrentFieldContents). I was expecting Get_the_Name would show the contents of Name when I clicked in Name, but it doesn't. What don't I get?

Who can help me with this: I want to create a field that will auto enter the current date and a two digit serial number; like this: 200802-01, next record 200802-02, etc but every day the serialnumber must begin with 01 again; like this: 210802-01, next record 210802-02, etc I hope I'm clear enough with my question!

Can anyone shed any light on the behavior of calculated fields in a multi-user situation, in general what happens when the calculation uses global fields to derive its result. And specifically when using an external function such as a range plugin in a calculated field which uses global fields to derive its result. I know that each user gets its own globals but what about the calcs that use them. Thanks in advance to FM Forums "The Best".

I have a file that is shared between my secretary and myself that is used for passing messges that are viewed in list form. When I have a message I want to send her, or vice versa, it is as simple as marking if for the appropriate person and viewing the found set. My problem is this. When she sends me a message, I would like the message to be visually marked in some way to catch my attention in the midst of all the other records that may be displayed at any given moment. (These other records may be to do items, call lists, etc.) Ideally I would like to find a way to change the background color or have a colored field or something like that to alert me that she has sent me…

Some help PLEASE! I am trying to store PSD files in a container feild. I can put PSD files in fine but when I copy & paste them back in Photoshop I loose all the layers. Does anyone know how to keep the layers? Thanks

In a calculated field, how do I find the date of two days ago? Today - ? Thx

I am building an in-house solution that is a stepping stone toward a solution with interface and data separated. In this solution, I am using graphics to draw attention to certain data by using a calculated field to display an appropriate graphic if certain conditions are met. So what is my problem? I have the images stored in a global repeating field within the file and the calculation pulls out the appropriate repetition. This all works fine up to this point. Where I run into problems is that if I clone the database for distributing to another location using a separate FMServer installation, the images that I have inserted are no longer there without me going in a…

I have 3 files, Customer - Invoice- InvoiceLine. I want to define a calculated field in the customer file which is the total of the sales or invoice values for the current financial year. In the Invoice file, the Invoice_Value is Sum(Invoice|InvoiceLine:Invoice_Line_Value). I tried the following Calculated field in Invoice file to isolate invoices that fall in the financial year ; (European date format) If(Invoice_Date>31/04/2002 and Invoice_Date<01/05/2003,Invoice_Value,0) I would then define another sum() field in the customer file, using the Customer ID relationship. However, it doesn't work, always returning 0. Any suggestions …

Hi all! I've got another little stinker for you guys and gals out there in FM land. Is there anyway to generate a checkbox list from a portal? I have a database where I am scheduling classified ads, and there is a table of all the different newspapers that they may be printed in, right now there are seven papers. Then another table stores all of the scheduled instances of this particular ad, different combinations of publication and date. So I have a portal now that lists every publication that an ad will be printed in. What I WANT is a list of checkboxes, one for each possible pub, filled or unfilled depending on whether or no the ad will print in that pap…

Hi there... yes, I am STILL working on the billing and employee db I have been working on, but actually, I can see the light now. Anyway, I do have a question, if someone has the time... Sometimes in an accounting system, although standard values are pulled to a field via calculations, the financial person in charge of data entry may want to change the value. Is it possible to create a calc field that allows a user to change the value? I can't imagine this hasn't come up previously, so I am hoping that one of you will be able to help me. TIA, KC

Hello Forum, I'm a beginner working on creating a database for my e-commerce store. I noticed in the sample files that there is a field called a "customer id". Each order placed on my site has a unique order number which is generated by my shopping cart program. What is the advantage of having a unique customer id field when I already have a unique order number? If I have a repeat customer and I don't want to have multiple files of his/her address and other repeat info, yet I want to include in my database that this person has ordered more than once, which direction should I take in terms of designing my database? …

Looking at the Text functions, it doesn't seem they support regular expressions of any kind! Is that so? Is there any reasonable way to do things like find the Position of the first numeral in a Text string? Parsing info out of fields and imports would seem to be bread-n-butter for a database app; in fact, FileMaker would be the only one I've seen that lacks regular expressions.

I am trying to create a calculation field that performs the following: I put a time in of 5:05pm and a time out of 6:10pm I want the calculation to round each time to the nearest 15 minute mark ie.. 5:05pm would round to 5:00pm and 6:10pm would round to 6:15pm then calculate the total time. In this example it would calculate 1hour and 15 minutes, and report it as 1.25 (decimal format) any ideas or help would be greatly appreiciated. KKalvin26

I've defined a field called 'menu' and set it to be a required value. Then formatted it to be a pop-up menu using a value list named 'menu_vl'. Defined button to perform script called 'get menu choice' which should evaluate the value chosen from the pop-up and branch to a subscript. Script sample: If("menu= "menuitem1"") Perform Script(Sub-scripts, "gotomenuitem1layout) Exit Script EndIf If("menu = menuitem2"") etc... Simple concept. Issue: When in browse mode the pop-up doesn't pop up and there is no apparent action taken with the exception of the button movement and appears to be a screen refresh. :? BTW - sure would be nice t…

I'm sure this is a rank newbie question, and its not strictly neccesary, but is it possible to refer to a grandchild related field directly? That is to say, if I'm in file A, and its related to B, and B to C, can I type a calculation in A with this in it: RelToB::RelToC::FieldInC

I am having a problem with a inventory file matching part #'s. I have a master list of master part #s (approx 800,000 records). this file contains a manufacturers part # and a cross reference to my part #. I have an addional inventory file which contains up to 3 alternate part #'s (alt1, alt2, alt3) and a cross reference to my part #. My problem is when a get an order from a customer. they may give me either the master part #, alt#1, alt#2 or alt#3. i am trying to relookup my part # into the list they sent me. the only way i can see is to create 4 relashionships. do a relookup, change the defination, do another lookup, etc... al manually. is there a bet…

I want a field, which contents should be randomized. I would like to have one field numeric and one alfanumeric. The randomizing should be in a range specified of me, of course. How do I do that?

Hi, I'm trying to define a calculation field that will count the number of times another field is modified. For example if field "Trigger" is modified, field "Counter" is incremented by 1. Any ideas? Thanks - Grahame.

I sure this is a simple answer, but for some reason my brain does not seem to remember anything today. I have database that contains a number field for lets say....Money charged. each record also conatins a date field. How do I make a feild that will show the total amount charged for lets say MAY 1st. In other word it would add up from all the records found the total sum. HELP! RET

I have a field that is supposed to auto calculate a finance charge. But - I am having diffuliculties with the order that I need to write the calculation in. I want the finance charge to be calulated if Today's Date > DateDue +30 or if a field (FCMarkOff) is not checked. Here was my thought: If (TodaysDate>DateDue+30 and FCMarkOff = "=",AmountDue*.015,0) I get a little confused on the logical operators (or, and, xor). Also - I am not sure if this is the correct way to do the entry for a checkbox in a formula. I have the calc entered this way in my current database and the Finance Charge field is not being calcualted when it should. …

Hi , Does anyone know is it possible to do this in FM? For example: I've two columns:Col A & Col B Users A & B can view both column but user A can only edit col A.(The same apply to user Thanks

I'm trying to make it possible for students to schedule appointments w/ staff via Web. I've gotten to the point where a student can search for available times, select and submit an appointment time. But the return info is not specific enough. The database is set up according to the weeks of the semester beginning each Monday(ie, Week 1=8/26/02, week2=9/2/02,etc) and the available appointments are set up according to the day of the week and the time. A record is created for each available time. A typical record looks like: Week:1, DayofWeek:T, Time:10-11a,Available:Yes,StudentID#:(blank),Name:(Blank). A student can go to the website and search for available appoin…

I have a database of names that I would like to send letters to from time to time. The way I have handled this so far is just to have a simple letter layout with merge fields for specific data. That, however requires me to go to layout mode everytime I want to change the letter, which I don't want my user to have to do. I would set up a related file which has fields which contain the letter data, but I don't know how to incorporate merge field data into this letter text. Any ideas?

I am trying to get a count of unique employee ID numbers for those employees who not only work at a specific site, but whom also have an AM work schedule. In the employees file, I have all of their personal info as well as their expected regular schedules. So, I developed the following CASE statement, trying to get the count (Monday AM is the field that holds each employee's Monday arrival time): CASE ( Site = "Austin" and Monday AM > 0, Count(Employee ID), Site = "Rochester" and Monday AM > 0, Count(Employee ID), Site = "San Francisco" and Monday AM > 0, Count(Employee ID), Site = "Nashville" and Monday AM > 0, Count(Employee ID), "…

I'm stumped. I have five fields for tracking numbers of students in a class each week. The fields are defined as follows: Count1 is text. Count2 is text with auto-enter calculation: Count2=Count1. Count3 is auto-enter calculation: Count3=Count2, etc. What happens is when you enter a number in Count1, it automatically pops into Count2 thru Count5. This is as it should be. But then what I want is when you MODIFY say, Count3, I want Count4 and Count 5 to have the new info. The only way to get it to work is first go and delete any values in those fields (tedious). I've tried defining fields as number, instead of text; I've tried a self-join and a Lookup instead of Specify…

Hi, I want a simple formula for working out the tax week from a given date ie 16/08/2002 is tax week 19. Is there a way of doing this remembering that 53 can be used if the date is the first week of the following year. Many Thanks

For some reason my calculation fields formats the character string %20 as 0 only. Is there a way to get around this problem or am I stuck? Cheers Steve Griff

hello How to make the combination of three fields to be unique. bye geethanandh

There is no documentation on what the mathematical operators really do with Dates in the FileMaker help. Does anyone know? From experimentation, it seems that the type of 'Date - Date' is Date, instead of Number. If I define a field 'Age' that is 'Status(CurrentDate)-Birthdate' I get a Date in the first century displayed. Furthermore, I can change it to compute 'Year(Status(CurrentDate)-Birthdate)' and it works fine and gives me pretty close to my age in years plus one (its off by the difference in leap years). Further, 'Date / Number' also results in Date. I expected it to convert the Date into a Number (of days) and then divide the two numbers. But instea…

hi all, i have a little bit of trouble with a simple math using fields from related databases: what i'm trying to do is to create a field that substract the value from a field in a related database from the value of a local field. the value of the field in the related database can change whenever.... how can i define i field like that. i define a calculation field that like this: field calculation = local_field - field_from _related_database but if the value in the related db changes the calculation doesn't work. i did a field that shows the changed related value and that one does change when the value in the related db changes but the calculation field doesn't do…

I have a calculation that looks at a checkbox to see if it is checked. Depending on the state of the box the calculation returns either a yes or a no. It works the first time. If I create a new record the calculation returns a value of Yes. If I check the checkbox the calc. returns a value of no. If I uncheck the checkbox the calc. is stuck on no, it wont switch back to yes. Here is the calc. I am using. Any suggestions? Case(Completed=1, "No" Completed =0, "Yes", "No") Do not evaluate if all ref. fields are empty is checked. Do not store calculation results is checked. LR

I am still working on the ledger I have been building, but I am very close now to completing it. I currently have a situation where a Calculation field called "Debit" will not hold the value I give it. I have worn out my patience with it and will now hope that after some explanation, someone here will be able to help me. I have three files: "employees" - Holds all info about the company's employees; each employee has a unique ID. In addition, this file holds the general "schedule" that the employee has agreed to, as well as calculations of how many hours he is working per week and his regular monthly pay. Important fields are: Weekly Hours (calc…

Hello, I am a newbie to FileMaker Pro, using the 5.0 version. As far as I can understand it is advisable not to use repeating fields. Why does the FilMker pro 5.0 template Purchase Orders use them? I am trying to create a simple orderform, the repeated fields seems to work fine in the file as such except for when I am trying to perform searches and creating reports. I have got three files. one product file ( with product ID), one Client file ( with Client ID) and one Order file (with OrderID) Ideally, I would like the portal Ihave set up in the Client file to correctly display total productID with description for all the items purchased by a particular Client…

I need to have a calculation field, "salutation" that can change to different fonts depending on the user's choice. I'm doing that by having many fields on top of each other. For example, salutation_arial=if (font="arial",salutation,""). I do that for a whole bunch of different fonts, place these fields on top of eachother, and let the user choose the font. Here's the problem: I don't want to have my font value list be a list of fonts that I choose. I want the font value list to be populated with the actual fonts that the user has in his computer. Is there any way to do that? Is there are any status function or perhaps a plugin where I can set a field "fo…

How can I validate the contents of a field based on the contents of another field? I tried (in validate by calculation): If( field1 = "yes", field2 <> "yes", "") but it didn't work. And then I thought, what exactly is the difference between putting a calc in the validate by calc area rather than just making the field a calc field? And then I remembered that I have other info I want to keep in that field and that I just want to make sure that both fields cannot = "yes" at the same time. What am I doing wrong? TIA, KC

FileMaker's Round(number, precision) function is nice, but I need it to round up all the time. I will not be dealing with negative numbers. My scenario is a material calculator for a machine shop

I have a date field in a fmp 4.0 database and currently have the following calculation to make sure the user enters a date in the future. date > Status(CurrentDate) but now i need to change it so the user has to enter a date that is at least 1 day in the future. e.g today is 13/08/2002 so the earliest date the user can enter would be 14/08/2002.

hello I am new to filemaker. i have a user database and DVD collection database. A user wants to add some DVD's as his favorites. how should i implement ? Pls help me. Bye Geethanandh

I need to convert a user-entered number formatted as "1.25" to time as "1:30" for time billing purposes. The entry field is numeric, and my calc field is time using "TextToTime(number_entered)." The result I get only seems to work with values entered as whole numbers (1, 2, 3). When the decimal value is entered the calc field does not display any value, regardless of its length or how it is formatted. Any suggestions? Thanks, Mike

Anyone's Help would be greatly appreciated.... In a template that comes with V5.5 there is a file called Calculations and Scripts. Within that file there is a calculation that calculates the future end date of a project excluding weekends when given a number of work days. The calculation for the end date is as follows; StartDate + Int (WorkDays/5) * 7 + Middle ("12345012340123601256014560345623456", (DayOfWeek (StartDate) - 1) * 5 + Mod (WorkDays,5) + 1, 1) I need it to do the opposite. I have an end date or a Project delivery date. I need to put in the number of days it will take to complete the project and end up with a start date or the date …

For some reason I can't seem to be able to enter data into field(s) inside a header. The field(s) format is checked for allowing entry and on other layouts with the same field(s), I can enter data. The header has become frozen at the top of the page in browse mode (I don't know why!). Can some one tell what I did or how I can fix it. Also the freeze frame is great, but How did I do it?

Hello I have a database that I use to print mail labels and recently my agency requested that I now print the labels with all upper case print. I have simple text fields ie.. Business Name, and Contact 1.. these have information that is not formatted to all upper case, I searched around the forums and I am not sure how I can take all 2000 records and convert them to upper casing.. I see a function named upper("text") but I am not even sure if it will do what I need please help.. kkalvin26

I have a simple database that keeps track of our product movement. At the end of each month I manually add these numbers by hand. My fields are <<Date>>, <<Sales>>, <<Margin>>, <<Inventory_Balance>>. I now have over a years worth of data. I want to show on a columnar layout this years data and right next to it last years data for a report. How do I get 2001's data to show up next to 2002's data. I of course can print them in one long list but I want to compare current month to last years month on the same line.

Hello! FMP 5.5. Mailing list application. Two related files: "households" and "dancers". Household records are related to one or more individuals (dancers) on "household_id". Some household records have a single e-mail address which serves all the members of the household. I have a calculated field which formats them like this: "John Smith, Jane Doe" <[email protected]> (and leaves the field blank if the household doesn't have an e-mail address). Many individuals also have their own e-mail address, and these are listed on the individual's record in the "dancers" file. I also have a calculated field to format the "full e-mail address" for individual dancers. Eas…

Hi I wouldn't say that this is bad. You will probably want to store all your pictures in a folder on one machine that will be the "Host." The other machines connect to the host. In the Host FM database, images are inserted and stored as a reference. If images are stored in the FM database and are NOT stored as reference, the size of your file will increase greatly as images are inserted. Some is OK, but many images WILL slow down operations in FM - believe me. If you store as a reference, you will not be able to export images, and if you delete the actual image from the HDD, it will not show in the FM container field. Storing a reference means it reside…

I'm trying to get some rather large (2+ Mb) TIFF files into my Filemaker 5 database. For some reason, no matter how I try to insert these pictures, only about a quarter of the picture shows up in the container field. I've tried inserting the pictures, inserting them storing only a reference, and using Applescript to tell FMP to store a reference to the picture, all with the same result. There's no problem for smaller (<1 Mb) TIFF's; they show up in their entirety. This is my first FMP project, so I might be missing something obvious, but I'm pretty puzzled, so if someone could tell me what's going on here, and how I could fix it, I'd greatly appreciate it.

Hello, I have an inventory control DB that involves an INVOICE, INVENTORY, PO, RECEIVING, and PRICING?DESCRIPTION. The wholesale costs are in the INVENTORY DB, and I need them in the PRICING/DESCRIPTION DB. The WHOLESALE field in PRICING/DESCRIPTION is a lookup of INVENTORY. The lookup seems to work fine, but if a change is made to the WHOLESALE field in INVENTORY, the change does not occur in PRICING/DESCRIPTION. I usually need to copy and paste the PRODUCT ID field in itself for the change to be made. Am I doing something wrong that will not allow the lookup to instantly make the change? Thanks, Steven