Calculation Engine (Define Fields)

Maybe basic, but to hard for me. I have a database for events with start day and end day. I need to add event status, which update automatically: Today is more than 100 days away: status: coming Today is less than 100 days away: status: preparing Today is past start day: status: running Today is past end day: status: closed I like the idea to use 4 radio buttons which jump to the right status automatically. (High visibility and easy for searching) Can anybody help?

When I enter a date in my data base I enter it as M/D/Y and it displays as M/D/Y. I sent and updated the database to my two co-workers (also Macs) and they are now forced to enter the date as D/M/Y but it displays as M/D/Y as it was set up. How did this happen and how do I correct this?

Hello all, Need a little help. I have a database for creating quotes and then processing them to jobs. Thought I had things working great then noticed a big problem. Three Tables Quote QuoteItem Job Each quote can have many items. If a quote becomes a job, all the items quoted may not be included. I've set up the database around the QuoteItem Table. When a quote becomes a job, I find the right quote through its ID, select the items that are included on the Purchase Order(which is listed through a portal) via a Yes/No field. When Yes is selected, a lookup is performed and the JobNumber is inserted on the QuoteItem Record. Now through a script, I …

First of all, my Filemaker and my database skills are very weak, so I 'm hoping you'll be patient with me. I have a mailing list that was created in Excel. I have divided it up into three tables and imported it into Filemaker. Instead of bringing each table into a single Filemaker database, I find I have three separate Filemaker database files that are linked by the relationships I have set up. Is there a way to get all three tables to exist inside the same Filemaker database file?

Hello I have to design a basic database to keep track of butcher shop transactions as a school assignment. I have 2 files linked by a relationship. One has all of the products and their pices in it and the other keeps track of all the transactions. I use an id number to enter the products through a reapeating field which looks up the names and prices of that product. My question is how can i get it to display a picture of that item in another field when i enter that id number?? I will attatch a screenshot of it in its early stages which might help you understand better. Any help would be greatly appreciated. Thanks in Advance Toby :

Our tax rate (Canadian G.S.T.) changed on July 1st. I had a calculation for the 7% tax that is now reduced to 6%. When I change the calculation to 6% the previous invoices change as well. We sell events and so the calculation change for the GST field needs to be determined by whether the event took place before or after July 1st, 2006. There is a field for the event date in the same record. Can anyone suggest the GST field calculation to do this? Thanks in advance.

I’m trying to get my database of 100,000 records to export 3000 unique records at a time as fast as possible. I need the database to do this 8 times in a row. What I have been doing up to this point is sorting the records by item name ascending export , and then descending export weight ascending export , and then descending export color ascending export , and then descending export size ascending export , and then descending export This gives me eight mostly different sets of records but sometimes the size and weight are both low I get duplicates. I cant think of another way of doing this? Please help!!

Is there a validation calculation that veryfies the minimum input of for example 6 letters if less that shows a message? Thanks TMAS

I have a FMP6 data base with a date field. I want to define a new field (called Term) where the data is a calculation based on the date field: ie if the data is within a range of dates eg between 30/1/06 and 30/4/06 (Aust format) then a number eg 1 goes in the Term field. I can't work out how to write the If statement... help please

I am working on a database where I need to do sorts by age. For some of the records, I have the child's date of birth and for some I only have their current age. (Obviously, age based on date of birth is more useful since it is dynamic.) Right now I have three fields: 1. Age Field (User can enter in the age) 2. Date of Birth 3. Age Calculation (Auto enters age based on birthday. If Birthday field is empty then it autoenters age based on "Age Field." If both age field and date of birth contain a value, the date of birth takes priority. [i've attached a simple example] In sum, I am wondering if its possible to have a single age field that wil…

Are there any know issues using certain text characters or numbers when naming (Tables, Fields, Table Occurrences, Scripts)? Being new to FM7 and FM8, I have been having a hard time coming up with appropriate naming conventions for Tables, Fields, Layouts, etc. Back in FM5 I got in the habit of never using space in field names. I can't even remember the reason anymore, I think it had something to do with working with calculations...so I could easily selecting field names within the calculation via a double mouse click. As my current project grows it has become increasingly hard to make sense of the spaghetti maze in my table occurrence window. In an effort to he…

Hi, I need some advice on table design. Specifically, I have an table which contains many sub-classes. Do I use ONE table with many, many fields OR use MANY tables containing ONE class of asset? Thanks in advance! :)

I have a calculation based on three field. I have a state field that lists the states. I have a Type record field that list different type records. I have a numeric field that show the amount of money. Sample Case ( State = "Texas" and Type = "RI" and Tax > 0; "01-03811";""; State = "New Mexico" and Type = "RI" and Tax > 0; "02-03812";""; State = "Oklahoma" and Type = "RI" and Tax > 0; "03-03813";""; State = "Louisiana" and Type = "RI" and Tax > 0; "04-03814";""; State = "Arkansas" and Type = "RI" and Tax > 0; "05-03815";""; State = "Alaska" and Type = "RI" and Tax > 0; "09-03819";""; State = "Kansas" and Type = "RI" and Tax &…

small annoying problem this time guys, hope you can help... ive got a list of contacts where some entries are family and the others are just friends. ive got all the details, such as name, adress, and phone for each contact. what i want now is a checkboxfield below the contact info called "family?" were you check "yes" for familymembers, or "no" (or just leave blank) for others. the thing is that i want this connected to a table which creats a new entry for every family member and sticks them inte the table (this all because it going to be easier to send mail to them all, yes im just that shallow and unpersonal ) now, i know how to script a button to do all th…

I would like to have the text color of a field change to red only if the data in the field has been modified since it's initial entry.

Is it possible to have one picture calculation from two different fields at the same time? GetRepetition(Color_Images;Case(Color_Choose = "None"; 1; Color_Choose = "Red"; 2; Color_Choose = "Orange"; 3; Color_Choose = "Yellow"; 4; Color_Choose = "Green"; 5;Color_Choose = "Blue"; 6;Color_Choose = "Purple"; 7;Color_Choose = "Gray"; 8;)) First field = Color_Choose (Colors see above) Second Field = Category = (Web Site, Email...) How should the calculation look like in this case? Thanks TMAS

Hello, Is there a way I can assign multiple values to different fields with one button? I'm guessing with a script of some sort... I've got a product sheet layout that needs to be revised every now and then. I'd like to have just one button (I currently need three, but feel I shouldn't) that, when clicked increments the Revision Number by one, changes the Revision Date to the current date and prompts the user for a signature. I have separate buttons that perform the first two functions, but not the signature. For the first button, I use a Set Field where Revision Number = Revision Number + 1 For the second button, I use Insert Current Date into Revision Da…

Hi all friends there are some unespected things coming from this function. (I found this when try to set all the rep to the control value) For example I have 2 fields: control - number field repeating - calculation repeating field with 10 rep and calc: Case( IsEmpty(control);Extend(control); repeating ) Now i think that if control is empty, repeating is empty too...but if I enter something into "control", "repeating" Extends... : If you change the calc to UNSTORED, there are some other unespected things... (the first rep comes with "?" and the remaining reps...) And so the same, if you change the calculation to number with option of a…

Using FM6, I've got a calculation field that is attempting to count the number of related records that match a condition. The code I am using in the calculation field is: Case(Client License Changes::date_submitted ≥ Date(7, 1, 2006), Count(Client License Changes::license_change_id)) I am trying to get the number of related records in the Client License Changes file that were submitted after July 1st 2006. Unfortunately the result continues to be equal to blank. This is one of those things that I could swear I had working a week ago during the initial development and testing, but it fails to work now. Can anyone shed some light on what I am doing wron…

Hi there, I have a database for a school guidance department where guidance counsellors put students on a wait list for courses that are full. I would like very much to have a calculation that takes the original date the record was entered into the system and then looks at today's date and calculates how long the student has been waiting for a course (ex. 1 day, 7 days 14 days,...). I have a global container field with graphics to indicate each level. I know the following calculation is wrong but hopefully it can help out... Case( Date of Entry + Today>= 1, GetRepetition( Wait Levels , 1 ), Date of Entry + Today>= 7, GetRepetition( Wait Levels , …

I've got a simple calculation totalling values from a portal, but it won't update when one of those values is changed from a different layout. I tried making it a summary, but its totalling values from a different table, so I can't do it. See attached. For example, using "Form View - People", try changing Alice's pay rate to 95. Then, in "Form View - Projects", the Total Project Pay for Alpha (at the bottom) will not have updated. Is there any way to fix this? Any help is greatly appreciated. Job_Accounting.zip

Hi, I've got a couple of instances where I want to be able to copy the field contents of a field that is not displayed on any layout but it never seems to work. Is this is a limitation of FileMaker or am I doing it wrong? Thanks Peter -)

Because I have to leave the country Saturday for an emergency operation on my father, it'd be great if someone familiar w/ FMPro 4.3 can help me Thu or Fri, June 29 & 30th. I'm a fairly novice FM user, entering member contributions to our small non-profit into FM databases. FM converted the year in our datefields back one hundred years, e.g. 2004 became 1904. This happened when I invented a procedure to move several hundred members who haven't contributed for a while to a FM database of such members (with identical fields). To do so I first found the date range I wanted in that field and exported those records in FM format, then imported them into a pre-e…

Hi, I have a FM5.5 question. I have a field defined as a numeric with an auto-enter serial number to keep track of transactions. I also allow the user to override the entered number in case a transaction requires more that one record. In which case they add an alphabetic character to the end of the number ie: 12345B. They prefer to keep the number and alphabetic suffix in the same field rather than in seperate fields. What is the best way to trap for these alphavetic suffixes entered in the numeric field?

Hello, this will be easy to answer for all the experienced users out there, but for me, I've been struggling! Here's what I'm trying to do: I have a field called "Surgery Time", which is the time it took to perform a surgery. In a separate field, called "Average Time", I want to show the total average time for all the surgeries. Any help would be appreciated! Thanks! Alisun

I've been using FM for few years but new to forums. So here we go! I've got a container field in which i want reference to an image in the folder "Previews" (Same level as my FM solution). I can get this to work with this calculation "image:Previews/" & JobNumberMonth & ".pdf" In FM the field "JobNumberMonth" contains 4 digits "0206" relating to job number 6 of the 2nd month. In my "Previews" folder i have screenshots that are named "0206.pdf" etc that link to my FM solution. What i want to do is be able to have files of different types in the "Previews" folder (.jpg .png etc) I have tried taking the file extension off the reference file a…

Hi, I am trying to figure out how do we create the record base on given a value. Example Given Record : SN00012 New Records Going To Be Add On : 25 So when we click on a button, it will create SN00013 SN00014 SN00015 and so on until SN00037 Each record is a portal item by itself. How do we do it ? Thanks in advance. :)

Does anyone know how to create a field (or better yet a calculation) where the last two digits of the current year (e.g. 06) and a set serial number are created. So it would be 06-001 to 06-999 through the year then the next year would go from 07-001 to 07-999 and so forth. I'd be willing to modify the calculation to change the serial number to 001 for the beginning of each year if there is no solution for that. Background: Right now another section is manually creating these record numbers for each projects they do but now would like the record number automated. In addition, because the year has already started months ago, they are already well into t…

I'm running FMP server 8 running FMP 8 to 6 site within my office. Set up a global repeating container field [8] to hold icons. I also have a calculation that checks a text field to see if the text field is empty. If ( not IsEmpty (Prescription Text Box 1); GetRepetition ( Container_Global; 7); "") The problem is disappearing icons in the repeating global container field 5-8 when the computer is turned on/off. The icons that are stored in repeating fields 1-4 never disappear. I might add that one of the 6 served site has FMP Advance running and I'm not sure if this plays in the problem. Any insight into why 1/2 the icon disappear from a repeating global contain…

Here's my problem. I import pictures for my database that is going to be about 5,000 pictures in size. That's fine I have the machine, bandwith, and storage capacity to do that. The problem I have is I need the thumbnails to be smaller files then they're being imported as. What its doing right now is importing the full file for each entry, but the thumbnail is just a shrunken version of the orginal picture with the same file size. I need to make that picture considerably smaller (it starts at about 5 megs). Does anybody know how to do that? I assume you have to alter the container on the "list View," but I've tried altering it but haven't had much success and t…

This could solve one of my many fmp problems, but if there is a way to dump the filepath and the filename of an image you add to a container to other fields, that would be very helpful. IE: r-click on a container, select add image. When you do that, you get a file requester, you find the image, and FMP imports it to the container. At the same time I would love to dump the path and filename into 2 other fields, or if I have to path+filename in 1 field. Anyone have ideas? :)

hi all, im newbie in filemaker, i want ask you all i want make some video overview in filemaker using container field , and i was done on it . the problem is when i move the file and video file (*.mov) to the CD for use it in another computer, i got some error "the file cannot be found:myfile.mov" . can you help me please

Ok so I've figured out the below calc to identify the number of days between date1 and date2 If(not IsEmpty(Date08e); Int((Date08eW - Date08sW) /7)*5 + Mod(Date08eW - Date08sW; 7) - Case(DayOfWeek(Date08eW) < DayOfWeek(Date08sW); 2) - Count(ADM_Holidays08::Date) ;"") What I need to do now is identify "bring-ups" (due dates) based on date1 - days so if date1 = 30/06 LESS 28 days = 22/05 (23/05 less 1 day for public holiday on 12/06) I've been trying to break down the calc above, and have read up on Mod but don't quite get it I know I have to identify how many days aren't valid (w/ends or holidays) and minus those off the original date …

This is very simple, but a few minutes of research haven't yielded the answer. I need to calculate a "follow-up" date. The result should calculate based on two existing fields: a) The initial entry date, entered by the user. The follow-up threshold, entered as the number of weeks. The calculation should then be a+b - I know I need to use get as date and get as number functions, but I'm not quite sure how... Any help most appreciated.

A database which contains pictures that are referenced will not display the references in a web browser. Is there any way this can be solved? Thanks, allie

I have a number field "Copy Cost" that gets filled in based on a selection from a pull down menu. Question: The user now wants the "Copy Cost" field to have a 0 amount if a particular selection is made from this pull down menu no matter how many words are in the "Ad Copy" and "Name of Individuals in Ad" fields. How would I incorporate this into my calculation? //get the total word count and set it to variable "C" Let( C = WordCount(Ad Copy & " " & Name of Individuals in Ad); //Check selection in pull down menu and apply relevant value to field "Copy Cost" Case(PatternCount ( Photo Size ; "Text Only" ); 50; //Check selection…

Hi there, I have a bucnh picture of my product, i want to make a database for my product with picture. Is that possible to make a script that allow me insert my product picture in product field (container) after input product code in code field ? so i can sort my work insteed input code and insert picture as manual work. Thank for your kind attention and sorry for my english...

I am trying to create a calculation that gets all field values of a related record and combines them into a single field with a carriage return after each one. I have tried to use the "GetNthRecord" function to achieve the desired result but I have to string a bunch of them together to get it to work. GetNthRecord ( ASSIGNED_MENUS::MenuID ; 1 ) & "¶" & GetNthRecord ( ASSIGNED_MENUS::MenuID ; 2 ) & "¶" & GetNthRecord ( ASSIGNED_MENUS::MenuID ; 3 ) I am sure that this is not how I should be doing it. Is there a single function that can do this?

I thought I had figured out how to generate a random birthdate, given an age using a random generation for day and one for month and then combining with year. My purpose is for instructional use, to keep the patients the same age, no matter the year being used. Now, when I use the field in a layout in more than one place, the date of births are different. Is there anyway to get it to 'set' and not change? Thanks to all who peruse and ponder my problem! I appreciate any help.

hopefully this is posted in the right forum, anyhow here goes: ive got two diffrent problems: 1. ive got a checkbox list which is linked to a portal. when i check the boxes the result shows up in the portal all good and well, but everything goes into the same field! i want the different checks to end up in diffrent fields, how do i do that? 2. if i want to add checkboxes to my list by manually entering values into a field in a different table, how do i do that? preferably i would like to have a button next to the new value that said "add", which in turn added the new value, is this possible?

Hi folks! Problem: I need to import JPGs into a container field using a calculation to set the filename to import. Is this possible. Hope someone out there can help, this one has got me scratching my head a bit. I have created a database to import emails containing values posted from a web form processed by a php form processor. One of the values in the email contains the filename (i.e richard.jpg) of a file that also gets uploaded/attatched to the email from the web form. I have setup an applescript used by Mail to save down the email attachment to a specified folder. Is there anyway to script in filemaker the ability to import the attached jpg (bearin…

Is there a database size consideration when deciding whether to use Text or Numeric auto-enter serial numbers? In designing a database that, potentially, could contain hundreds of thousands of records in each of several tables it occurs to me that I could be wasting a lot of space using Text IDs for those certain tables. "Old school thinking" leads me toward numeric (e.g., 1 to 999,999) instead of text (e.g., A000001 to A999999), even though I prefer to use Text if the ID is ever displayed to the user. But is that reasoning valid or does FMP store Text IDs in a compressed form that makes space requirements a non-issue? (For readablity, I even have a few IDs like T…

Hi, I am having a little problem with my calculation of expiry date. I wanted to do base on date of invoice and then calculate the expiry date of the invoice base on given number of months. How do I do it ? Thanks in advance Fei EXPIRE.zip

Hi, all. Still in the design phase of my project. In our neck of the woods, we prefer to format phone numbers, for human consumption, in this way: (999) 999-9999. Postal codes: A9A 9A9. QUESTION: Is there a consensus as to what the "best practice" is with regards to storing these data in the database? Should they be stored WITH the formatting (validated at input time) or should they be stored WITHOUT any formatting (and edited every time they have to be displayed/printed)? Thanks for your input!

Hi, I am fairly new to FM8A. I wanted to know if it is possible to have a button which will open a new window and display the photo stored in a container. I want the user to be able to look at the photo on a larger scale. Thanks, Jim

I'm not sure if I'm missing something here but I'm having a heck of a time with a particular global field. Everytime I open the database it is empty. I enter a number and it stays until I close. When I open it I find it empty again. Never had this happen before. Any thoughts? bryan

If I apply TextColor to a calculation that returns a numeric value in a field that is formatted as currency in the layout, the color doesn't appear. If I change the calculation result to text, the color appears but the currency formatting doesn't. result= // maximum_value is the numeric result of a calculation // actual_value is numeric field entered by user Let ( DELTA = maximum_value - actual_value; Case( DELTA < 0; DELTA; RGB(255;0;0)); DELTA ) ) How do you set the color of money? Duh! After I posted this I realized that the number format provides an option for coloring negative. How do you retract a post?

Hi, I have a movie in a container field, but I want to be able to click off of that field, select another field to enter text, and then reselect the movie..... but everytime I select outside the container, it goes back to the beginning of the video... is there anyway to stop it from always going back to the beginning? Vandy

I have a simple calculation field that is only calculating on some of my records, aprox. 3 out of 20 records. The calculation is: "Annual cost" / 2 * "billing weeks" All fields are in the same table.

I am using FileMaker Pro 7. I have a database now for books. This dbf is used for catalog listings. I am needing to limit the # of characters in the field I have created for the description to 128 characters. Is there a way to do this? Thank you Allene

Hi All, Is it possible to display repetitions of a field dependant upon a number field which would be displaying how many repetitions should be shown and then using a calculation? I have a maximum of 12 items per job...although jobs have differnt number of items. Each item reprasents a repetition...therfore if i have a job with 3 items i only want 3 reps if job has 12 items i want to view 12 reps. Thanks in advance

Hi, all. Still designing an order entry system (Customers, Products, OrderHeaders, OrderDetails tables). I was initially looking at using the pk_Order_ID (automatic, serial number) as the OrderNumber (the "human-readable" number). But that doesn't make much sense, does it? The ID (and therefore the order number) would get assigned fairly early in the process, as soon as the Header record gets committed and before all the details have been created. But then, what happens if I decide, halfway through the process, to simply abandon this order and not create it? (In our environment, this does happen regularly; we do this for training purposes as well). …

I need some help. I'm still in 7 but I think you all can still offer some advice. I'm creating a solution for the coaches of my daughter's swim team. I need a time field that will record the swimmers times in minutes:seconds:hundreths of a second. I need to be able to sort by this field from slowest to fastest etc..., So far, I can't figure out how to do this. Any suggestions would be greatly appreciated. Papa.

Ok, I am migrating an biding database from 5.5. In the old version I have a calc field the references discription fields in the same table and gives me a paragraph that is printed with the dollar amount for that"Area". In the new version, I have made a join table that holds all the line items making it much more flexable, however, how do you summerize text fields from a related table and show them in a paragraph?

Ok. I have a database I am designing for a police officer/department (possibly). I need to have a button that will pop up a calendar showing the names of the arrested/cited on all calendar days. So it needs to have 7 days x 31 days calendar with the names in the day boxes. The fields I can use are "LastName", "FirstName". My problem is making the calendar popup and the names show in the day boxes. Any advice?? Thanks

Ok so I have a container field that basically contains a colour based on a calculation. For aesthetical reasons I would like this container to resize (sliding/print) to the same size as the other text fields in the same layout. Hence giving a preview of a report with all the boxes the same height.

Hi, I am trying to create service call database as following: Table1:Service_Call Field_1:Service_Call_ID Field_2:Service_Call_Assigned_Enginners Field_3:Service_Call_Status Table2:Assign_Task Field_1:Service_Call_ID Field_2:Engineer_Name Field_3:Task_Date Table3:Service_Visit Field_1:Service_Call_ID Field_2:Service_Visit_Status Field_3:Service_Visit_Date The Problem is:(in screenshot2) How can I define the caculation to make Service_Call_Assigned_Enginners="Ben+Ken+Jason"? And Service_Call_Status="In_Progress"? (if there's a "Finished" status in "Children", the status of Service_Call will be "Finished", otherwise it's…

Hi, not sure if this is in the correct forum, but please help: We have been using Filemaker 6 to learn FM and are upgrading to FM8 this week. My problem is that I want to assign client specific abbreviations plus job numbers i.e. RFS001, RFS002 etc. and ROT001, ROT002 etc. Am I able to do this with FM6, or is there a much easier way to do this with FM8? I am aware how to set up a field to identify consequtive job numbers but so far I am unable to introduce the client unique job numbering. Any help very gratefully received and I'm sure I'll be asking more!!! Many thanks.

I have these fields: Property Name, City, Phone. I need a field to calculate the first 5 letters of Property Name, City and the last 4 phone. This will give me a unique id for this contact. Thanks

I have a situation were I have 7 fields that the user chooses to show what days a newspaper runs (Mon through Sun). Is there a way to have a field that summarizes those 7 fields? For example: a newspaper runs Mon, Tues, Wed. I would like to see that say Mon-Wed or if it was only 2 days list those days. Any thoughts or help is appreciated. Thanks

Hi, I try to develop a custom function in order to distribute a number based on a sequence, with separators. An example - the number is 17 - the sequence is 5 - the separator is "X" --> the distribution should be 5 X 5 X 5 X 2 The total of the numbers is 17 and each sequence is separated by "X". The distribution ends by the Mod(number ; sequence) This is the recursive function I developed, without success : Distribution(numbr; rythm) Let([ M = numbr ; R = rythm ; RI = Mod(M ; R) ] ; Case( RI = M ; RI ; R & " X " & Distribution((M - R) ; R)) ) Doesn't work... Find attached my test file : you will see that…

I have two seperate container fields located in the same database. Everytime I insert a small jpg logo file into each container field, the images are gone when I go back in the database. I leave unchecked the option "Store only a reference to the file" being I want the image stored IN the database. Each jpg is only 4k. What gives? There must be something I'm missing. Each container field has Use Global Storage checked off. Please help. It's not do or die, but man it's frustrating.

Hi, Does someone know how the FileMaker Random function works internally and if there has been a change in its behavior between FM versions 7v1-8v3? I'm having the following problem: last year we deployed 200 FMP7 runtime solutions in which random unique IDs (random combination of 10 characters/numbers) are generated for transactions. The transactions are gathered back and imported in one central database. We never had problems with these IDs until we deployed a new version of our solution created with FMP8v2 last month. Since then we received already 5 newly generated IDs from 3 different runtimes that have a match. Consider A, B and C, then B and C now produce…

Hello FM lovers, I use a portal to display values in a related field. The problem is the field could have multiple repeating values of each of the values I need to display in the portal Sample: MyField--------------------------Portal should display A123---------------------------------------A123 A123---------------------------------------B753 A123---------------------------------------C147 B753 B753 C147 C147 Can I use a calculation to accomplish this? Thanks in advance.

I am trying to take a date, that could be any day of a particular month, and use a calculation that will determine the first day of the month that the date falls in. For example if the user enters 6/16/06 the calculatin will result in 6/1/06 (the first day of the month). Please help if you can...

Hello, I have a calculation that shows Active Jobs in a portal... here is the calculation. Case (jb_date_end ≥ ( Get ( CurrentDate ) );"Active") It works, but we have found out that some employees' jobs don't necessarily have a job end date (jb_date_end)... so how do I add if jb_date_end is null - job is also active? Thanks in advance

My head isn't working well today. I've tryingto modify a current calcualtion to show me the time remaining of a 3 year EOL (end of life) on a computer. right now i have it just displaying a word Retire or Serviceable depending on what side of 3 years it falls on. here is the current calc. Case ( Get ( CurrentDate ) ≥ Date ( Month (Date Purchased); Day (Date Purchased); Year (Date Purchased) + 3) ; TextColor ( "Retire" ; RGB ( 55 ; 0 ; 0 ) ) ; Get ( CurrentDate ) ≤ Date ( Month (Date Purchased); Day (Date Purchased); Year (Date Purchased) + 3) ; TextColor ( "Serviceable" ; RGB ( 0 ; 153 ; 0 ) ) ; " " ) i want to replace the "Servi…

Hello, I am new to the FM world. I have build a DB that is working perfect so far. There is only one thing I would like to see it do. Is there any way to get a container field to insert a picture based of a serial number I have a field set up to calculate. Maybe with a button using a script to make it happen. Thanks, Donnie

I am a relative FileMaker newbie, and so far I have mostly worked with web interfaces using CDML. I have a need now to provide some slightly fancy behavior in the actual FileMaker interface. What I'm looking for is something like a JavaScript rollover. Basically, I want an image with a number of 'hotspots' which, when rolled over or perhaps clicked, expand to to provide some informational text and perhaps another image. I can set up a button which pops up a confirm dialog, or I can set a field as a dropdown list which pulls text from a value list, but those are both kludgy and unattractive. Does anyone have any ideas for me? TIA!

I have a "LineItems" database with the following fields: LineItemID Qty LastModFlag (global number) Whenever the "Qty" is modified I would like the "LastModFlag" to lookup the record's "LineItemID". Is there a way to trigger a lookup that would do this?

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…