Calculation Engine (Define Fields)

Hi all, I've got 3 related tables: Orders Products Orderline Orderline is a child table of the other two. I'm trying to trouble shoot a calculation field on the order page, a sum calculation. I have to periodically change pricing for shipping rates and in this case want to be able to update the shipping rate field on the Products page without overwriting all the order records that calculated shipping totals using the old rates. Is there a way to do this? On the orders table I've got a field called "Shipping Total"Sum ( Orderline::ShippingTotal ) The Orderline field "ShippingTotal" is also a calculation: Quantity * ShippingUnitPrice and S…

Greetings all, I'm a rank noob - been at FM (and RDMS) only about three weeks, and I'm making great progress, but the last week or so has been trying to figure out one thing. I THINK I'm missing a key word or term in what I'm searching for, because it's got to be insanely easy and I'm just not getting there... I have a an inventory field called "Features" that uses a value list and displays as a checkbox entry. The item can have multiple values in the field, say "Feature 1, Feature 3, Feature 17, Feature24". The entry is fine. The problem is trying to format those values individually. I've tried several different incarnations of calculated fields and r…

Does anybody know how to make a calculation field that sees into all related records? I've tried a variety of calculation structures but none seem to be able to see beyond the first of the related records. I have a company that does partial shipments, ie, the order lines of an order can have individual and different ship dates. On the Order table I want the Ship Date field to read "" (blank) when nothing has shipped, "partial ship" or "" (blank) when at least one of the order lines has a shipped and "shipped" (or date of last shipment) when all have shipped. I've been able to get this to work using a Case calculation and others but only for the first related re…

i have a field( date 1) which i enter a date dd/mm/yy. In another feild (date 2) I want to calculate three months in the future of "date1". Ie date 1 = 1/11/08 date 2= 1/2/09 I thought this would be simple but i just dont get how to do it, please help .

Hi all, I’m new in this forum and regret for my bad English. I revert to you because you are the sole forum I found related to FileMaker; and for this, thanks a lot, I hope you can help me solving my current problem and the other I’ll probably have! But now the problem: I’m using a global field in order to have the same text in all my old and new records. When I close the file I’m working with and open it again, my global field doesn’t report me the value I’ve just written and the field is blank. I have to write again the text to be able to use the global field where it’s addressed to (a script opening a file in my net). I’d like to specify that I’m working on a F…

Hello All, Does anyone know of any slick code I can use to validate the user is inputting in email addresses correctly? Thanks Jalz P.S I found a custom function on Briandunnings website, but this was written 2005....anything newer

Hello — I have 2 tables. On the main I have a portal for items (description, qty, price etc..) Some of these items will be marked with a checkbox to say that this item is a stock (ie paper, boxes, shrink wrap etc..) and not actual product. Items table and main table are related through a Jobnumber.. Right now on the items table I have a sum field for a total. This gets me a total for all the items in that job. I need to be able to get a total of my products and a separate total for my stock. Is that possible in one table? Thanks, Paul

I have the following setup: Four tables: Customers, Orders, Order_Line_Items and Items They are related: Customers::Customer_ID = Orders::Customer_ID Orders::Order_ID = Order_Line_Items::Order_ID Order_Line_Items::SKU = Items::SKU I have a portal in my customers table which displays records from Items table. That way I have a list of the items a customer purchased on the customer layout. If a customer purchased the same item across multiple orders the portal displays only one item as intended and that part works fine. I run into an issue when I want to show how many of that item the customer purchased across multiple orders. I go…

I am trying to convert a time field to a number field. My field “Total Hours” shows the total in hours and minutes (39:30). Now to convert the next field to a number. The formula I am using is: Hour(Total Hours) It works fine unless there are minutes. 39:30 (hhmm) shows as 39.0 and I need for it to show 39.5. Please help and Thanks.

Hi, and thanks to everyone in advance for any help that can be provided. I have a field which contains text and a Field reference, as follows: --------------------- Cost Analysis = "Body of Text" & " " & SubTotal --------------------- The net result I'm looking for is a text block with the field data added to make a cohesive whole. The problem is, the number field is adding the extra digit, i.e. 549.155, instead of displaying the way the field itself does independently elsewhere, i.e. 549.16. How can I format this within the Calculation area so that it dislays properly? Or can I? Again, thanks for everyone's help...I d…

I have a table of permits and a second table of inspections. Each permit can have multiple inspections, but only one passed inspection. I have a field in the Inspections table drawn from a value list of Passed, Failed. How can ensure that there's only one passed inspection for each permit?

Ok, I have a following calculation in my file, the result is Text. Case ( Item_30_Day_Performance ≥ Settings::Inventory_Hot ; "HOT" ; Item_30_Day_Performance ≥ Settings::Inventory_Average and Item_30_Day_Performance ≤ Settings::Inventory_Average_II ; "Average" ; Item_30_Day_Performance ≥ Settings::Inventory_SO_SO and Item_30_Day_Performance ≤ Settings::Inventory_SO_SO_II ; "So So" ; Item_30_Day_Performance ≥ Settings::Inventory_DC and Item_30_Day_Performance ≤ Settings::Inventory_DC_II ; "DC" ; "" ) The calculation works just fine without any issues while browsing the Item records however when I switch to list view it gets rather slow. The calculat…

I need to convert the elapsed hours calculation and am hoping someone here can help. All hours are entered in 15 minute increments. My calculation (as an example) for an employees hours of 7:00AM - 3:30PM comes out as 8:30:00. I need the result to read that he worked 8.5 hours, so that it can be easily entered into the payroll program. How can I modify my current calc to achieve this answer? Mod ( ApptResEmp_TimeOut - ApptResEmp_TimeIn + 864000;86400) Thanks!

I have three related drop down menues whic allow someone to classify the type a file within our filing system. Each classification has a unique classification number which forms the first part of the file number. I want to auto enter this classification number when someone selects all three elements of from the drop downs I've tried lookup but it only works with the first find in a single list I've tried autoenter with a calculation using e.g. case, but can't get that to work either. Any ideas? Thanks Linda

Hi All, Need help with the following and I hope I can expalin it correctly. I have a feild for which I have created the following CASE function for in a Calculation. Case ( General Text 11 = 3;"Water is entering the subfloor through *(state location) elevations of the house and pooling along foundation wall – recommend drainage is improved to remove water.";General Text 11 = 4;"Hot water system overflow outlet drains towards the external wall of house and needs re-directing to avoid build up of excess moisture.";General Text 11 = 5;" The air-conditioning unit run-off is adjacent the external wall and is causing damp areas conducive to termite attack. Recomm…

Hello All, Can someone please help me on a layout, I am making a layout to look like a personel check. Please how to take the number of the total say 10.95 and turn it into text such as ten & 95/100 or $106.30 into say One Hundred six & $30/100 for the text line of the dollar amount on checks. Any help please Thx, Erick

I have some fields that perform a basic average calculation. The fields are using number formatting, and they are using decimal formatting. My fixed number of decimal digits is 1 because if I set it at 2, I don't get clean percentages. I'm using percent notation with no thousands separator. My question is regarding the rare ocassion when the average is actually 1. (The numerator and denominator are the same.) This causes a "?" to appear, but when you click in the field, it says "1". I can't get it to show as "100.0%" Any ideas? Thanks a mil!

A field defined as a unique serial number, validate always with the option of display custom message if validation fails checked. Is it possible to get a calculation in the custom message? I would like to display the custom message saying "This SKU is already in use by (insert name of the item that the SKU belongs to). Please assign a new SKU. Is it possible via custom message or should I try a workaround and if yes what would be the best way to go? Thank you.,

I would like to create three fields in order to culminate into an actual date. Month field (text field) Day Field (number or text field) Year Field (number or text field) Once all three values are in place, I would get a resulting composite date, like: January, 8, 2008 in a forth Date format field. Most all of my other date fields are based on a Date type field which requires all three (Month, Day, and Year) in one swoop. This is fine, but I have run across a scenario where I wish to be explicit in the information I need entered into the field. I have tried a few ways to get the data of those three fields back into a "Date" type field, but can not ge…

I don't know if this is possible, but hopefully it is. what im trying to do is create a record and have a selection that will prompt me to select a Product Line such as "COR". So if i select COR and i comit to the record i was my Serial Number to be COR001. and if i create another record with the same Product LIne "COR" i would want my serial number to be COR002 and so on, NOW the problem that im having is. If i have 3 Product Line that i can choose from, and instead of selection "COR" i select "MIM" i would want my serial number to be MIM001, so i would want my serial number to follow the next consecutive number within my Product LIne. IS this possible? Any help…

How to calculate the date of the next Wednesday? Any suggestions out there?

Field #1 is a number field with a drop-down list calling upon a dynamically created value list using values from another table. Field #2 is a number field with a pop-up menu calling upon a dynamically created value list using values from another table that are related to the value in field #1. The proper workflow is: The user makes a selection from the drop-down list in Field #1, then makes a selection from the pop-up menu in Field #2. When this happens everything works great. The problem is, the user can make a section in Field #1, then make a selection in Field #2, and then go back and change the selection in Field #1. When this happens it mildly screws u…

This is ridiculous, since I am fairly confident that I've done this before: I forgot to make auto-enter creation and modification date fields... now of course, I need them. I created them and tried to replace the field contents with a get calc.... I thought for sure there was a Get(CreationDate) or something like that in the design functions.... Any thoughts? thanks!

If it possible I need to come up with a calculation that when a new record is created in Table 1 it counts the number of occurrences of a particular value in a field in another Table 2. In other words when a new record is created in Table 1 I get a calculated value that counts the number of times the value "Active" appears in a particular field in Table 2. How do I go about doing that? I know I can get this count by drawing a relationship between the two tables and counting the records that way but I wondering if there is a way to write a calculation that counts occurrences without the relationship. Maybe a custom function?

Hello, I'd like to add a field to my Images table with a calculated list of Subjects that appear in the image. I'd prefer it as a calculated field in the Images table so it can be added to a portal in a different database which selects multiple Images for something like an album. Data Structure w/ Relevant fields: Images table -ID ImageSubjects table (record creation/deletion by Images allowed) -ImageID (joined with Images::ID) -SubjectID SubjectsList table -ID (joined with ImageSubjects::SubjectID) -Name Data entry in Images allows the selection of multiple Subjects via a portal. Here's what I'd like to do in pseudo-code: …

I'm hoping to write a database to help a small organisation keep track of their book sales and keep track of stock. They sell lots of titles a month and some run out sooner than others. So what they want is to do a stocktake, record sales, and show remaining stock. When stock hits a certain point it flags something to say reorder. I have a file that contains rows for each title: title | stockcount | triggerpoint I need to create another file that works with this data. How do I create a field that keeps a running tally as you put in the quantity sold in that period (eg weekly tally per record) it takes into account all previous sales to give you a 'remaining st…

Im adding automated clause numbers to a portal using an auto entry calculation and the Count function. Unfortunately I cant get the first row to number (presumably because it hasnt been fully created yet). I tied a case statement to no avail. Case(Count(minutes::minutes)="";"01"; Count(minutes::minutes)) What else could I use? i also tried isempty and isvalid. thanks

Greetings, I have added a new table to my database called "Serial Numbers" - I know, original. In the table "Serial Numbers" there are 7 fields. *SN_Serial Number Prefex *SN_Serial Number *SN_Category_1 *SN_Category_2 *Category Full Name *SN_Serial Number Complete *SN_Status SN_Serial Number Prefex: Just a field that has the first part of the Serial number stored, for example Adult Games is AG. Books & Mags is BM. SN_Serial Number: Just a field that contains the Second part of the Serial number, this is basically 500 records that have a increase of one. For example 0001 - 0500. SN_Category_1: Has a field that contain…

I know there are a lot of topics on this subject, but I didn't find any that fixed my problem. I'm a pretty new to filemaker so let me know if this doesn't make sense. So: I want a field (call it InStock) that will display how many records fit a certain criteria specifically: if instockinventory=0 and location=n then I want it to tell me how many records there are for the field called model number. The picture posted shows a find that shows all the records for the critera of instockinventory=0 and location=n and 4 records of that model number I would like that # of found records to be put into a field called instock, so I don't have to go into fi…

I am new at this and wasn't even sure how to search to see if this question has been answered. I want to be able to pull up an employee number and then create a list with a breakdown of how many procedures the person has done by code. A physician may do 50 differently coded procedures. There is a field for the code and a field for the doctor number. So rather than look each one of those up individually, I would think there is a way to create a list that could say that Dr. Smith did 10 of Code 490505, 4 of Code 47563, 30 of Code 19000, etc. It is probably some simple calculation field, but I am clueless about how to do this. Thanks, Dawn

I know how to make a case () statement, but I need some help doing a more complicated one. I can easy do one with the case statement looking up one (1) value and returning another. But how do you do one where you have to look up two (2) values. Example: Company_Name = Widget & Son Contact_Name = Joe Return value = 1156 Wood Street Vs Company_Name = Widget & Son Contact_Name = Tom Return value = 1200 Water Ave Both have the same company name but different contract name therefor different address. Thank you, Joseph

Hi everyone! I hope you can help me with this: I have a field called 'name', and a Value List with some names. What I want to do is to find a way to find out which names of the ValueList don't appear in the records... For Example, the V.List is: Pedro, Amalia, Martin. If in a record, the field 'name' is filled with 'Pedro', I want the calculation gives me 'Amalia, Martin' that are not mentioned. I don't really find the way... is there any formula? Here I attach a sample base. Thanks for all your help people. You're the best. idea_base.zip

Greetings, I was wondering if there was some way that you could disable a text box (for example) if another field has a certin value in it. i imagion i can get the if statement and alike working, however i can't seem to find anything that is simular to making a box deactive.

Greeetings, I have a Table Called "Products" and Another table called "Categories". They are relationship together and in the Table Categories there is a field called Count. Now count will go and count the amount of records that are in the Field "Category_1", however there is another field called "Category_2". "Category_1" will have a the following enteries, Books Lingerie Vibrators and "Category_2" will also have some fields that expand on that, so for example Category_1 might have Lingerie, Category_2 would then have things like Babydolls, PVC, Unisex, etc. Atm the count only runs off the category_1, it is causing it to duplicate some o…

Here's what I am trying to do. Working with the separation model I have two different files ("Interface_1" and "Interface_2") that both work with another file "Data". In both Interface files I have the same Field that I need to have trigger a script when the value in it is changed and I'm doing that with the help of the zippScript plugin. Typically (written in the Data file) I have the change in the Field triggered by an Auto Entered Calculation that replaces the existing value written like: [color:blue]Field & zippScript_PerformScript( "Interface_1" ; "The Script" ; "") ...and that works just fine,... for the Interface_1 file. But it ignores…

I have one table "Sales" in which orders are kept. Along with each record I have a field "weekly_sales" which calculates total sales for that week. The calculation uses another field "first_day_of_week" What I need is a layout that shows only one entry for each week. For Example: Week Beginning on: Sales 9/28/2008 $1,000,000,000 10/5/2008 $0.01 10/12/2008 -$2E1000 Cant seem to wrap my head around this one Thanks in advance

Hi guys, I am trying to make a button appear or disappear on a layout based on a calculation. If field "X" is not empty, I want to have a button appear on the layout that allow to execute a script. I defined two fields: 1: Calculation field with If ( not IsEmpty ( Implant::Failure Time );Implant::Failed_New_co) 2: Container field Failed_New_co The problem is that the copied icon loses its assigned script once I pasted it in the Container field. Any help please?! Thank you Paul

I'm pretty sure I know the answer to this but maybe someone has a brilliant workaround. Or maybe I don't know the answer. Anyway, is it possible to have more than one auto-enter calculation acting on a field? Example: I'm using a phone format auto-enter calculation that I like very much and would hate to remove it. I also like the idea of using an auto-enter calculation to show field labels in an empty field: Case(IsEmpty(field); "field label text"; "") Is it possible to have both calculations acting on a single field? Abbe Lougee

Hi, This might be a hard one, it might not. I am trying to combine repeating fields into one value. In the picture you see two entries that have the same value, Thailand and 5070-5984. I only need one, this is a portal. I need help on a calculation to just have one record. But the hard part is the number inside the bracket. I need to add those two numbers together. so the it should be: Thailand[339] 5070-5984 Need Help? Thanks

I need some simple help: How do you subtract two dates and then if the answer is Less than 180 days return a "1" to a field is more do nothing. I have attached what I thought would work but as you can tell if you know what you are doing it does not work at all. Case ( Policies::Current_Date - Background_Check_Chart_Broker::Requested_Search_Date ≤ Day ( 180 ); "1") Thank you, Joseph

I am currently working on a "virtual keyboard" in FM for use with a touchscreen . I have all my QWERTY and numerical buttons working but I am having trouble with the backspace button. I am triggering a script from the button with a Set Field command and have tried various Trim and Replace lines but it doesn't quite work. this is what I have so far: Trim(Middle ( Text_field; 1 ; (Length ( Text_field -1 )))) Any ideas welcome thanks

Main table is student ID information including a "mid-point" date that is individual to each student. Child portal table has student scores, each with a date. I have been trying to make a calculation field that will sum the student's scores after the mid-point date (which is entered into the main table when the student reaches that point). I'm sure this is simple.... Thanks for the help everyone.

Hi I am trying to use a send event script by using the following: "cmd.exe /c md &" "%USERPROFILE%Desktop &" "& Policies::filefoldername Filefolder name has the name of the file. This should make a folder on the desktop but I can not get past the calculation window. It will not accept this calculation. Help? Thank you, Joseph

I'm trying to have Field_B replicate Field_A but having spaces between each letter... I've tried this function: Let ( [ $text = text ; $counter = $counter + 1 ; $result = "" ]; Case ( $counter > Length ($text) ; $result ; CF_Spacing ( $result & Middle ($text; $counter ; 1) & " " ) )) It's not working. Can anyone tell me why? Thanks, Michael

Hello all I have a Contact table with at least 3 fields (first, middle and last names) and a field called "Fullname" that concantenates my other 3 fields. i.e. John C. Smith I would like to have a layout in another table with at least one field called Full Name that has a drop down button which I would like to list all data from the Fullname field in my Contacts table. Once I select a record from this list, it will populate that Fullname field. I know this may be easy to some, but I can't get it for now.

I know most would say just store the picture in the database but in this particular situation it is complicating things by doing so. I am looking for insight into two problems. 1. how to setup the database to calculate the value of the container field based on a field called "jobid" and store it as a reference to the file that is stored on a local server... So say, /proof_images/12345.jpg The proof_images is mounted at the root level on the client machines. 2. How to automate a task to insert this value into 20000 previous records. Once the previous records are updated it would auto enter the value on creation of record. Thanks for any help.

I'm using the Get(FoundCount)function in a calculation field on a layout. Shouldn't it give me the # in the found set after a find rather than the total record count in the table? If not what should I use to get the found set count if I'm not showing the status area? Thanks

Hello folks, is it possible to disable the return key for a text field? I need to prevent the user presses 'return' while he's inputting the value for a specific field. Thanks and have a nice day!

Greetings, I have a image feild that is set to "Container" and called "Image". There is another field that is called "Wholesale ID No 2" which contains the model number for the wholesaler. The Image name is the "Wholesaler ID No 2". What i want to do is set it so that it will automatically insert the pictures into the field using a web address and the model number. There is over 2000 records that need to be done, however everyone has a different model number. For example www.test.com/images/00001.jpg - model 00001 www.test.com/images/00002.jpg - model 00002 www.test.com/images/00003.jpg - model 00003 www.test.com/images/00004.jpg - model 00004 …

I am proud to announce that I have just managed to create a ground operational aviation control mission db. Meaning: A db which calculates and reports crew duty times, flight hours, schedules , cargo loads, passenger loads, fuel uploads and usage, departure times, arrival times, delay hours and reasons, daily, weekly, monthly schedules for airplanes and crews and quite a few more things. Even though, looking behind the curtain, many of the db cracks would probably consider it a joke…..for me it works and “ the mother even looks good” and last but not least, which was my goal from the very beginning - she is very user friendly. Now I need one more feature (for n…

Note that I'm using five records as an example. The real numbers are significantly higher. This is the last major calculation problem I'm having on my database and I could sure use some help. Table #1 ("Software") has five records (A, B, C, D, E). Table #2 ("Builds") has five records (V, W, X, Y, Z). Via a portal to Table #3 (see next), each of these five records stores a 'group" containing a set of records from Table #1. Table #3 contains multiple records that "link" Tables #1 and #2. For example, one series of records may contain "V" and "A", "V" and "C", and "V" and "D". Another series may contain "W" and "C", "W" and "D", and "W" and "E". Tabl…

Is there a way that you can pull the comments that you type under the Field (in Define Fields), other than doing a DDR? TIA Kia

A tale of two tables..... Table #1 has a number of fields in it, including one called "Software Link" (number field) which is relevant to this question. Table #2 has two fields in it, one called "Software Connection" (number field) and the other called "Software Count" (calculation field). Table #1 has a few hundred records. The "Software Link" field on each record is populated by a number with up to three digits (and it's not unreasonable to think that it'll go up to four or maybe even five in the future). What I need is a calculation for the "Software Count" field that will tell me how many times the value in "Software Connection" appears in "Softwar…

I have a text field which contains numbers, text etc.. for example. "012345","GB","012","045658","testing" I need to be able to grab a part of that text. Which will always be the same length of characters and always in the same position. so for example I need to extract the "045658" from the text field and have that part based on starting as the 21st character in that field.. is there a way to do this? FYI This information has been imported from XL and I need to seperate a part of the fields.. the fields in XL are not seperated. just as a csv.. TIA

Hi, This is probably very basic, but I can't figure out how to add text (.jpg) to a field in multiple records while retaining the original text in that field. Is there a way to do this? Thanks

has anyone come up with a fix as to why filemaker crashes when you have a container field set as Insert quicktime and insert a mp3 file into it and all the sudden it crashes on pc only. Everytime i insert quicktime and import the mp3 file into that container field, filemaker crashes and i have quicktime 7.5 on it.

I have a client using FMP 9 on a Mac. They have taken photos as well as scanned documents. I have seen the images and they are in the correct orientation but sometimes when they import the pictures they rotate in the field...most often they're don't. It isn't a rotated field issue...I have no answer. Don't you just hate these "sometimes" issues! Anyone out there experience this? Diane

Hi, Im not having much luck creating this function work, I have 2 fields, one displays a drop down list of months by number 01..02 etc. I would like to create a (if function?) to change the number to the month name in the other field (month name). Anyone do this before? thanks much

Hi There, First off, I am new to FileMaker and am use to access and their functions and am supprised the similarities. Now for my question. I am converting my database for a class registration. Part of the registration process needs a class date and expiry date. The expiry date of the certification in driven by the type of certification. Ie) First Aid is good for 36 months & Fall Protection is good for 12 months from class date. I have set up a table with the various classes and their validity period. I am trying to calculate the date from the class date "Event Date" to add the respective number of months in the class table I have called that…

Hi All, Here is my delimma. I have a timetable database and a workload database which calculates each teacher's 'points' based on the amount of hours taught. I am having trouble getting the workload database to look at the timetable database and select only semester 1 classes and add them up (same goes for semester 2) The way the workload database works is, each teacher enters their initial and on doing so will get a summary of all the hours they have taught in semester 1 and 2 and a total of their points. Here's one of the formulas I have tried: If ( Timetable::Semester="1";Sum ( Timetable::Workload points ) ) What it does is calculate …

This may be newbie, but I can't get it. In a standard summary report with 3 Summary Parts: Country State City Body is Person How do I count the number of (separate) countries, States and or Cities for the persons being browsed (If two people live in the same city, the city only gets counted once etc). Thanks Peter

Hello. This seems simple, but I can't remember how one might do this. I have a lot of records that need to be assigned IDs so as to relate them to records in another table. The trouble is that they are all spelled inconsistently and there are slight variations that would need the same assignation. I wanted to build a case function like this: Case( b_MatName = *"B&B"*, "038", b_MatName = *"PVC"*, "044", b_MatName = *"curb"* "012", "") I've tried this both with and without the quotes and with the quotes in and outside of the asterisks.... no avail. Any thoughts? Thanks, Sara

I've got a table with a number of fields, among them "User", "Model", "Type", and "User with Model". (All fields are text fields.) The contents are pretty obvious; "User" is the username, "Model" is the manufacturer and model information for that hardware, and "Type" is the type of hardware (mouse, laptop, etc). The "User with Model" field auto-enters a calculated value thus: If ( (Type = "Workstation") or (Type = "Server") or (Type = "Laptop") ; User & " (" & Manf Model & ")" ; "" ) This generally works fine. In records where the hardware is a Workstation, Server or Laptop, I get the appropriately generated field ("john_doe (Apple iMac)", etc). In r…

So I have a really tough time searching in here but know this has come up before......... In short I need to take my - List (field)- and add to it. [color:red]I see some confusion - my fault - edited to help little.. These are items sold and how many each customer bought... not how many of each in the table (sorry) assume it's for a used car dealership: Records table1::buyer , table1::item name and table1::item quantities john smith car 1 helen jones truck 1 james hunt boat 2 (he's rich I guess..LOL) table2::all items as list...I need to calc out to: Item.1=car&Item.1.quantity=1&Item.2=truck&Item.2.quan…

I'm drawing a blank here. I want to see if any of the records in my found set (Contacts) has a related record in another table (Dealers). The Contacts records would have a foreign key _kF_DealerID populated. I know I can gtrr in a new window and check the found count, but there must be a calc I can do. If I do a gtrr using found set, and the first record does not have a related record, then Get (LastError) = 101. So, I can't use that. GetNthRecord somehow?

Really aggravating problem. I have a client database that has a portal with notes at the bottom. Since I have converted from FM 5.5 to FMP 9.0, this portal does not have the scroll bar. I have deleted and recreated using the checkbox for scroll bar, to no avail. As a side note, I also have a closed client database with closed notes. It is a duplicate layout of the above mentioned database, except the clients are moved to this database when they are closed. Yes, you know it, that portal in the closed client database does indeed show the scroll bar. Another note, the mouse's scroll wheel works in the client database (on most computers in the office). I'm s…

I am trying to look up a value of a related record but am having some trouble. The relationship between the two tables is a phone number and one of the tables holds multiple records with this telephone number. I would like to obtain a fields contents from the last record oof this table. I thought about using the getNthrecord function but this requires knowing the record number so what I need is something like this GetNthRecord (field ; lastrecord) is there a way of Getting the last related record for this to work? thanks

I have an internal notes field area where the users click a time stamp button and then enter text, however I want the next time stamp entry to enter new text at the beginning of the text field, not at the end. Here is the current basic time stamp script I use: DBName::Notes_Internal & "¶" & GetAsText(Get(CurrentDate)) & " " &GetAsText(Get(CurrentTime)) & " - " & Get(UserName) & " - " Any ideas how to get it to push down previous entered data and make the insertion point at the beginning of the text field? Thanks

with any character & " " Something like this: Substitute ( textField, "*"; "*" & " ") Except of course this doesn't work. Seems like my brain isn't working too well on that... Any suggestions for the easiest way to do it? Thanks, Michael

I feel really dim... but I can't figure out how to extract the first line of a text field. The lines are separated with proper line breaks. I know I have to use the PatternCount and Let function, and somehow define a start and end point with the ¶. Anyone?

I hope this is an easy one. Let's say I have a three-level table structure describing a collection in which each item belongs to a single series which is in turn part of a larger Category, and I want to give each record a unique "Collection #" (not used for key purposes), where the numbers are built out based on the parent. For instrance: Category # 105 Series # 105.003 Item # 105.003.022 I want each # to be unique within its table -- easy for the Category # - - - but how do I best enforce uniqueness in the Series and Item level? What I *want* to do is build up the numbers based on the parent records so that a user can't violate the rules of parent-chi…

I am looking to find a way round a problem I have. I want to have a script (scheduled by FM9 Server) to email a query list to a supplier but because Server 9 doesn't support 'save as PDF (Perform without dialog)' sending as an attachment is not practical. So I would like to list the records within the body of the email. My script finds the records in question but I need to list some information from those records in my email. I thought about creating a globalfield and looping a SetField , pasting the info into the global field but it sounds a bit clunky. So I am wondering if there is a more elagant solution available thanks

Hello Is there a way to use a calculation field to perform calculations on other unstored calculation fields? I am trying to sum a few unstored calculation fields with another calc. field, but nothing is displaying on the field. Joe

I've been searching an experimenting, but not getting anywhere... I would like to send an Object Name as a Script Parameter, when a button is clicked (the name of that very button). Ultimately, I need the result in a Variable, "$ButtonResult". Does anyone know how to accomplish this? I found the "Get(ActiveLayoutObjectName)" function, but it never returns anything... Thanks! Michael

There is a text field in my database that contains letters, numbers and spaces. Is there an easy way to remove all spaces from thousands of records in FM, Excel or other ways? Thanks for any help! -Sal

Hi, I am relatively new to filemaker. I wasn't sure where to post this, but since i think the calculations are causing the problem, I will post it here. I am working on databases for the recording studio I work at (we are making the transition from keeping our clients records on paper to keeping them on the computer) The database has some basic calculations (cost=rate*hour, etc.) and some calculations take information from the previous record. The problem is, once we get over approx 10 records in one client's database, filemaker starts performing the calculations very slowly. When the necessary fields are filled out and its time for fm to calculate, it takes abou…

Hi again, I understand that $$ creates a global value. I have a script that sets a global value and then opens each layout (separate tables for each) in turn and adds a new record to it, populating a matched field with the contents of the global. at least that is what it is supposed to do. It works perfectly on the first layout it goes to, and after that the global field is not populating the table field as indicated. The text is identical, save the table name and this leads me to suspect it's the global at fault. I shouldn't have to reset the global value at every step so the next fields can use it should I? Cheers

Here is what I am trying to do but cannot seem to figure out: I have a sub-contractor that does work for me and enters job details into Filemaker so we can pay him. He gets paid a different hourly amount depending on what type of work he does. So I have a portal where he enters the work he did and the amount of time he spent doing it. He also selects a value from a drop down list of the TYPE of work he did. I would like to have a calculation that will add all the hours based on the TYPE of work he did. For instance: He does 2 hours of TYPE A work and 1 hour of TYPE B work on Monday. He then does 3 hours of TYPE A work and 2 hours of TYPE B work on T…

Hi all, I am trying to set a YES NO checkbox field to either YES or NO in a script but the programming tools will not let me - it states that the table cannot be found. Any ideas? Thanks

Like my subject line says, I've inherited a Database in Filemaker 5.5. There are 12 related databases which contain figures from invoices which have been paid to 5 different accounts over time. Each payment has an account, country, category, sub category and item, amount, and payment quarter attached to it. I need to be able to see all the payments by by country, with the sum of the items grouped by subcategory, by payment quarter, while being able to compare the figures of each account. I'm trying to decide which tool to use to begin creating this layout. Any suggestions??

first of thank you everyone that has been helping me today, so far i have managed to get everything to work and i am now using the stuff you taught me to do other things now! I am working on a calculation that will take 1 date and todays date and give me the number of days left. e.g.(days) 660 300 5 0

Yet another question from a beginner I have a field set up with a drop down window for an anticipated life for a battery in years e.g. "2" "8" but I want a second field to automatically fill its self in when you select 2 or 8 I want the second field to automatically say 6000 or 21000 cycles depending on the time chosen. Am I way in over my head? Should I be thinking of a more simple way to do this?

I am building a database for my father and he wants it to calculate the expiry date on warranties. I can do it if I make 3 separate fields like "day" "month" "Year" because the warranty date is 1,2,3 years ahead so I can just make the year change by the number of years. But,,,, I have all the data entered using the drop down date function, how would I get it to change the year by 1 2 or 3 years using the drop down window for date and number of years for the warranty?

Hello, My Windows XP is in English. The localisation of my Windows is set to English (UK). My Filemaker Pro 9 Advanced in English. My Filemaker Pro 9 Advanced "Interface user language" is in English. But the result of Functions are in French. The main problem in my application is with "DayName (Date)" I would like the names of the days to be in English. But I don't find a way to change this. Thank you if you can help.

Is there a way to make a field conditionally required? For example, can you make field A required to be "not empty" only if field B has a value, but not required to be not empty if field B does not have a value?

I have a Client's Table and a Billable Hours table. On a Client layout, I have a field showing a summary for billable hours for that client. How can I show a summary of a summary? In other words, in the footer, I want to see the total amount of billable hours for the found set. Client Hours (Summary) X 22 Y 20 Z 20 Total 62 How do I create the total row when the Hours are summary fields? Thanks!

I hope the this is posted to the right forum I would like to look at the customer master and select a rate based on a date I have four start and stop dates and four application rates defined. I setup a calc. for the new record as follows. Case (Customers::Cust_AppDate1Start ≥ Oe_PlacementDate and Customers::Cust_AppDate1End ≤ Oe_PlacementDate;1 ; Customers::Cust_AppRate1; Customers::Cust_AppDate2Start ≥ Oe_PlacementDate and Customers::Cust_AppDate2End ≤ Oe_PlacementDate;1 ; Customers::Cust_AppRate2; Customers::Cust_AppDate3Start ≥ Oe_PlacementDate and Customers::Cust_AppDate3End ≤ Oe_PlacementDate;1 ; Customers::Cust_AppRate3; Cus…

i have a field which stores the date of the monday of the week that the record's data is entered. to give a more sequentialness to it, i have another field with the following calculation: Right (Year ( Get (WeekDate) ); 2) & Right ("0" & WeekOfYear ( Get (Weekdate) ); 2) so that i get values like 0801, 0802, 0803, etc. this year, the dates were 12/31/07, 1/7/08, 1/14/08. Question 1: why do the above dates produce 0753, 0802, 0803, skipping 0801? Question 2: How can I create a field that gives me a nice sequential 1,2,3,4,.... so i don't have to figure out how to count from xx53 to xx01 (or xx02 !)? I assume that I could create a calcul…

I am trying to use the list function for the first time. My result is fine, except that it returns return separated values into my portal field (which I believe is what it is supposed to do). What I need is to get each of the the results into their own field. I haven't found any discussion of this, but I could have missed it. Any help is appreciated.

I am checking out dolphincart pro, but I think that may even have more in it than I need. What I am looking to do is to have a page that lists a bunch of products, with a picture of said product, a price point, and a field in which the customer can enter the quantity. At the bottom of the page, there will be an "add to cart" button which should then display a summary of the order, and present them with the payment options. (I am not worried about the payment options right now.) What I am scratching my head trying to figure out, is how to best set-up the database so I can get what I want. I'm attaching a picture of the style I am looking for. DolphinC…

Has anyone been able to use the Get(ActiveSelectionStart)function with FileMaker Pro 9.03? In theory I should be able to have two field (one being the calculation) and another a text field. When I click into the text field and highlight a selection of text I should have a number showing me my start point in the calculation field?

looking to get a total for a field (Expense Amt) when sorted by a field (Accounting Code ="C27"). c27 is the the code text input into the field "Account Code". After I get the format with this, I will do this for Account Code A01-A03, B01-B08, C01-C30, and thru D08-D05. None of the following seem to work. Please advise. If((Accounting Code)="C27"),Sum(Expense Amt) If(Accounting Code = "C27",Sum (Expense Amt),0) If((Accounting Code)="C27",Extend(Expense Amt),0) GetSummary(Expense Amt,(Accounting Code)="C27") Sum (Expense Amt) , If (Accounting Code ="C27")

I have come around an unexpected behaviour: At end of the day all invoices are deleted from the file: The last invoiceNumber will be put into a GlobalField 'gLastInvoiceNumber'. Then all invoices are being deleted. Records = 0, I can see the value in 'gLastInvoiceNumber'. Than close file. Re-open file again: it has lost the value of the GlobalField 'gLastInvoiceNumber'. Any idea why this happen, any workaround? Note: The file is remote on FileMaker server. This behaviour is not happen as local file! Cheers Greg

What function/calculation do I use to get the highest of 3 values from three fields (field1 , field2 or field3) to show in field4.

I want to populate a field of a newly created record in a portal row with the value of the field in the record right above it (with a secondary key). I'm trying (wanting) to do this via an Auto-enterd Calculated Value and I've got my FileMaker 9 Developer Reference cracked open but I just can't figure out a way to make it happen. In the past I've done this with a script that creates a new record in the portal but I in this case I need to have it so that writing in the portal window creates the new portal row record.

Hi there! I've been tossing and turning over this one; I'm convinced it has a relatively easy solution but I can't seem to find it. In my database, I have 2 sets of dates: Start and End, both working with a dropdown calendar on the layout. What I would now like to do is, when the Start date is filled out, set the value of the End dropdown calendar to at least the same date as Start, [color:red]without actually showing that date as a field value! This to avoid that the user thinks he has already filled out the End date. Suggestions, anyone?

I'm having trouble figuring this one out and I wonder if some cleverer brains than mine can help... I've imported a load of Addresses from Outlook, and many of them have two lines combined into one, e.g "1 The StreetThe Town" etc. I can't figure out a way to seperate the data into two lines. It appears that there is always a lower case letter followed by an upper case letter, but I can't find a function to check for the 'case' of text. Am I missing something obvious? Many thanks in advance.

I'm running Server 8 with access for 20 different users. We created an HR database file that contains a summary of job descriptions. For each record (80 toltal records) we use a container field that contains a PDF file of the full job description. The problem is that in the server the link is correct and opens easily. But when called from a network computer, the link does not open and gives the message "Windows Cannot Find the File..." Any suggestions...why does it work in the server but not on the network computers. This is wierd because when you assign an object to the container, its as simple as browse for the file. Appreciate any help I can get...

I need to create a calculation that will allow me to add 5, 2 or 1 years to a review date... but there is a snag. In a nutshell here is what is required... I will get to the snag at the end. A person in this database has a medical category (1,2 or 3) If they are category 1 or 2 and they are under 50 then they must be medically checked a 5 year intervals. If they are between 50 and 60 it must be every 2 years. If they are category 3 after the age of 40 it must be every 5 years. That in its self is causing me some grief but here is the snag. The field I am creating adds years to a review date OK but if a person has a medical at 49 on the 1/1/08 they will…

1 table tblHours 5 Fields: nameUserName : txt field timeIn : timestamp timeOut : timestamp date : date in format 01/01/2008 totalHrsInterval : Auto-Calc Field "Hour (timeOut-timeIn) & ":" & Minute ( timeOut-timeIn )" sample record: jsmith010188; 8/18/2008 11:13:05 AM; 8/18/2008 2:45:11 PM; 8/18/2008; 3:32 I want to Sum the totalHrsInterval fields that exist for a specific date such as Sum(totalHrsInterval {where date = DateCurrent-1} I would like to simply display this on a form without actually having an entirely new table... I thought about a merge field, but I don't think I can calculate in a merge field, can I?