Calculation Engine (Define Fields)

I would like to have a validation occur in a date field. I would like the validation to be a date range of 60 days (30 days prior to current date and 30 days after the current date). The date entered needs to fall within that 60 day period. How would I enter this validation information? Thanks

How do you generate even/odd random numbers? ex) Generate "even" random number from (2-10) resulting in random value from (2,4,6,8,or 10) Has anyone done this before? Thanks

i need help on insert a file into a container as a Reference and store the phyical file to the remote folder. i am new to the FM and trying and getting any idea . Pl help me asap. Thanks, Satyam.

Sorry for the long title but there was no other way to explain it. I have a field called Findaddress. This file is formed by concatenating two other fields. StreetNumber and StreetName. The result via script will then be output to a file called FindAddress.txt. Now here is the issue im having, I need the result to be for example: 123,Main Street. This should be followed by a carriage return

I have recently started using functions. I was wondering if someone can point me in the right direction for a calculation I would like to use. I am wanting to take a data in a field that is separated by commas and added dashes to collapse the data. This is what the data currently looks like: 210,211,213,214,376,377,400,501,502,503 I would like the calculation to produce: 210-214,376-377,400,501-503 Since sometimes the data separated by commas can be 50plus numbers in consecutive order, than a jump in the data, I am looking for a way to condense the data. Since I have been having a lot of success figuring the functions, I think I have drawn a comple…

I wasnt able to find this answer so here goes. I want to add a radio button to my inventory table for each item to say defective "yes" or "no" I am able to create it in the field but want to be able to set conditions later that if yes is selected then it will bar it from be available in a related drop down as active material. These items are scanned in via barcode into the inventory and would also like them to be set to no automatically. What is the best way to do this so I am able to use in calc's?

i am using the audit tracking for the saved records. I am not getting the previous value for a field. can any one help me on this?.

Hello all, I am making a solution in FMP10. It is an invoicing solution. I have a portal in my "Invoice" Table, that has a relationship with "Inventory" via a join table. The portal works great as far as when the employee types the part number in the invoice, the rest of the information is automatically added (price, short desc., etc). We are in the aftermarket automotive industry and are looking to make it so when the part number is entered (first part only (of the portal)) it will automatically insert the year make and model that part fits in a seperate feild. So pretty much in more of a layman's terms.... I have a portal and based off the first value entered (the fi…

Short story: I have a field that adds up the time field on a layout. It displays the results in hours and minutes, which looks ridiculous as the hours runs in to the 10,000s. I need a function that turns these hours and minutes into years, months, days, hours and minutes. For example, 12,009:34 becomes say 0 Years 8 Months, 12 Days, 14 Hours and 34 Minutes. (I understand the function will give a text result which is ok, and I am NOT looking for a compare time function - I've found too many of these). I'm guessing the function will be a mess of Case clauses but it's too complex for me to figure out. Heeeellllllp! (PS Please don't reply telling me the examp…

Hopefully I'm missing something easy here. I want to get and use the current "FieldName" in the Define Fields as a Calculation. I want the calculation to run some logic depending on what the field name is. If I change the field name, in Define Fields, then the calculation may run some different logic. Does that make sense. I'm aware of Get ( ActiveFieldName ) but the field has to be on a layout with a cursor in it. Also the FieldNames(...) function only gets all the fieldnames and not just the one that is crunching the current calculation. Hopefully it is simple like an "=" sign or something and the calculation will return the "FieldName"... I can probably…

I have a field I want to be a flag for a expiring date. CurrentDate < Datefield -30;"expiring"; CurrentDate > Datefield + 1 ;"expired";"" It will just show the text I want to put in it. I need to tweek this a little Thanks in advance.

How do I calculate mode function in FP10? any built in function? Thanks

I'd like to us a number in a thank you letter but can't get the calculation to show a comma. So here is the calc "Thank you for your donation of$" & getastext(trans_amount)& " your donation will. . .etc." Is there a function to add a comma to the number? Thank you in advance.

Hello, I have an issue, which I can't explain so I attach an example file. So my question is: Does anybody have an idea how the field "shipper 2" with drop-down list could be transformed to "shipper 1" with Edit Box having the some functionallity? or Why I can't check "auto-complete using value list" box in the case of "shipper 2" example.zip

Hello all, Any quick thoughts on how one would go about implementing a variable commission rate calc. i.e. each product sold has different commission rates. I'm envisioning a solution where once a total value is entered into a field, another window would pop-up where you would choose the appropriate commission value and then the calculation would execute with the final result placed in another field. Creating a new display is not an issue; it's just integrating the calc with the display is the stumbling block. As with our questions I've posed, there always seems to be more than 1 way to accomplish things in FM...I'm open to other suggestions/ideas. TIA J…

Hi, please forgive me if this has been explained already. I have a layout setup to add data, another is the printable version of this. I have the script setup to pull the data into the print friendly layout and display for printing purposes... but i need to add a field which will tell me if this record has already been printed or not. So, once i hit print, my script triggers, but it should then change the radio button i have setup to "Yes" from the "No" that it would be before printing. i have attached a PDF of the script as it stands... could somebody point me in the direction to add the required function to this script. The new field i added is c…

I have a file that I am trying to import into filemaker. The date is set up as follows: May 1, 2009 How do I convert those dates to something that filemaker recognizes as a date. I want to be able to sort and find on all these dates. Right now I am importing them into a field that is defined as a date but it displays a question mark. I feel like this is one of those things that should be so simple, but I cant find any solutions. Do I have to write a custom function for this?

This is most likely easy but.... I have three dates fields for our school's immunization records, DPT, DT & Tdap. I need a field that evaluates those three fields and is filled with the latest date. Currently I use the following calculation to evaluate two fields: If(Max (mr_DPT) > Max (mr_DT); Max (mr_DPT); Max (mr_DT)) Thanks, Scott

I have created a DataBase for a testbank and would like to print a test from it, and have my sort/selection numbered (ascending) for printing. Perhaps I just need to be directed to the correct format, but I don't even know what to look for. Any help appreciated.

In FMPro 10, I have a field called "Staff" which displays a set of about 30 names. On a layout, I have (using Field/Control Setup) checkboxes against these names, so the user can select any number of Staff to assign to a particular event. I now want to create a Calculation field which concatenates the names of all the 'checked' names into one field, so I can display them as a string of text on another layout. How do I do this?

Hello to everybody, I would like to know the number of pages I am going to print at a layout. I need this number for other calculations. A way I can accomplish that is with: Ceiling ( Get ( FoundCount ) / 26 ) where 26 is the number of records a page can hold. Is there a way to get this number dynamically. So this script to work at an other layout where there are 30 records per page?? Thanks in advance

This one's a little tricky and I'm sure there's an elegant, simple solution that I'm overlooking. I want to limit the number of records a user can create in a runtime demo file to say, five records. I also want to leave the Status Area available since it'd save time/coding for such a simple project. So far, I have a Summary field ("record_count") that will count the number of records in the Master table. What I figure is that a calculation can be created using that field: if its count exceeds five records then the user is flashed a Custom Dialog message saying that he or she has reached the five-record limit and cannot create any more records. ...and therei…

I apologize if this is a double post but it went to portals the first time wheich makes no sense. I am a relative Novice. What I am going to ask about is something I do in Excel all the time Suppose I have a database with 100 records; I do a find on a field and locate a unique 10 of them I want a time field to start at 8AM with the 1st found record and increment by 6 minutes. until I get to the end... 8:54, I then do another find and have a unique set of 12 and again start at 8AM and add 6 minutes to each until I get to the end at 9:06 etc until I have worked with all 100. I don't want these to change based on a new find or sort unless I tell it t…

Hello Guys, It has been a while since I've been on here, but I am back and have a couple of questions. Has anyone here every used FusionCharts? I am trying to setup one of their charts in FileMaker 9, but I have my calculation field is not working for me. I need to calculate the total number of jobs in the database and break that down by year and month. I know I can setup a report with totals and subtotals to do this but I need all of this to happen in just two fields in list mode. I have been trying to put a calculation field that will give me the follow example. 2008 Jan 298 Feb 829 Mar 90 Apr 193 May 75 Jun 478 etc…

Is it possible to use the Substitute text function with wildcards? I want to take a field containing completely unpredictable text and replace every occurrence of a number preceded by a "$" with the same number but with a comma as the thousands separator. For example, replace: The cost of the job will be $5678456 give or take 10%. with The cost of the job will be $5,678,456 give or take 10%. The words before and after the dollar amount will change. Also, there may be more than one dollar amount to substitute. I tried using $#### as a Substitute search string but it took the #'s literally which wasn't what I wanted. Any suggestions woul…

I know there is a way to eliminate the field border from highlighting when entering in fields and for some reason I can find it now. Can someone refresh my memory?

If I use a field from another table it updates info instantly. If I use a local field and set it to lookup info it doesnt clear out once it gathers info. Is there a way to change this. For Example: When I input the word Apple on I want to cal Fruit (Which it does) But when I go back and clear Apple in still shows the calc of Fruit. How can I change this?

Dear Forum members This is quite possibly a simple FileMaker solution, but nevertheless as a beginner I have struggled to find a solution. I have two related tables. Products (Primary key: Product ID) and Product Prices (Foreign Key: Product ID). There is a one-to-many relationship from products to prices, meaning that one product can have many different prices at different dates. So typically a Product Prices Table would have a record for a specific Product ID, a date field and a price field. Now, I would like to produce a number of fields on the Products Layout that give the %change in prices related to that product for each month. You can assume that the…

Is it possible to Save as PDF with working hyperlinks to external websites in the PDF? Is there maybe a plugin to do this?

I have a Calculation Feild containing the Calculation FilterValues ( Check Box Details ; "Motor" ) but the feild will only display the results when i click in the box as if to type in it. And when i click away it disappears again. It also won't behave properly in scripts. Any help would be Much Apreciated

Hi, how can I have 1 calculation field which automatically enters all of the data from other fields (50) (within the same record). The main field will result in a very long sentence. Also, is it possible to use the 'if' function within the same calculation. I've been away from FM for over a year - & forgotten everything!

I have "shipper" field, which includes shipper name, address, city and zip. All these in 3 rows in one big "shipper" field. I have check auto-complete check box, but it auto-completes by row. If I wrote everything without hitting "ENTER" key... it is working, but it isn't aligned wheel. How can write the shipper name and address, city and zip to show up on separate rows ? Should I create a separate fields for name, address,city and zip ? Any ideas ? Thanks in advance!

This is hard to explain but, look at the attachment. My question is how do I get my report to show the behind fifth field. This is important as bowlers need to know how far they are behind first ( I have that working) and how far they are behind 5th (making the team). Thanks. I believe it is some scripting and using the set script function.

Sorry subject isn't very descriptive, not enough room. I've got a report that subtracts one date from another date, and puts the result in a very simple calculation field. It's basically just date_2 - date_1. Additionally I've got a summary field in the footer that gets the average days from that calculation field. My problem is when we get a row that only has one date added, and not both. When this happens I get a question mark in the calculation field, and apparently what Filemaker is doing is just plugging in the number 733533. At least that's what the field shows when I export it to Excel. So obviously this completely throws my Average field off, a…

Hello, could anyone tell me if adding field validation (maximum number of characters) on a couple hundred fields adds considerable overhead to FMP? I guessing this works differently than setting field size in MySQL. Thanks

Hi , i am creating a project that contains the email id and mobile bill amount of all the employees.what i want is that i want to create a group of all the employees and after that i am want to send their mobile bills to each groups and the mobile bill is different for all the employees. the main problem is that i want to send their mobile bills on a single click of send email.in apple mailer it opening different windows for each email id. plz help me in this issue. Thanks and Regards, Deepak Kumar Gupta

Hello, all. I'm trying to create an "Age:" field calculated from two others. That is: DATE-YOB=AGE (YOB is "Year of Birth," not a full DOB) Notes, requirements, & issues: 1) Because of my own inexperience, the DATE field is formatted as a number rather than as a date field. 2) I need to deal only in years, not full dates (approximate ages). So we're talking basically AGE = 2006-1978 and AGE simply containing "28" 3) YOB is formatted as a number, and it contains only a year, not date-month. So YOB is always 1979 1987 and so on DATE is formatted as dd-month-yyyy with dd and yyyy as numbers, month as a word. I'll basically need to grab the …

I have a field called "paid by" everytime a customer comes i fill out what they used to pay, Cheque, Cash, MasterCard etc... is there any way to calculate how many people used MasterCard? (every record is one person). Thanxs for any help

Hi, I've run into what seems like a fairly straight-forwards problem but I can't figure it out. I have my principal record "table1" with my related "table2". "table2" takes "table1"'s primary key. I have a field in table 2 called status that can be set to "Open" or "Closed". I also have a status in table1 that can be "Open" , "Closed" or "Started". The way I want it to work is: -If there are no related records in table 2: status = "Started" -If there are "closed" and "open" related records in table2: status = "Open" -If there are only "closed" related records in table 2: status = "closed" I've been trying to figure out a calculation but I don't k…

I apologize if my subject line does not clearly explain what I am trying to do. 2 tables: teams and games teams ------------ *team_id, team_name T0001, TEAM 1 T0002, TEAM 2 games ------------ *home_tm_id, home_tm_name, *away_tm_id, away_tm_name T0001, , T0002, I am trying to calculate each by the specific team_id being entered in the home and away field ids. So when you select a team as a home team (which is linked to teams by team_id) it will know to populate the home_tm_name with the team_name that corresponds to the team_id used in home_team_id. As I am pulling in two team_ids I have been unsuccessful and calculating these…

What are your 'best practice' recommendations for this? In the past, I have one table for entering income and expense amounts, with sub-totals by category (ie, each record has fields for id, date, category, amount). The expenses were entered as negatives. I'm in the process of building a new database, with a similar situation (income types, less various adjustments = net income) and I'm second-guessing how it should be structured. Do you recommend: a. one table with income and adjustments (as I've done before) b. one table with separate fields for income and adjustments (I don't think so) c. one table for income and another table for adjustments d…

Hello All, I am trying to do comparisons of sales between a month and the previous year's same month. So say this month so far, i have $50,000 in sales, I want to compare that to May 2008 sales. I have the dates down. I can show this month and i can show all of the previous year month. The problem that I am running into is making the previous year month dynamic as the current month is. This is how my calc work now. This May 2009 so far i have $50,000 in sales, May 2008 it will show $250,000 in sales. It is showing the entire past month, not keeping it up to date with where we currently are in the current month. …

I am trying to calculate a value as an "If" function but I can't seem to get it to work... I am trying to create a calculation that will take in consideration the following. If a "date_field" is between dates1 and dates 2, calculate result, If a "date_field" is between Dates3 and Dates 4, calculate result. I cannot seem to get this 2 work... I can view records in table based on a date from and to but cannot work out the calculation for this result.. TIA

Hi I have two field one call Time Field and one call Press Field. When I click on the Press Field to select one of the press. The Time Field will show the present time. But sometime we need to modify the time, but on the calculation field we can do that, alway get "This action cannot be performed because this field is not modifiable". Does any one have a better way to do that. I attach the sample file. Thanks gregory Calculation.fp7.zip

Is it possible to use a calculation like (Filter ABCDEFG....) But specify the sequence and length of numeric and alphabet characters. Example 1 Relating the initials to specific character sequences thru drop downs. I have it set to read the last example but I have multiple sequences and lengths that vary per "initials". I use the Filter ABCDEF... but not sure if adding the just 1234567890 will work with alpha characters. Example 2 "AB" to J098374 filter all sets with J0 "AC" to 20090505 filter all sets 20090505 starting with 2009 and 8 characters long. "AD" to AD20090505 filter abcdef.... work but can I add the others into one calc. …

I am using this currently but must be missing something because I am unable to replicate it for another use. I want to be able to select an item based on what customer I have selected.Each job number has the customer initials in the beginning of it for example. Apple Pie is AP12345. So when I select AP from the initials drop down I wont see job numbers like AB or AC or AD etc. Customer Initials Job Number Selection = Filter (Job Number; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ) I then relate to on each table? I have changed this over and over but missing something because it doesn't work.

Hello I'm compiling a table that has a number field. However, sometimes it will be US$ and sometimes some other currency. I'm not sure of the best way to handle this, but I'm thinking that I need to add 3 fields: Text: Currency type Numeric: Exchange Rate Calculated: Amount * Exchange Rate 1. Do you have any alternative suggestions? 2. with the above structure, how do I make Currency Type have a default value of "US$", and the Exchange Rate have a default value of "1" Thank you - Marion

Hi, I'm working on a database for a Mac users group. Right now I'm using FMP 5.5, but I'll be getting FMP 10 soon. We will be giving away a prize in December, based on members who attend our monthly meetings. For example, if a current member attends the January and March meetings, then they'll have two tickets in December when we do the giveaway, if they are still a member at in December. Thus the question at hand is a field definition, which is a calculation. The CASE function is working fine for the first two conditions, (they did attend the specific meeting, and their membership date is in 2009) but I can not get it to work for the third (that they renewed wi…

Hello I am trying to figure out how to have a field that automatically calculates the cumulative salary of each contact in a very simple payroll db. There are 30 contacts in 1 table. A 2nd Table contains the contact pay sheets. One of the fields in the pay sheet is a cumulative - running total of all that person's salaries in a calendar year. I am struggling to find the tools to tell the calc line to only sum using the contact ID number in the paysheet & only over a calendar year? Many thanks

Hello. Another question; I do have, in my db, a single table. I have the fields: "ID" "Productnumber" "Location" "In_Stock" "Total_In_Stock" My question is; I have several productnumbers, both with and without letters/ numbers, but they are not unique. Each Productnumber is given a location. Each Productnumber with Location is given a number in stock, from 0 to 2000. Each location is not unique to that productnumber. But, I might have the same productnumber several times, but with different locations, and different number in stock. I need the field "Total_In_Stock" to count the total number in stock for each Productnumber. I.…

Sorry for this maybe easy question, but don´t get the right answer. How do I combine two databases into one new database? Records from Table A should be a summary from both Records Table B and Records from Table C. The record should be included in Table A as soon that a new Record in Table B or C is started. Somehow I need an Instruction for Dummies. Thanks for your help. Frank

I've attached a file I could use ideas on. We have an administrator who checks incoming data. Sometimes there are records which should not be allowed in (but can't be deleted) and should remain in a Holding table. Currently, she has to boolean check (1) any record to ignore but we are trying to automate the process because sometimes there may be 1,000 records come through all fitting the criteria NOT to allow in. What is the criteria? It is patterns within one single text field. We want Admin to have rights to tell us the patterns and calc to automatically eliminate those entries. After import, she can change her mind (because the number field "Ignore" will be …

Hi all, I've been trying for a week to accomplish something that i suppose its easy, but i just cant make it work. I have a layout with 2 tables, on for incoming $ and another one for expenses. I also have a total amount money (in a global field), and a balance of the incomes and expenses for that particular field. What im trying to do is to have a field saying how much money was there before those incomes/expenses, and another one saying how much was left. I just cant find a way to do this, any ideas ? Hope my english was clear enough. Thanks for your time

Hello, everybody I'm new to the forum and I have one case, which I could solve. I will try to explain it as much as I can. I want to create a relationship between Post Codes, Wight and Rates. My idea is - when I enter a Post Code and Weight - the rate automatically to show up. So I create additional table with all zips codes and rates. For my rate quote field, I checked "Calculated Value" and following formula as Case Case ( Max ( WEIGHT;DIM_WEIGHT) <= "100"; rateszips 2::onehunlbs; Max ( WEIGHT;DIM_WEIGHT) > "1000";rateszips 2::onethoulbs; Max ( WEIGHT;DIM_WEIGHT) > "2000";rateszips 2::twothoulbs; Max ( WEIGHT;DIM_WEIGHT) > "3000";ratesz…

Does anyone know if portals can be made to work in a horizontal manner rather than a vertical? I'm producing a database in which I can print out a 'Movement Order' which shows the entries from another table. It would be great if I could have the 'Roll No's' running across the page as oppose to down the page so I can fit them on one A4 (if there are lots of rolls). Thanks

I'm writing what in essence a simple database but have got stumped at the following. The database is to track the movement of certain film rolls in and out of two locations. There is a record for each roll 'roll numbers' which have a 'movement order no' assigned (separate field) as they get shipped about. I have to produce a document to authorise this movement. How can I create a field that shows all the 'roll numbers' for a 'movement order no'. I know I should be able to but can't get the function right. I've created a report which will count the number of rolls per 'movement order' which is great for costing but I need the specific 'roll numbers' to cre…

I have been trying for hours to get my calculation correct. Please someone give me some help to make this work. Here is my dilema....I want to show an overdue field when a file has not been returned by the specified due date. I can get it to show "Overdue" but I can not get the "Overdue" to go away once the file has been returned. Can someone please help....this is what I have done..... I have four fields (Overdue)= Calculation Field RA Date Received RA Due Date RA Date Returned Case (Get(CurrentDate) ≥ RA Due Date; "Overdue!"; IsEmpty(RA Date Received) and IsEmpty(RA Date Returned); ""; not IsEmpty(RA Date); "";) I know I have something b…

How can I calculate the value stored in a container field? Thanks.

This seems like it should be a pretty simple thing to do but I can't seem to get it to work. Basically, I have a few merge fields in the footer of most layouts showing record creation, account creation,modify timestamp, and modify account. These housekeeping fields are named the same in every table, so I was hoping I could do something like create a global text line with the names of all these housekeeping fields set in a merge field format, such that I could just display this global on each footer and have it dynamically update according to current record. By doing this, if I ever decide to change the format or what information is displayed there, I only have to do it…

I want to create a list of layouts, but not all of my layouts. I'm sure this is relatively simple, but I'm having trouble figuring it out. Let's say I have Layout A through Layout Z, but I just want to pull Layouts between B and F. I know that I can use LayoutNames(Get(Filename)) to create a list of all of them, but how could I use the text functions to pull what I want? Thank you.

Hi, I have a field where I have first name and last combined but a comma in between them like Smith,John. I have over 70,000 names. So I can't manually separate these first name & last name. Is there a way I can separate these first name & last name? Any help would be greatly appreciated!

I have two fields displaying numbers. Field 1 and Field 2. How would I get Field 3 to show the lowest numeric number from either field 1 or field 2 but ignoring fields that are blank. Whats the smallest value out of 1 and 2 ___Field 3___ Number Field ___Field 1___ Number Field ___Field 2___ Anyone Help with this?

New York State now requires physicians' prescriptions to indicate a patient's present age in years and months. Knowing Date of Birth and Present Date, can anyone provide a calculation code (or direct me where to find one) that will yield one's present age as follows... "xx year(s),xx month(s)" Using FMP v.9.3 (Standard) Any info is greatly appreciated. Barry Sommers [email protected]

Hi, I'm having a problem trying to validate a field. I have 3 fields. 1) Job Status: This is a drop down with Won, Lost, Pending 2) Date Won: Date field 3) Sold For Basically I don't want the Date Won field to be blank when Status=Won or when the Sold For field is not empty. This is what I have so far, but it's not working. The validation message still comes up when all three fields are either entered or empty. [color:red](not IsEmpty(Sold for) and not IsEmpty(Date Lost)) Any ideas on how I can do this?

I'm working on a database where I've got values that need to be carried forward from week to week. IOW, on a current "note", I'll input some data, like weight, and the next week's note, I'll need that previous week's weight in the field for "previous week". Do I need to create a table for "previous weights" or is there some way to just display the previous week's weight without having to do that? Similarly, there are some checkboxes that are marked on each week's notes and I'd like those checkboxes marked with the previous week's values.

I have a database that has a results page. Like so: game 1 game 2 game3 total behind pos 100 200 300 600 ---- 1 99 156 210 455 145 2 I can get the totals to work but How do i get the amount of points behind the leader. I have a maxtotal field set as a container. then when i do the report i set that field to the value of the first records total. Then for the second and subsequent records, I take the container field and subtract the 2nd records total. and the position is a ranking field that will show ties if they happen, eg 1,2,3,4,4,6,7,7,9 etc

I have a FMP 8.5 DB for an annual car show. One of the fields is called "Division." The field setup is set to Pop-up Menu with values from a value list "division". The division are 20's, 30's, 40's, etc. I am trying to create a report or script which will give me an update to date number of entries in each division. If I create a field such as "count 20's" and code it's type to 'summary', I can get a total count of the division entries, but can't break it down to specific divisions. I have also tried to write a script. I have been able to write the script to print/show a report of all entries in each division, however as time goes on it will be necessary to b…

I named a field on my layout: 1B-L::FilterField1 (object name). I'm trying evaluate the contents of this field with the following calculation: If( GetField($LayoutName & "::FilterField1") < 1 ) It doesn't work. If I use the same to define a variable, I get a "?". $LayoutName is fine, it returns 1B-L. Any ideas?

Hello. I do have, in my db, a single table. I have the fields: "ID" "Productnumber" "Location" "In_Stock" "Total_Locations" My question is; I have several productnumbers, both with and without letters/ numbers, but they are not unique. Each Productnumber is given a location. Each location is not unique to that productnumber. But, I might have the same productnumber several times, but with different locations. I need the field "Total_Locations" to count the number of unique locations, for each Productnumber. I.E. I have: PO# "920-580" on both Location "H1" and "291504". The "Total_Locations" would be 2. PO# "MayOS" is locate…

Is there a simple way to show balances to date when viewing a list of records, i.e. just like a bank statement? The most recent dated record shows current balance but other records show the balance applicable at the record date. Thanks Keith

I am creating a report on employee time data. For each employee I have time logged and the total hours available for work in a date range and a target utilization percentage. The utilization for each record is calculated as (Time/Hours available for the employee for the date range). The report has a sub summary part by employee that shows the total hours and the Utilization which is a Summary field (total) of the field Utilization. For example an employee works a total of 30 billable hours but is available for work for 40 hours, so the utilization is .75 The target utilization is .85 and I want to show the variance to utilization in the subsummary part…

I am new to Filemaker and would appreciate a little basic help. I have designed a database for my on-line username and passwords. Can someone tell me the best way to create a button next to the field so I can copy the information in the box at the click of a button. Then I can just paste it into the username on the website. Thanks for helping.

Not sure if this is the right forum for this or not, but does anyone know of a good white paper or read me on globals? Basically what I am trying to accomplish is when a user logs in, they go to their related records and only their records. Is globals the way to accomplish this?

Hello there. Thanks to an excellent nudge from [color:blue]comment, I've figured out how to make portals work in my database. However, I'm stuck with a Calculation Field I'm trying to use as an ID#, since some records may occupy up to four values, and I can't get FM to read beyond the first one. My database has a Table-1 of up to 200 records (employees) and a Table-2 of 71 records (tasks), selected on Table-1 by 5 pairs of Value Lists via Relationships. Not everyone performs tasks from Table-2, and I want to automatically assign them all with an ID# of 0 (zero). Whoever performs a specific task, receives an ID# specific to that task (from 1 to 71). Then come…

I should be ashamed I know about placing a question regarding these most simple of fields when you have such straightforward formatting tools!! But this is ukdave (me) and if I cannot understand something I will put my hand up and ask - until I do understand. Please find attached 4 small files showing (Oh No! I see I am only allowed 1) (a) the formatting settings I used for Date and Time and ( the results of keying in information (not necessarily the way one should) nevertheless keying it in the way someone perhaps using the database might do. After all they are not expected to go into detail they just see the time as say 2.35 p.m and key in literally…

Hi: would appreciate some help as I'm affraid I'm still just a beginner. I have three fields: Part No. Bin No. Sub-bin No. Parts go into bins this is an existing spreadsheet we inherited from a parts supplier that went out of business. We want to figure out if a Part No. is assigned to more than one Bin No. and/or more than one Sub-bin No. There are 1.6 million parts, 58 Bins, 1142 Sub-bins. all irrelevant...but you get the idea of what we're trying to do. bins and sub-bins are numeric and part numbers are alpha numeric. Thanks!! Doc

Hi If i need to total the no of day, in calculation which Function should I use? eg. 14/04/09 to 16/04/09. Total no of day is 3 day Thank gregory

I'm completely baffled with this time format problem. What I need is a time field, 24 hr, hhmm, in which I can enter four consecutive numbers and have the format show as HH:MM. For example, if I enter 0900, it will show as 9:00. I used a calc in FM9 that worked great, but in FM10 I get a different result. 0900 gives me 90:00? I have looked and looked for some interference with the calc and I'm stumped. The field is set to AutoEnter a calculated value, validated with a timestamp to only allow five minutes to change the input. Here is the calc: Let(tt = GetAsText(Self); Case( Length( tt ) < 5; Choose( Length…

Hi there, I^m pretty new to Filemaker and having problems to find the right approach to a dynamic calculation field problem. As you can see I have three different points that mark a weight for a mass&balance calculation for an airplane or helicopters. these weights are calculated (sum and division within Filemaker) and now should be made obvious on top of a second layer on a flight envelope. Does anyone have an idea how I could solve this problem ? The points should be calculated (I think I solved that step already) and the "printed" or "displayed" on the envelope. Thank you very much

Hi I would be grateful if you could help me out here. I guess this must be simple but I cannot get around it! How could I create a script to go a record according to the value in a field? I would like to be able to move through different layouts, based on value c or value z. So if in field 'type' the value is 'z' I would like to go to layout 2. Otherwise to go to layout 3. Many thanks!

I just want to calculate the number of items checked in my field, the field is a text field with a value list displayed as checkboxes, If 2 items are checked I want it to say 2, etc... I know this seems like a simple question but for some reason I don't remember how to do this. Thanks for the help!

I have a field called ClientID in many tables. I want to access that field name using the same code but in different tables. Thus far I have tried different variations of the following without success: Get ( ActiveFieldTableName ) & ": ClientID" Any help will be appreciated.

Hello All, I am new here and looking for a little help on a problem. I have a database with one table in it called "Leads V1.2". I want to create a field with a calculation that will count how many times a value is selected from a drop down field called "Confirmed Results". IE. I have a field (drop down) that is called "Confirmed Results" That field is populated with a value list called "Confirmed Results". This value list has a value called "Confirmed". I then created a calculation field where I used the commands Count ( "Confirm Result" ; ValueListItems ( "Confirmed Results" ; "Confirmed" )) . But this is not working. I hope I provided enough detail to get …

I've currently hit a roadblock I can't seem to get past in a solution revision of a past build. The past build that is being revised is really old, was designed in Filemaker (5-6) era and has only been thrown together. The obvious answer is the solution needs to be rebuilt, but the folks in charge don't want to work on that currently so it needs to be retrofit. We use a numbering system that might be slightly odd. Job Number: This is the number that is assigned to any job that enters house. This number can be duplicated for each re-order as long as their isn't physical file changes which causes it to receive a new job number. Each classification of …

I have 2 tables. One to many relationship. I would like to get a count of the children that relate to the parent. I can't seem to get this to filter right. I keep getting a count of all the children or nothing at all, no matter what I do. If an example is needed I can whip one up. Thanks for any help.

I have imported records from another application into Filemaker pro 7. I have one field called "Category code" in which from zero to seven letters (a,b,c,d,e,f,g) appear, which correspond to category codes in the old application. I created a new calculation field called "CATEGORY" which is supposed to look at the category field and put in English words corresponding to the category code(s) (if any). So far I have not figured out how to do this.

I have an excel file that I import into my database It contains a timestamp as one of the fields. I only need the date portion of timestamp and not the time. I would like to run a script or maybe just make it it a calculation field to remove the time portion. Here is how it is formatted: 9/12/2007 12:17:12 PM I only need the date. I know there is probably a simple way to get rid of time but calculations and I seem to disagree alot. Thanks for any help.

Can some explain to me why the Calculation below works for most date ranges, but not all, like the two date ranges below. [unstored,from Company,= Case ( Get ( CurrentDate)≥ s_Contract_Start and Get ( CurrentDate )≤ s_Contract_End; "YES";"NO")] Shows "NO" if: s_Contract_Start= 4/5/2008 s_Contract_End= 4/4/2011 and s_Contract_Start= 6/26/2008 s_Contract_End= 6/30/2009 Both should show "YES".

I have a table named "jobs" I have another table named "line items" I want to create a report that will summarize a found set of "jobs", and it seems to work best working within a "line items" layout. Except I cannot summarize fields that are from the "jobs" table. So I try to build the report based on the "jobs" layout? Then I seem to have trouble summarizing all the "line item" fields. Where am I going wrong?

I need to create a simple status bar, which not only changes color, but also length. My idea was to create a calc field (type container), with 12 repetitions which draws from different container fields containing differently colored images. Case ( CurrentTable::Job Status = "Draft"; Settings::Blue; CurrentTable::Job Status = "Creative"; Settings::Green; etc. ) For the "Draft" status, I only want the first repetition to be filled with the blue container; for "Creative" the first and second repetition filled with green, etc. How can I change this calculation to access and influence repeating values in that manner? Is this possible? Thank…

Translation needed... I need to make a calculation field to produce a bank transfer reference check number. It is to last number added to the reference string. I have a php calculation for this, but I do not "speak" php, so could someone be so kind to translate this to FM calculation format... php: $number1=$_GET('number'); $ref=CalculateRef($number1); echo $ref; function CalculateRef ($number) ( $multiplier="7137137137137137137" if (CheckNumber ($number)) ( $length=strlen(strval($number) ); $apui=strlen(strval($multiplier) ); for ($i=$length; $i>0 ; $i--) ( $result=$result+($number[$i]*$multiplier[$apui]); $apui--; } …

I have a one to many relationship where I need to calculate the value of a field in the parent based upon the value of a field in the children. It's basically just a true of false value. The parent is set to true by default. If all the children have a false value, then the parent is false. My apologies if the explanation is poor. I've attached a sample which makes things clear. I appreciate any pointers. conditions.fp7.zip

OK, I have muddled through this project so far putting together an entire client and staff admin database, and I am working on payroll functionality. I have a table used to record hours and dates worked. I have another related table to house employee information. these tables are related by a common employee ID number. I have set up a portal in the employee info table and I am trying to make a calculation to automatically break down total hours worked by month in a summary field for each respective month on the employee info table via the timecard portal. I do not want a running total, I want 12 subtotals one for each month. IE I want the hours worked in jan to be totale…

I've tried a number of permutations to get this to work but I just can't seem to nail this one. I have a TimeStamp field ("TimeStamp") and a Get(CurrentDate) date field ("CurrentDate"). I need a Case statement calc to test if 90 days has elapsed since the TimeStamp date; if so, a blurb of text should appear; if not, nothing. What's that syntax for this calc? Thanks! Rich

Hi there! this might be a stupid question, but I have no idea how to solve that problem. I need a field that will handle time in the format hhmm and convert it to hh:mm. Is there a quick way to solve that ? Thanks for your help

Hi I have a Table and selected a subset of records. I am trying to use the LIST function to list the contents of a particular field based on the subset records. Example: I have Table A that has 1 Fields. After a find I have 5 records. I want to display the contents of a particular field in a new field Record Number,Field A 1,A 2,B 3,B 4,B 5,E Output A,B,B,B,E Any help in solving this would be much appreciated. Thanks in advance Mac

I have a field where users input "frames" as a whole number and I need to have "seconds" calculated automatically. 30 frames = 1 second The resulting seconds would auto populate another field. Not sure how to proceed here. Any guidance is appreciated. Thanks

Hi there Forum Here is my conundrum: I have a field called "Full name" will takes info from "Title", "First name", "Middle name", "Last name". So in the "Full name" field I have the text calculation: "Title & " " & First name & " " & Middle name & " " & Last name Some of our clients do not have a middle name, but with the calculation as it stands, a space appears between the first and last names. Very petty, but would be interesting to see if its fixable? Cheers for taking the time! Tim

Calculate Repeating Service Dates I'm sure I'm just missing a small piece. I need to calculate the next date of service based on the frequency code. So if the code is AN (for annual) I need the calculation to be the date plus a year BUT I always need it to be the same day of the week. For example today is Thursday April 9th, the second Thursday of the month of April. I need the next service to be scheduled for the second Thursday of April in a year. The second thursday of April in 2010 is April 8, 2010. I need this to work for Monthly, quarterly etc. So always the same day of the week of the month. I know how to get Day of Week. any help is greatly appreciat…

Hi I am wondering if it might be possible to script the creation of a graphical button. I would like the user to be able to have a field filled with their topic and using another button they would be able to create a button in a specific layout in a specific location ( so they would never overlap) and be assigned a script that would be preplanned. Thanks