Calculation Engine (Define Fields)

I have a database with about 1800 names, addresses, phone numbers, emails and checkboxes for 8 categories. I would like to create a layout in which the 5 fields devoted to the individual's record are merged into one field so that I can easily search the records when I dont know which field exactly the search word happens to be in. I'm sure this is dead easy but the Filemaker 7 help (for Macintosh) doesnt seem to tell me how to do it.

Hello brilliant minds I need to do a SerialIncrement but I can't find the right way. I have a line item table with those values: dining room master bedroom bedroom kitchen ...and more then in another table, with a pop up value list, I choose rooms from the line item master bedroom kitchen bedroom bedroom bedroom Other fields describe items included in all of those rooms My question is How can I have those bedrooms to increment each time I add a new one? bedroom bedroom 2 bedroom 3 Any idea? Thank you.

I've been trying to develop a case calculation that will stamp an order with one of 4 tax districts depending on the information given in the order. The tax rates are: "Out of State", "State", "County" and "City" I seem to only be able to get 3 districts working at a time, with State and County overriding one another, depending on which line of the calculation I put first. Oddly, if I pull the county calc line line all of the remaining orders (that haven't been claimed/stamped with out of state or city) file under state, though they don't meet the criteria and visa versa. So I know I'm doing something wrong. The best way I've been able to work this out pul…

I was wondering what would be the easiest way to change an existing field that will pop up a small message when certain data is put into it? For example: I have a "customer name" field that if I ever enter a certain customers name, I would like to have a message pop up say "please do not order material for this customer "

I have a column of different dates. Each date is the date on a record of when a subscription to a magazine lapsed. I have a global column showing today's date. I need a formula that will tell me the number of months that have elapsed between the two dates. I am working in Filemaker Pro 5 Can anyone help?

For some reason I am getting a negative number in this field when it should never be >0. Below is the exact calc I am using. I thought it would stop at 0 but getting a negative number. What am I doing wrong? Min ( Max ( 0 ; l_ff_roll2_ft - l_ff_roll2_m_diff) ;l_ff_adult - l_ff_roll1_a - l_c_ff_roll1_adj; )

I have a field that looks up qty based on the selection of another field. It works but when i clear out the item number the qty remains in the lookup field. How can I get this to clear out when nothing is selected?

Is there a way to display in a Field, the name of an imported csv file? I always start with an "empty" data base. I then import a csv file. I want the name of the imported file to display in a field. When I am through "vetting" that csv file, I delete all records and import a new csv file. Now I'd like to see its name displayed in a field. (This is so I won't lose track of the csv file I am vetting.) Is this possible? Thanks for any help on this.

I'm almost done with my checking/billing file, but I have run into one more hitch. Basically this part is a basic check register. There is a Transaction Amount field, which is for the amount of the transaction and a Balance field which shows the current balance. However I have an archive function that deletes transactions over one year old (it's contents get pasted in a text file) Long story there,but that part works great. Forget all of the background on the archiving, though - here's the problem. I have two additional fields - Archive Adj - this is a number field set as a global, the other is Balance Adj. Balance Adj is a calculation that is simply Ar…

Hi all, I'm a novice and am wondering is it smart to create a database with several hundred fields in a single table? What problems could I be facing in the long run? I'd really appreciate any and all opinions on this question. Thanks, Rob

I created a checkbox (called region1)with a bunch of fields "1", "2", "3", and "all". Does anyone know how to create a calculation for the field so that when I click on all,1, 2, and 3 will all be selected.

I have one text field called SERVICES NEEDED with checkboxes indicating what services the user still needs to buy for her wedding: Accessories & Jewelry Alterations & Preservation Bridal Attire Caterer etc.. I was manually converting that text to numbers for submitting the associated codes to customers. Each topic has a number associated with it: Accessories & Jewelry is 2 Alterations & Preservation is 4 Bridal Attire is 11 Caterer is 20 So I was manually typing in 2,4,11,20 I want to create a calculation field to do that automatically. So the new Calculation field would look at SERVICES NEDDED FIELD and create numbe…

Hello, my case statement doesn't evaluate correctly. I don't know if is OK to use the Length( ) to evaluate my Justification time fields please help!!! Case ( Length (End Time) > 0 and Length ( S Justification time ) > 0 ; (End Time) - (S Justification time) ; Length (End Time) > 0 and Length ( E Justification time ) > 0 ; (E Justification time) - (Start Time) ; [color:blue]Length ( S Justification time ) > 0 and Length ( E Justification time > 0) ; (E Justification time) - (S Justification time) ; Length (End Time) > 0 ; (End Time) - (Start Time))

This is my first filemaker experience so bear with me : I want a field to be autofilled with an number. This is what I have. I have a table called system with a field account I have a table called personel with a field account and a field pID I have a table called hours with a field pID the system::account is filled with a name. When a new record is made I want the hours::pID to be filled with the pID from where personel::account = system::account My guess is that I should make a calculation but I just can't figure out how...

New member, possibly naive question: I have a families database for a small school. 2 tables: families, which contains parent contact information, and children, which contains child information, related to families by a unique index field (FamIndex. Children has a field (for each child, obviosuly) called EnrollStatus which can have values 'inquiry', 'accepted', 'enrolled', 'alumni', 'declined,'etc. I would like a calculated field in parents which takes the values: enrolled, if any of the associated children are enrolled, alumni, if any of the children are alumni, prospective, if any are inquiry or accepted, declined, if none of the above are true…

I'd like to create a calculation field that will show the number of records in the found set where another field has the same value.

Since my last "request for comment" thread was so successful and productive, I thought I'd start another. For the ForEach() topic, I had a specific idea to discuss, this time, the only limitation is calculation function ideas that you think would be useful. Please try to limit the ideas to calculations though, I don't want to see a long thread about script triggers. Also, to avoid rehashing what we're already been over, let's assume some sort of iteration concept already exists in the form of ForEach() with a { Range() / Counter() / Enumerate() } helper function. If you are going to make a suggestion, please try to suggest a name, show the arguments with …

I am having a bit of an issue with the following calc: Filter (GetAsNumber(Truncate (Weight / 16 ; 2)) ; "1234567890.") The field Weight is a text field and the result of the calc is set to text. Note that the same problem occurs even if I switch the field types to number and the calc result to number as well. The calc returns .06 when Weight field contains 1 (transforming ounces to pounds). I want it to return 0.06 -> I can format the field to decimal (if I change the result from Text to Number) and it then shows as 0.06 however on export it still shows as .06 Thanks.,

I'm relatively new to FileMaker and what I know comes from playing around and self-teaching with a book. I have never posted on a forum before, so I could be going about this all wrong (please let me know!). Background: I work for a university lab that gives questionnaire-type tests that results in numerical data. I'm trying to create a system so that the same data can be entered by two different people, with a "checking" system to validate that the data entered is the same (to circumvent entry error). So, for one question on one test I have three fields: first entry, second entry, and a "check" field. The "check" field is based on an auto calculation: If (entry…

Hey all, So what I'd like to achieve is have different text to appear based on what is in a certain field. More specifically, I have a data entry page where you can select from a checkbox whether a location has been scouted (yes / no). On a summary overview page I'd like text to appear that indicated the status of the location and if it has been scouted (value: yes) I'd want it to say "Location was scouted" but if it has not (value: no) then it would say "Location has not been scouted". In addition to this, I'm hoping to add the scout date to the first caluclation from a separate field (Scout Date field). So the if the location has been scouted it would say…

Hey:) For a while no I have stuggled to find a reasonable soulution for my active or deactivate record. I want to have the record still listed in my database, but when I search for client the deactivated record will not be searchable. but it should also be an option when a checkbox is ticked "search deactivated records," deactivated records can be found. Does anyone have a good solution??? Thanks soooo much for your help. any hints or tips will be welcomed with a huge smile Kind regards

Hi, I have a field that isn't serialized. I typically want people to manually enter their value into this field. Usually these values are something like W0001, W0002, etc... so if I did serialize it, that is how I would set it up. I am not using this as an indexing Key, it is just the next available item number they would use. They can change this value if they wish, and the database stays connected, because I employ Master Keys. But I have a scenario where they want to click a "new record" button, and it would give them the next available serialized variable, but with a D0001, D0002. So basically, I was thinking of a script that would tack on a "D" and t…

Looking at the below image, is it possible to create a calculation field that will look at Paths from: and Paths to: fields and if they are identical as in the below three records, put the contents of the name field into the calculation field? Therefore the new calculation field would contain 'Crookes Blakemore Fairburn' across all three records. I could create a script to do this, but would be happier if I could achieve the same result with a calculation field. Unfortunately I only have Filemaker 6. Be delighted if someone could help.

I have a repeated field with 80 repetitions, that I am using to create a bar graph for weights. I have a calculation and conditional set so that the bar color is set appropriately based on the weight. Works great, but I want to do one more thing with it and can't figure out how... When I print the graph, I only want the actual number of repetitions or length of the colored portion to print, followed by the weight appended immediately to the right of the bar. Right now, I have the graph printing and it looks good, but I can't place the weight directly at the end of the bar. So it would look like this (R=repetition displayed with color): RRRRRRRRRRRRRRRRRR…

What would be the best way to filter carriage returns out of the field? If I use Filter or FilterValues I would have to type in all of the chars I want to keep in the field and the ones that are not specified will be filtered out - at least that is my understanding. How would I specify it the other way around?

I have a group of text in a field. and also another one field having a single word. My question is: How can in find the line No from the the grop of text for the single word. Help to me find the solution !

Hi everyone, I was wondering if you could help me understand how to achieve something (I am very new to Filemaker). I was wondering if there is a way to perform a count on the number of records that have one value in a value list in a separate field. I don't want to specify the value in the calculation dialog box, though; what I want is for whatever value that appears in the separate field to automatically be searched through all records and then be counted in the specified field. In other words, if the separate field says "Meductic Group," then I want a number to appear in the specified field that represents all the existing records with "Meductic Group" in the sepa…

Is my understanding correct that the container field can only hold one file or is it possible for it to hold multiple files?

I have a couple of questions. I am very new to FMP. I am working in an existing database with several tables. 1. The first challenge is we are looking for instances of an event happening in a date range. So we put in: Case (movedate > Date(1;1;09) and movedate > Date(5;31;09); 1; 0) Then we can find the total of it with a summary Total of. It shows the correct result. The problem is we want to be able to search that field to find the ones who have moved 2 times versus the ones who have moved 1 time. So I am guessing the summary is not the way to do it. What other way could I do this and find a searchable total? 2. We are marking record…

I have done some combing of the forums with no luck so far. I have a count function now that simply adds up all the open calls in a database by a Count (table::field.id) easy Now I want break that down further to giving me a count of all open calls for each employee... I have attempted to put in a conditional comment in calc, and even an if statement, but get errors that this is not allowed. Is there a simple way to do this? Am I making any sense? LOL

I'm sure there is a way to do this but i am drawing a blank. I have a database that contains a Customer Table and Orders Table. On th customers table i have one field for first name, and one field for last name. I also have a button the create a new order that copies over some information about the customer to the orders table. On the Orders table, there is a field for Full Name (not separated as the customers table). Is there a way to copy the first and last name from the customers table first and last name fields and add it to the orders table in the full name field? I know i could wok around this by separating the fields on the orders table but for…

Hi all, I created a script to auto save a record and using a field Date in the filename, it works find with the exception that if the day or month on contains one digit i cant figure out how to add the leading zero. Any ideas would be great. This is the formula I am using on a Variable Set Variable [$Date; Value:Month ( Service Ticket::TicketDate ) & "-" & Day ( Service Ticket::TicketDate ) & "-" & Year ( Service Ticket::TicketDate ) RudyM

Is it possible to have a summary field that is the total of a calculation field that uses GetSummary? I am running a sub-summary report that finds all line items in a date range and sorts them by employee name. The fields are: num_Hours_Worked summary_Total_Hours_Worked calc_num_Overtime_Hours = If ( (GetSummary ( summary_Total_Hours_Worked; text_Name_Full )- 40 ) > 0 ; GetSummary ( summary_Total_Hours_Worked; text_Name_Full ) - 40 ; "") I then have a summary field summary_Total_Overtime_Hours. The sub-summary report, sorted by text_Name_Full correctly totals the overtime hours for all of the line items in the date range for each emplo…

I had been stumbling on this for a few hours now and figured it was time to ask for help. I have a database with 2 tables, Clients and Claims On the Clients layout, there is a portal which is linked to the claims that are associated to that Client. I also have a field on the Client layout for 'Claim Count' which i was hopeing to use to calculate the number of claims the client has. How would i go about calculating the number of items from within the portal? Thanks

Hi all I am looking for a calc or custom function that will reliable extract the seven digit code in a text field that is between two square brackets. eg. field value is My letter to Smith [1234567], I want to be able to extract 1234567 The issue I have is when there is another[ in the field eg. [FWD] My letter to Smith [1234567] The code I have used so far is Let ( [ $StartItem = Case ( Position ( MyField ; "[0" ; 1; 1 ) ; Position ( MyField ; "[0" ; 1; 1 ) ; Position ( MyField ; "[1" ; 1; 1 ) ; Position ( MyField ; "[1" ; 1; 1 ) ; Position ( MyField ; "[2" ; 1; 1 ) ; Position ( MyField ; "[2" ; 1; 1 ) ; Position ( MyField ; "[3" ; 1…

Example: 1234567891234,12345,1234567891235,1234567891236,23456,1234567891237,345678,1234567891238,45678 I need to delete any that are less than 13 dgits so that I have 1234567891234,1234567891235,1234567891236, etc....... I'm not that good with calc's or much for that matter.

Hi, I'm not sure this is the right place to post my question anyway I have a portal that shows the list of the documents. For each document I have a button that links the document to a model. But since the number of documents is quite eleveted I would like to filter them, letting the use to input a string an display only the documents that contain the string within their title. Is there a way to achieve so? I really don't have idea where I should start from. Thanks and have a nice day

First of all, i dont think that this post fits anywhere. Please move it if you find a proper place for it. Well, the subject says it all. Changing the "Show FileMaker Quick Start Screen" on the preferences doesnt do anything. I've trashed the FM 10 and reinstalled it, nothing changes. Any suggestions ? Thanks

Guys, I am having a brain freeze. I'm trying a simple thing as counting the number of characters in a field and displaying it..i've got FM Pro 10 Advanced, ive got a onKeyStroke trigger that fires the script.. the layout has 6-10 fields so i cant name the field, ive tried Length ( Get (ActiveFieldContents) ) ..no good Length ( GetAsText (ActiveFieldContents) ) ..no good and then assigning the value to could someone tell what im missing here... update: ok i solved that part...it was getting it correctly, i was using Set field to display it...changed it to InsertCalculatedResult and that works...but now i have another issue…

Is there a way to set a number field to always be equal to the value of the same field from the previous record..... plus an increment of 1? What I have in mind is something that produces a similar result to an auto-enter serial number. What I want to be able to do is delete a record in the middle of a list and have the number value for the next record adjust itself automatically to be equal to the new "previous" record. I hope this makes sense. Any ideas?

brain fart... I know I am missing something easy. I have a solution that comes up with a code based on the number of fields that are populated. easy enough with count function. My problem is that some of the criteria dictate that you can only count if 2 fields are both populated. count (field A; field B; field C & field D) i need to count 1 if field C and D are BOTH populated not either. is this possible without a case func?

I hope this is the correct section to post this in, sorry if it is not. This is my first post on these forums. Here's my situation: I have to separate databases, one which contains a listing of all possible products([color:green]Products), the other is for a product assortment([color:orange]Assortment). The [color:green]Products database consists of numerous records of individual items, their [color:purple]item#, price, and a product image. Examples of [color:purple]item #s are: 0001, 0002, 0003, 0004, 0005, etc... [color:orange]Assortment consists of a table with four item fields ([color:green]Item1, [color:green]Item2, [color:green]Item3, [color:gre…

I am working on a database where I have two related tables The main table contains my records which may have a number of variant names held in the related table e.g. Record1 with related values Variant_name3 / Variant_name1 / Variant_name2. Not all main records have variant names. Some records will have only one variant, while others have multiple related names (as above). I want to concatenate the related record strings into a single text string held with Record1 which would read “Variant_name1, Variant_name2, Variant_name3” I’d like them in alphabetical order with commas behind each string (but not the last one). I’m not sure where to start apa…

I hope someone can help me with this. I created a colored bar graph using a repeated field (with 400 repetitions to represent 400 days.) I have a condition setup so that the color will be green for the 1st 300 days, then yellow for the next 40 days and then red. I use the values 0, 1 and 2 such that a value of 0 will condition green, 1 for yellow and 2 for red. The text color is set the same as the background so the values are not displayed, just the color. I have a script that will set the fields based on another field Days (which is a calc field, using the current date and a user-entered date.) The script looks like this: Set Variable $repetit…

Hello guys, I have 2 fields: edition_date and expiration_date; how can I validate expiration_date to be later than edition_date? When I select "options" -> validation by calculation I get Validation for field "expiration_date" = and if I try to put > edition_date I get an error. Thanks and have a nice day

Hi, So I've got a portal on my layout that has two fields in it: "Fiscal Year" and "Amount". There can be any number of records in this portal and I want to be able to calculate two values; "5 year target" and 20 year target". Both these calculations sum the "amount" fields in the related portal for the next 5 or 20 years. The current fiscal year is in a global field in the database so that the user can do historic reporting. Currently the "Fiscal Year" field is formatted "****/**" but I can change that if I need to. I'm at a loss on how to do all this. Any help? Thanks.

I just read about optimizing calculations in FMP 9 bible and I thought I'd give it a try. I have a Full Name calculated field, and my boss wants the text of that field to be red if the contact is a current member (we are a private club). Doing this slowed Finds made within that field considerably. I tried to optimize the field, and to my surprise searches slowed even more. Here's the before and after code. What can I do to speed up this field? Before: Let( fullName=TextFormatRemove( If(Contacts::Last Name ≠ "";Contacts::Last Name & ",") &If(Contacts::Salutation ≠ "";" "& "("&Contacts::Salutation&")") & " " & Contacts::Firs…

Hello every one I am still new to file maker and I really believe its possible to do great stuffs. I am in the middle of a program. Its suppose to have tables for products, patients and invoices. I made a form for the patients, where their medical diagnosis can be tallied up with their financial history. But I had to delete a product from the products table everytime I made a sale for the patient. Example. I have 25 pcs of a particular drug in the store, if i record 4 pcs for a patient, how will I deduct that from the drugs table dynamically. Please bear in mind. the drugs table has it own form so I can update a quantity of drug recently bought into the shop. I need to kn…

Hello everyone. I am working on my first filemaker database using some of the more advanced functions. I have had good luck finding answers via google so far, but I can't find anything for this because the search terms are so generic! Sorry for the noob question, any info or links are appreciated. I am setting up an employee classifieds database. One of the restrictions is they are limited to 5 ads per month. I would like to calculate the number of times the emp_number from the current record appears in the rest of the database. A simple task but I just don't know where to start. Thanks!

Hi there, I've been scouring the forums and racking my brain to try and find a solution for the following problem: I'm looking to be able to compare rates on a year on year basis down to the day and or week. To enter the data in a user friendly format I have 3 fields; a start date, end date and rate that is applicable for the period. For example: 01/11/09 - 04/11/09 $400 05/11/09 - 22/11/09 $450 23/11/09 - 01/12/09 $400 The above is an example of some rates for 2009 and below is an example of the rates from 2010 that I'd like to compare: 01/11/10 - 03/11/10 $440 04/11/10 - 20/11/10 $450 21/11/10 - 29/11/10 $440 As you can see the rat…

I'm close. I've almost got it. But there's still one glitch I haven't figured out. I'd be forever grateful to someone for clueing me in on what I'm doing wrong. I'm tracking students' ethnicity. There are 10 codes — AI/AN (American Indian/Alaskan Native), AS (Asian American), B (African American American/Black), HA (Hispanic), W (White) and several more but these are enough to illustrate my problem. There are many students with mixed ethnicity, i.e. Hispanic and Black. I track them as M (Multiple) and have a field called EthnicCodeDetails where I list the codes for their specific backgrounds. This student would be listed as ethnicity M and the EthnicCodeDetail…

Can anyone provide an age calculation code for an FMP novice??? The way my mind works, I would use four(4)number fields: BirthYear, CurrentYear, BirthMonth and CurrentMonth. Find the DIFFERENCE between the related fields and voila... # of years & # months Just as people say, "I'm 29 years and 6 months." Well, I've tried all the permutations using my 4 fields but... Question 1: Is there a possible calculation code that would follow my mind's reasoning path : (Using the 4 fields) Question 2. How would FMP do this calculation : Specifically.. what fields do I need to create, what operators, and how do I write the formula for FMP's calculation editor …

Hi all - I have a situation where i'm trying to get a field to change color (from red to green) if and only if 11 other fields are filled in with a value. In other words, if the user enters data in 9 out of the 11 fields, the field would not change color until all 11 fields had some value in them. Not sure if it matters, but 10 of the fields are radio buttons and 1 is a text field. And they only need a value selected (or entered in the case of the test field) - it doesn't matter what that value is. In lieu of not being able to change a field color, I could alternatively have the field show the text "***INCOMPLETE***" in RED when some or all of the fields are n…

Hello all. I have two fields Ordernumber and line number. I want to join them together to become one number but its a bit more special than that. order:5082544 line:050 what i want =0005082544000050 order:5082544 line:500 what i want =0005082544000500 order:5082544 line:5000 what i want =0005082544005000 Although not a great way of doing it I was doing this with some if statemants depending on the length on the ordernumber but something tells me this is not the best way of doing it. Any thoughts. Jord

How can I get a list of Table names in FM v10? The current TableNames and TableIDs functions do not work. Instead of returning TableNames and IDs these two functions return RelationshipNames and RelationshipIDs. Tables and relationships are two very different things (tables are a storage structure, relationships are just searches). All I want to do is generate the same list of 'Tables' that appears in the Tables tab of the 'File:Manage:Database' dialog.

I have upgraded from FM 9 to FM Pro 10, in a file I have 7 columns, one of which is notes, I would like to color the text in notes to red, in some of the files but not all, when I highlight the text to change the color in the formatting bar box, as long as that box is highlighted the color text shows red, if I click in another field the text shows up as black. The back ground is white. Can someone offer some help

I hope you guys will be able to help me. I have a field that takes dates only. In this date field I have set the conditional formatting to turn the field red when more than one day has passed beyond the date entered into the field. Is there a way for me to determine the percentage of time the field goes past the posted date? I know it sounds a little strange but I am trying to create an analysis report that shows how often project go beyond the completion date entered into the date field. You help is great appreciated. Cheers, Kasei

Hi everyone newbie here. I have a value list that has approx. 40 entries in it. What I want to do is a calculated field that is a summary of what they picked in the value list. For example say may value list has 1) Red 2) Blue 3) Green The user picks red and blue Then I want the calculated field to say Red,Blue All the data is stored in one field called color but my calculated field is called ct_color. The formula currently looks like this color & "," & color. However this is not working. Thanks for the help

Hi, My app needs to automatically calculate the number of sheets of paper a print job will need so the requesting department can be charged accurately. Most of my calculations work fine, but if the original document has an odd number of sheets, and the job requires going from 1-sided copies to 2-sided, I end up with an odd number of sides, and there's no such thing as a one-sided sheet of paper (Moebius strips not withstanding). Example: Original has 3 single-sided sheets and the job wants them converted to double-sided. 3 / 2 = 1.5 sheets, which means 3 sides will be used. Since the user enters the number of original sheets on their job ticket, how ca…

Hello I am a brand new user and I don't speak english very well. so bare with me. I am creating a database for Employee Service and I am having trouble getting my head around the calculation concept. If any of you could help out I would really appreciate it. I have these Field DOB ( Date of Birthday ) DOJ ( date of Join ) EMP_Serv = calculation EMP_Ret = calculation and ofcourse Current date How to find the date of service( from current date ) if the Employee completed ( 30 ) years of servece. And how to find the date of Employee Retirement ( from current date ) ( if he reach 55 years ) Which will come first when he completed 30 yea…

I've used the > symbol in calculations to identify fields that have information, as in: Case(Home Address > 0; etc., but I've found that if the field starts with a parenthesis, it doesn't see that as greater than 0. Is there a simple way around this? Steve

I have an Average of summary field that more often than not doesn't calculate. I suspect it is because the field it is summarizing is a combination of a lookup (allow override) and a calculation. Any ideas on how to get the summary to always calculate would be greatly appreciated.

Hi, I would like to say that I only have 2 days playing around with FM, and this is my situation. I want to know if there is a way that i can type in a field a formula like this =(8*2)+2+2.5+10 and get the result either in the same field or another field to be used in a "Qty total field" for a Square Feet count. Te reason for this is that we walk the site with a notebook taking notes of how many SqFt the workers did on a week to generate their payroll, I have this table setup in Excel with VLOOKUP for Price per Unit and everything, thanks to this forum i already have tat part of my payroll working (using an invoice based solution), but the part that I want to av…

Hi everyone. Need help trying to figure out the best way to create and lay out a window that allows the administrator to search on a particular zip code and sale date of a list of homes and have it show various statistics for each entity who has work to do on those properties. I have several entities who work on each property. Miners, Drivers and evaluators. There are 5 Miners, 10 drivers and 10 evaluators. I have three fields across each property record - a text field for MINER, DRIVER and EVAL where each record is assigned to one of each (in no particular order). In other words, the MINER field for property record 1 might be assigned to MINER01, DRIVER…

Hi All, FM10A OSx.5.7 A calc evaluates an (wage) amount against a list of (tax threshold) values and should return the value list row (rate) number. input into g_amt: 100 returns 1 300 returns 1 301 returns 2 400 returns 2 500 returns 3 600 returns 3 900 returns 3 then it all goes wrong... 901 returns blank 1000 return 2 substituting A3, A4 etc for RATE, returns the correct values, that shows the case argument rows are evaluating the correct values, and the pattern looks correct and logical in the case statement; but a blind spot has developed... Illumination would be appreciated. ---------- The g_list of values: 300 500…

Hi I think this is the area I should be posting this in but if not I will appologise in advance. I am new to Filemaker and trying to get my feet, I have gone through some tutorials online and started to build my first "solution" What I want to achive is a layout that shows how many records a user has created on that day. I have created six layouts each one is unrelated and has a a few fields of data we want to capture. I am also auto entering the username(account) the created and modified date. So basicaly what im trying to achieve is a front page that count records where the :username and the ::created_date match get (AccountName) and todays date…

Hello, I need to create an invoice that, when a specific customer is selected, the price field will display pre-defined prices that are set for each individual customer. I found a sample invoice solution in this forum which I will use to help pose my question. Please refer to http://fmforums.com/forum/showpost.php?post/309136/ Using this example, I'd like to have the following happen: Adam should pay $1 per "Alpha", Betty $2 per "Alpha", Cecil $3 per "Alpha", David $4 per "Alpha" and Eve $5 per "Alpha". Ideally when the customer is selected and an "Alpha" product is added to their invoice, the price would automatically change to reflect their individual s…

HI everyone. I have a field which is user-controlled by radio buttons. I'm currently looking to create a calcumation field that tells me, out of all the possible text combinations, how many occurrences of the word "MINERS" are selected. I've been on another 32 hr FileMaker bender and I'm brain dead. Any suggestions for how to make this happen?

I have just tracked down spurious behaviour in my database to the MiddleValues function. Using it to extract a single value out of a list results in the single value plus a trailing return character. Whilst this can be removed with substitute function I was wondering if this is deliberate behaviour of MiddleValues as it does not seem consistent with the function description. Can anyone advise me? Regards Jim

So this calculation "Year ( Get ( CurrentDate ) ) & Month ( Get ( CurrentDate ) ) & Day ( Get ( CurrentDate ) )" for today June 6 2009 returns 2009610. Is there an easy way to make it return 20090610? I also like to have the leading zero on the day too.

I am running into some difficulty trying to produce monthly sales reports. I basically have a report that shows monthly sales for each vender and then groups these sales by trailerID. For example if in the month of May vender A had sales from 3 trailers, the report would have 3 separate sub-summery parts showing gross sales and net sales for each trailer followed by a grand total of sales from all 3 trailers. I need to be able to store the gross and net sales value for each trailer to use in another report. I have tried using a Set Field script step and also a GetSummery() function without much luck. Any good ideas as to how I might accomplish this simple task…

I am trying to populate an order table with data from different product tables. I have multiple Product tables, each has it’s own complex set of attributes and associated tables. What are my options for bringing them together into an OrderLineItem table?

I have several clients with whom I have annual contracts. I give them a database which they have to login to use. I want to be able to put a time limit on their ability to login. In other words if their contract expired at the end of July and they decided not to renew, then after 1 month their login would be de-activated. I am using FM 8.5 advanced. I tried the following script but it immedietly de-activates them. I have tried changing the date format mm/dd/yyyy but still the same result. Can you advise? Many thanks, Dominic If [(Get CurrentDate) > 01/09/2009 Go to Layout ["Startup" (Assets)] Show/Hide Status Area [Hide] Beep Show Custom Dialog ["Databas…

This should be an easy one for someone hopefully. I am looking for a calculation that will count the number of unique names that meet a single criteria. Unique because there are several records that have the same name in the name field and I only want this to be counted once. The criteria I want to set is the persons grade, therefore this calculation would give me the number of people I have working in each grade. Unfortunately I only have FM6 which doesn't have some of the functions available in later versions.

I have a number of records that were originally imported from text files and have kept the "" on the text. I am looking for a way to remove the leading and trailing quotes" from about 18 fields and about 30,000 records. I have contemplated just removing them all - but I have a lot of measurements (1-1/2" etc.) that I would have to replace after. Any ideas short of pulling all the data to excel and editing each record?

I apologize if this is not the right place but I have users accesing my DB via IWP - unfortunately the enter/return key does not function the same way for them as it does for me. Here's what I need help with - I have a field that only accepts a 7 digit SKU - when they hit enter it creates a new line. Is it possible to set up a calculation for that field so that when it has 7 digits entered- it could trigger the script that my button currently does? I would only need it to work in "Find mode"

how can i have commands like TRIM, UPPERCASE used on a field so that it will display correctly, and export correctly? I'm not exactly sure how to make this happen. Ideally it would occur after a record was modified.

I have a date field that is set to auto enter the modification date. However I have another field that I would like to modify that won't automatically enter the modification date in the former field. So, I'm happy for the modification date to be updated whenever a field is modified except just this one field. Can this be done?

Hi, I'd like to merge records into one field for reporting. I have a join table where 1 patient is linked to several procedures. These procedures have a code. For reporting i'd like to merge these codes in one field. I've attached a sample file. Can somebody help me on how to do this. I tried a list function but can't get it to work. Thanks vraag.fp7_.zip

What would be the easiest way to add | after each word in a field? The number of words will change time to time. I am looking either for the auto-enter calc that will do this or an output into a different field would also be ok. Basically it is for keywords. The field will look something like blue big bird fuzzy stuffed animal After that is put into field I need to get this: blue|big|bird|fuzzy|stuffed|animal Not sure where to start, any help is appreciated. Thank you kindly.,

Hello, I'm trying to calculate the number of years and months between today's date, and a date provided ("MyDate"). Here's what I've got so far: Get(CurrentDate) - MyDate This returns me the number of days between the two dates. But when I try to do Year() of this statement, it doesn't get me the correct number of years (it's one too many). Hmm. What am I missing?

Hello everyone, been a while since I had to ask a question, but I now have one. We are working on a layout showing the feeding days of our K9s. Here is what I have tried without any luck as it returns a count for every feeding entry not just the one for this month and this year: If( Date_Of_Feeding =( Get (CurrentDate ) ); Get ( FoundCount )) What I am trying to do is get a total for each month for the current year. DB Screenshot Can you point me in the right direction. Thanks again, Jim

The attached file demonstrates that the Extend function does not work when using auto enter on a field. It works fine with a calculation, but not with auto enter. In the sample file the you can enter data in the Qty and Tax fields - just look at what happens. Any one got any ideas? EXTEND_BUG.fp7.zip

Hi all, Does anyone know how I can get the name of the file stored in a container, without using a hard-coded field name? This was my idea (below), but it only returns a "?" Evaluate( GetAsText( Left(Get (LayoutTableName); 3) & " • File1" ) ) The table I'm working in is named T2B • Artworks, the container field object name is T2B • File1 Any ideas? I'm trying to have a layout independent script to import/export container field content, which I can use on any layout in any table simply by adjusting the object names of the container fields. Any help much appreciated!!

Hi all, My first request for help would be to see if it was possible to script the opening of a file contained in a container without having to first save the file, but I am now starting to believe that this is impossible and the file must be saved locally first. So my next question. I can setup the script to export the field contents - I do not want the user to see the save as dialog box or even know they are saving the document, i want them to click a button and the file opens seamlessly. So, in the script I set the file path to c:/windows/temp/ (this solution will only ever be used on PCs) and I set to automatcially open the file after saving. But i will …

Hello Mavens, What is wrong with this function: Let([ Case ( PatternCount ( "I" ; rx_med_SIG ) ; x = 1; PatternCount ( "II" ; rx_med_SIG ) ; x = 2; PatternCount ( "III" ; rx_med_SIG ) ; x = 3; x = "" ); Case ( PatternCount ( "QD" ; rx_med_SIG ) ; y = 1; PatternCount ( "QHS" ; rx_med_SIG ) ; y = 1; PatternCount ( "BID" ; rx_med_SIG ) ; y = 2; PatternCount ( "TID" ; rx_med_SIG ) ; y = 3; PatternCount ( "QID" ; rx_med_SIG ) ; y = 4; y = "" ) ]; x * y ) FM complains at the first "(" after the first "Case" that it needs a "number, text constnat, field name or "(" is expected her…

Hi, Does any one know how to add a webcam to filemaker? just a simple snapshot to be added automaticly into a container field for our customers. Is this possible? Thank you.

I'm not sure this is even possible but I'm hoping for some different direction if it's not. I have written a calculation using Case to add days to a due date based on the type of job they request: Case (project_eval="New";request_date+28;project_eval="Edit"; request_date+21;project_eval="Reprint"; request_date+18;project_eval="Signage";request_date+28) This works great as is but I also need to add in 2 other parameter to consider after that: Case(review="Yes";request_date+14) And then: Case (Pages="2";request_date+15;Pages="4";request_date+20;Pages="8";request_date+25;Pages="12";request_date+30;Pages="16+";request_date+35) So if a person has …

Is there a way to automatically populate a Timestamp field with a default time value? We're using Timestamps to be consistent with an external SQL database. But our users actually just want to enter a date. Is there a simple way to populate the field with " 23:59" so the user doesn't have to? Many thanks in advance, William

If I receive an error that a record is being modified by another User (201 or 301 etc), is there a way that I can find out who that User is? UPDATE: I mean ... FileMaker knows another User is modifying the record. And I would bet that FileMaker also knows WHO is modifying it. That's what I want - what FileMaker knows. DOUBLE-UPDATE: I realize I can use the Modified By (auto-enter). But that will only set after the commit and not during the modification itself. After the commit, I could gain possession of the record myself. But sometimes Users modify and LEAVE their cursor in a record's field. And I think FileMaker knows exactly who is doing it, too!

I'm trying to reformat a time field based on the following situation: users enter time in a 24 hour format (2300). The field is formatted to leave as entered. I would however like to have the fields insert current time via script, however obviously when the script runs it would enter this is 11:00:00PM. my question is how can I reformat this time field via calculation to eliminate the separator and the AM/PM. One issue I am having also is that when for instance it is 0700 the hour is one character instead of two which makes the hour part of the calculation either 1 or 2 charcters depending on the time of day. Any help would be greatly appreciated. Thanks, Ron

Hello everyone, I know that there is probably a very simple way to do this, but my knowledge of scripting is very limited, so I'm hoping perhaps someone can help me out. I'm setting up a database that will contain a list of appraisers in one table and a list of loans in another. The appraisers will be approved to work in various counties - a one to many relationship. The appraisers will also be assigned to various jobs - again a one to many relationship. What I'd like to be able to do is enter a job, and have the database basically say: "Okay, the loan is in Franklin County. The following appraisers can work in Franklin County. The next appraise…

Hi all I am trying to get the total number of pages to display on our bill of material program. I have create a calculation field called page_number that use the function Get(pagenumber) and then create a summary field called total_pages and chose the "Maximum function" and chose the field page_number and under Summarize Repetition I chose "All together". Reading the function for Maximun in the FM help to my understanding that this should work. But the total_pages field displays the number 0 in the preview and the print Mode. Should this work or am I not reading the help file correct and If I am not reading the help file correct would you please direct me in the…

Hello All, I seem to be having a little trouble with the syntax for a case statement. I am looking to set a field as follows; Tickets::status_paid= If Tickets::status="Billing"; "Unpaid" If Tickets::status="Paid Billing" or Tickets::status="Collected"; "Paid" If Tickets::status="Unavailable"; "?" Else Tickets::status I am confused as to where all the brakets and parens go so I keep getting an error when I try to set the calculation. Any help would be greatly appreciated.

Hi, does someone know how i would separate an address field that is currently entered as "1234/100 Smith Street" =(owner_address) into "1234" as (Owner_unit), "100" (owner_street_number) "smith" as (owner_street) I am a newbie with basic knowledge. I am guessing i need to create 3 new fields with the formulas to automatically pull them from the one field. Thank you in advance, any help would be much appreciated.

Does anyone know of a way to obtain the attributes of files? I am attempting to display the Date/Time Created & Date/Time Modified Information of files that I have linked to. Thanks, Bill

Has anyone looked into this idea? http://fmforums.com/forum/showtopic.php?tid/199653/post/310461/#310461 I'd like to be able to catch who's modifying the data and post it with a script without giving the account name. Thoughts?

Hi all, I'm seeing what I consider unexpected behavior from the List ( ) function in a script step (Set Field) and I was wondering if I'm missing something. Here's the scenario: I'm trying to create a list of values concatenated from 2 different fields from 2 different related TOs. The formula is List (TableA::field1 ; TableB::field2 ). I've verified that the layout I'm on at the time of the script step is based on a valid TO with correct relationships to the related TOs. On Mac, and some Windows machines it resolves exactly as I want: field1Value1 field1Value2 field1Value3 field2Value1 field2Value2 field2Value3 But on my client's Win…

Hi ! How would it be possible to set "constant values" for every user ? I mean I want to set come "constants" (for example values for alarms) that are same for every user regardless they are using FM Client or IWP. If I change a value of a global field from "15" to "5", next time I log in it is again "15". It seems that these Global fields are per session, not "real constants".

Hi - I need to delete the text part of a field eg "Y2.20.00 room name" to become "Y2.20.00" Is this possible? To complicate things the order might change and the number of words vary. eg "another room name Y3.30" Sometimes either the name or number is inside brackets cheers