Calculation Engine (Define Fields)

Hey there, I'm working with products with ingredients with certain amounts of nutritional values. I've currently got the following tables: NutVal - contains a record for each measured nutritional value Ingred - contains a record for each measured ingredient InNut - contains all Ingredients with a record for each nutritional value per ingredient InProd - contains a record for each ingredients in a product, indexed by product number Prod - contains a record for each product Each record in Prod contains a portal containing its nutritional breakdown. I need to do a specific calculation though, using the percent nutritional value per ingredient (contained…

Hi, all. I have this nagging problem with displaying information (text AND numbers) in a field. There's a "Product" table which contains a price field, defined as text, as per FM Help. It can contain an amount (2 decimal places) OR text (such as "N/C" or "Included", etc.). Then, there's an "Order Detail" table which contains a quantity field, defined as numeric, a unit price (defined as text) which is looked up from the price field in the "Product" table. It also contains an extension field which is the result of this calculation: Case ( Extension = "N/C" ; "N/C" ; Extension = "Incl." ; "Incl." ; Round ( Quantity * UnitPrice ; 2 ) ) I've tr…

What the calculation would look like to display only the first 20 words of a text container field? Thank you for your help Fab

In my file I have two tables (Projects) and (COTItems). These two tables are related by the following relationship. [color:red](COTItems) / [color:red](Projects) ItemID ----------------ServiceCodesID_Glob AND ProjectID -------------ProjectID I will like to use a Tooltip to display the higher value in field: UnitLabor for all related records base in my relationship. Example: I have a value of: 10 for record #1 and a value of 20 for record #2. Wend I create record #3 and place the mouse on top of the field: [color:red]UnitLabor the Tooltip should display 20 for A01 and 30 for B02 base on the higher value of all existent related records.…

Hi All, I have a container field which i wish to add a 'thumbnail' image of a PDF and to be able to then open up the PDF with Acrobat Reader. I have used a calculation value in the Container field to look to a text field i have. The text field contains the path of the PDF but the result i get in my container field is 'The File cannot be Found: Stock.pdf' The path im a using is: imagewin:C:/Documents and Settings/Administrator/Desktop/ERP/Stock/Stock.pdf So as far as i can see i am pointing directly to the PDF. The PDF resides on the same box as the PDF. Is the PDF too many folders deep?! Is there something i am doing wrong here? My PC is running XP P…

I know this has to have a simple solution... but I can't find it How do you make a field be only 2 Capital letters? For example, for the state field, we need it to be CA. And that is the default, but users can override it to another state. So I want to ensure that it will WA or OH not "Wa" or "Oh"... As always, thanks in advance.

I'm trying to set a field to a default value of text, and then in turn, allow the user to change it. I've tried using the Auto-Enter/Data capability, but it restricts you to 255 or fewer characters (my text is about 350 char long). I've also tried doing a Lookup using a global field, except there is only the option to copy the data if the field being copied isn't empty - rather than "copy only if field being copied into is blank". In other words, the way this option works is to copy it EVERY time, not just the first time, as a default normally would work. I'm sure this is an easy problem to solve (or should be, anyway); I just can't figure it out. Any…

I have about 60,000 records in a database that have a time field that i need reformatted. The problem I need to rectify is that even though the fields in question are already time fields they have been set to "leave data formatted as entered". What I need is for the format to be HH:MM. Can anyone suggest a simple way of inserting the : into the existing data fields via script or calculation. Im drawing a blank here. Any help would be greatly appreciated. Thanks, Ron

I have two tables employee and customer Linked on Emplyee ID. If I want to have a calculated field of all active customers how do I do this. I tried an if IF(CustomerStatus="active";Count(CustomerID);"0") But this gives me either all customers or a 0 but not the active customers. Any suggestions.

How can I script converting a number, like 104.39, to "one hundred four and 39/100 dollars" ? Thanks, -Dave

I am creating a data base which at this point has two tables, Customers and invoices. I want the invoice form to have a drop down list of all the customer's names to choose from. I made the customer name field in invoices a number with lookup to the customer table of its customer name. I related the tables by customer number. This did not work. So I defined a customer "list" which pointed to the customer name field in the customer table. Then I made the customer field on the invoice layout a drop down list pointing to the customer list. This also did not work. So how do I do this? I cannot find an example of what I would be a common feature in any of various referenc…

I know this probably has a really simple answer, but I can't seem to pin it down anywhere after seaching both here and in FMP help. Is there a way to duplicate a table so that you can have a separate table within the database that has identically named fields? Can you do it within a file as well as duplicating a table from another file?

Attached is an example of a database which shows selected images from an iPhoto Library. It uses references to the photos so that they don't need to be imported. Also included is a script which allows one to quickly specifiy the photos to include in FM database. Initially the user needs to input the Volume name and the File Path for the iPhoto library. In this way it can view photos on remote volumes (as long as the user logged on to the networked drive) User: admin Password: Images.fp7.zip

My Balance fields (calc fields in a related table) are not being updated after data entry. I tried commit record and refresh window scripts after entering data to get these fields to show the right balances but it didn't work. I have to close the file and reopen it to see the correct amounts. Any idea how to fix this? Thanks in advance.

I need a simple script to add a price field if a check box is valid. The trick is its in FM5 and I'm a novice. Someone tell me what fucntions i need to use (GetSummary, Count, Sum)? My need is deceptively simple. There is a Check Box, Price, and Total. Every record will have the same total value (no sub total or running total).

Hi I'm not sure whether this is the right forum as this is also a layout/printing question. I use a database to send out quote requests on parts and store the prices I receive back. Underneath each line (one part) on my fax layout I would like to have notes field that I can add extra information sometimes when necessary. Line 1 - Part #, Desc, Qty Req, Cost, Condition Line 2 - Part #, Desc, Qty Req, Cost, Condition Line 3 - Part #, Desc, Qty Req, Cost, Condition Notes on the line 3 part above - Line 4 - Part #, Desc, Qty Req, Cost, Condition Somtimes this field will be empty so I don't want it displayed on the fax at all. Is it possible to hide…

Is it possible to return the value of the field name of the field the calculation is in. eg: Field name: "MyField" Calculation,Text Result: Get(CurrentFieldName) Result: "MyField"

Thanks to all who have been helping me with the project I'm working on, I'm nearing the end of the work. I've got a weird issue though where one of my totals (which is basically a sum calc field of another sum calc field, both on different layouts), only updates when I either go into the originating field setup and open that up, or reopen the project. Thats a little hard to follow, so here's another try... I have a table PWGP with a field TotalProfit, which is a calculation for Sum(Royalties::Royalty) Royalties::Royalty is Sum(Mechanicals::Mechanical) When I define the database and go to the Mechanicals table and double click on the Mechanical field th…

Hi, all. I'd like to be able to display some global variables on a layout. I couldn't find any reference to doing that in the documentation. I know I can watch what happens to my variables with the Data Viewer, but I would have some need for a more permanent display of that information... Move them into some table?... Thanks!

Hi folks, after some searching I was unable to find what I was looking for. Since this is my first post, let me know what I can do to make myself sound more coherent! : We have two tables, one table called "A" is the main table we work in. Each record represents one 'adjustment'. Table B contains a layout that lists a 6 'adjustments' on one record. Since basically these are the same bits of data in the two tables, we want to automatically have the 6 adjustments from the ONE record in the B table to 1 adjustment per record in the A table. From one record with many parts to many records with one part... basically. I know we can do this with basic I…

My database uses container fields to store a "file reference only" version of a file. How do I capture that filename, visible in the container field, in a separate text field?

each time I open a dbase a global value has changed to blank - any ideas appreciated.

Does anyone know how I can have a calculation field that counts the number of unique values in a field? I have a line item portal on my purchase order layout. Each line item is tied to a workorder via a workorder ID. I would like a calculation to return a value representing the number of unique workorders included in one purchase order. I want to use a value of "1" (number of unique workorders) so that I know the entire purchase order contains products for only one workorder. Likewise a value of "2 or more" (number of unique workorders) would tell me that the purchase order conatins products for many workorders.

Two questions i need help on 1)I think i need a case statement but im not sure. I have a checkbox called Names a field called current name and a field called no of players. No of players uses the count feature to count the number of checked items. Currently the field current name hold the first checkbox item GetValue(Name;1). I need a script that will enter the checked name equal to the no of players field. eg If No of Players =3 the field current name will show the item that was checked 3rd) 2) I have a field called records needed, is there anyway that i can sort records based on the number in this field; in the following format. …

I attached a file that calculates timecode summaries, the only issue I have the addition between frames1+frames2 are wrong and also I have a auto enter calculation for TC in and TC out but when I want to change one digit in the already entered field it displayes it wrong! Like "00::0:0::01" Could someone please help me figure this one out? Thanks a bunch! TMAS : TC.fp7.zip

I can't think of the answer at the moment. If I create a simple Yes/No field, how can I set and store the actual date when the field was set to "Yes" and maintain the stored date?

i have a checkbox called participants it displays various names, is it possible to set a checked item as a variable eg $ check 1= first checked data item $ check 2= second checked data item

Hi, Ive already searched through the change text colour threads but nothing seems to work in this situation : My suppliers table includes the currency (AUD, USD, etc.) they normally quote in. Sometimes a supplier will quote in a different currency so I can change the this for each transaction as required. I need the transactions table currency field text colour to change to red if it doesn't match the supplier's regular currency. This field is a lookup of from the supplier's currency so I can't use the option of a calculated value option in the field properties. Does anyone have any ideas on how to solve this? Thanks Pedro :-)

Table1=t1 field a and b Table2=t2 field c and d. I was trying something like: Case(C=a and d=b;TextColor(text;RGB(255;0;0))). If any of the values in a AND B were displayed in the t2 fields C and D then the font colour of those displayed only would show RED in table 1. But it does not work. Any idea's what i am not doing ? Corey....

"Tendency" is a calculation field that returns one of three text symbols; let's say: A, B, or C. I already have this field correctly set up. However, I'm having difficulty constructing another calculation field that will give the percentage of instances when "Tendency" equals either A or C in the entire layout. so (sum of records when Tendency = A) + (sum of records when Tendency = C) / Get(TotalRecordCount) or instead of Get(TotalRecordCount) I could use the last value of the number field "Instance" I suspect I need some combination of a Case or If function with the Sum function; but I'm new to FMP and tho I've spent hours in the help files (obviousl…

Hi There I have a multi user database that has been working fine for years, however I have just added my new MacBook and seem to have a date issue only of this machine - The problem seems to coincide with arrival of my new Macbook or in the installation of Parallels Workstation software. I use a global field which is refreshed via a script with the system current date on login, however on my Mac only, the date is entered in a US format instead of UK. All the regional settings on my Macbook are correct & I have tried looking at the use system format setting. Other standard "auto enter" date fields seem to work fine, just the global script updated …

I have a checkbox and boxes 1-32 i need to seperate the checked items so that one data item goes into one box.I have this done but i need to change my script so that the data items goes into a random box.

I'm new to relational... so please forgive. This is probably an easy one. I have a table, TEST REQUIREMENTS, and each record has a field called "Type" (say... types A, B & C) I have a table, called TESTS. Tests can only be of type "Type" A, B & C. So, when entering a new record into TESTS, I select Test Type... but then when it comes to what requirements are covered by the test, I want to limit the user to making choices from the TEST REQUIREMENTS table of only the matching type (e.g. A, B or C) I understand how to make the relation between Tests and Test Requirements. I do no understand how to limit the relation to a matching type. Any ideas…

Is it possible is to disable the Revert Record Option when validating a field and the validation fails? I want only to allow the user to go back and correct the field and not Revert. The reason I would like to do this is I had to setup to only save record by a record exit commit. The problem is when committing the record if one field is not valid and the user chooses to revert record option it will revert all changes in the record since last save. I tried doing Commit Records with the Skip Data Entry Validation but it still runs the validation (not sure why) if that would work I thought maybe I could do the script with two commit records: once skipping validation an…

Hi, I have never really dealt with container fields before and I'm having some problems understanding how to insert pictures into a repeating container field. I want to insert 3 pictures, a red box, a yellow box, and a green box. I have a To Do List, and want to display a certain picture next to (or within) the portal depending on when the item needs to be completed. I created a new repeating field in my To Do List table, with 3 repetitions. Do I need to put all three pictures in each record, or do i need to make a seperate table that just has one repeating container field? My main problem, however, is that I can't figure out how to put 3 pictures…