Calculation Engine (Define Fields)

I want to count a specified number of fields that are non-blank on a record by record basis but there are upwards of 100 fields I want to count and I dont want to enter them all in a count calculation (they have arcane names and periodically new names are being added). In this case they are the names of photosynthetic pigments. Because these and only these fields are prefaced with "Z-" I thought I might be able to use something like the following where "CCLM-Pigments is the File name, "Major pigments" is the layout name, "Z_" is the prefix and "*" is wildcard for rest of pigment name. Count ( Case ( FieldNames ( "CCLM_Pigments"; "Major pigments") ) = "Z_"* ) or…

Hi everyone, is their a way to do a price range lookup for invoicing. For example: If i sell coffee cups if a customer buys: 12-20 it would cost 5.99 each 21-30 it would cost 4.99 each So if i put in the Qty field: 13 it would give the range cost between 12-20(5.99). If i put in the Qty field: 23(4.99). You probably understand what i am trying to accomplish. Thanks again. Jay.

i'm looking for a little help with a calculation. I'm sure this is a breeze for most of you. but i have a very limited knowledge of the calculation language. ok here is is. I want to calculate total hours paid when up to 8 hours is straight pay 8 to 12 hours is x1.5 pay 12 to 14 hours is x2.0 pay thanks

FileMaker Pro 11adv Knowledge Base offers the following calculation to convert a number into words, such as used to write checks. I've followed the instructions, but no luck. If I put the whole calculation in a calculation:text field, error tells me it can't find the function CalculationField. I then tried to build a Custom Function CalculationField (text result) but it gets hung up on the second comma. Help please ================================ Converting Numbers to Words or Numbers to Text Question How do you convert numbers to words or numbers to text? Answer The following calculation can be used to convert the contents of a …

I have the following search string: Paragraph-Tab-Tab-Space-Paragraph-Paragraph. I've tried using the "¶" and Option-Tab and then a standard space, as well as Option-Space, in different combinations, but they all fail. Can someone direct me as to how to do this?

I'm trying to track inventory count on a daily basis. What I want is a table where I put the date in one field and the next field calculates the sum of total inventory count from that day. Formula wise, I've come up with this: Inventory for date X = quantities shipped after date X + quantities with no ship dates (still in inventory) I have a summary field in the inventory that totals all the quantities together. Now I just have to have it show me only the quantities based on the formula I have above. Date X would be entered into a date field on a new table and the SumForDateX would be a calculation of the total sum based on that formula.

What's the formula you use to find all records within the last two weeks? //-14 doesn't work.

I don't know if I'm having a major brain fart today, but I can't figure this out. What I have: Case( PatternCount( Type; "Receipt") = 0; Expense) What I need is if the Type = "Receipt" OR "Mileage" to return 0 (False).... but I can't seem to figure it out Can someone point me in the right direction? -Thanks -VX

What is considered the best practice for locking a specific field on every record after being edited once? (But also have a NotEmpty AND Unique validation option) In other words, I have a field which needs to be filled in every record, be unique and once validated not being accessible. I suspect that setting another field to 1/0 by a script is a potential vulnerability if someone can access it, isn't it? I was thinking of employing a OnRecordCommit script trigger to prevent commiting before editing once, and then comparing creation and modification timestamps. What do you think? I still think however, that if one gets access to the fields from another l…

I have to do data entry using a lot of times. I'd like to use jut the number pad for everything. Is there a way I can type in 900 and have it be 9:00 AM? or maybe do everything in military time? 0900 would be 9:00 AM, 1300 would be 1:00 PM, etc?

More of a question than a problem. When a record is created in Filemaker does it store it's own internal Creation Date and if so, can this Date be accessed? I'm rehashing an old DB and at the time it was made, there was no Auto Creation Date field entered for each Record, I know I can create one now, but is there any way to get the date out of Filemaker if it actually stores one? Many Thanks

Hello, Is there a proper way to get the container type field to work in the web interface of filemaker? Also, I've tried it with several files and it works peachy, however office files such as .doc / .xls have the tendancy to open inside of the box making them unreadable, when you right click you can open them properly, but I was wondering if there was a way around this? (a .msg file [office 2K10] open in outlook .. which is great) Thanks in advance !

I don't have very much experience in parsing text. I have a field that contains a set of set of values that are separated by the "|" character. I would like to be able to pull the data held within the pipes for a specific position but I don't know how to do that. Say I have the following value in a text field; Train|Bus|Car|Plane how would I go about making a calc that pulls Car from that data set?

hard one to ask but there might be a simple answer (I hope) Setup: FM Pro 8 Is there a way to make a filed have a date fill in automatically when a number gets entered into that a specific field? I know how to do this by scrip by that is very cumbersome, is there a way to do this by calc?

Can Filemaker 8,9,10 or 11 calculate accrued interest at any point in time given a start date and an end date? Or should I just use Quick books?

Is their a way to count records in a found set? jay.

I am looking for a calculation to use in conjuction with replace field contents to remove all leading zeros from a number in a field? Thanks

Greetings; I am trying to get it to display a picture from a field. Image_URL I am playing around with the following code but it isnt showing the picture. Case ( Wholesale Name = "****";"imagewin://" & Image_location3; Wholesale Name = "****";"imagewin://" & Image_location2; "imagewin://" & Image_location1)

Hi everyone, First, I want to thank you all for your help, for the last year or so you did answer everything even before asking. I also want to add add that I've searched the whole forum(and the whole google) for this answer without luck, and hope is the right topic to post it. Basically I'm have a table called projects, with a fiel date for the start and field date for the finish. I wish the finish date to display the current date up until the moment that the project is finished, when somehow users should stamp the date (manually or button). The problem comes cause users should be able to introduce dates in the past (therefore stamp current or look up current w…

I want to create a video database with a video preview from clips hosted at BrightCove. Is this possible using Filemaker?

Hey all, I have 2 tables. Producer and Inventory. The tables have a relationship with each other using the field named "XSAN Number" The inventory table has a field named UPCs. So I have a bunch of records with UPCs that I want copied to one field in the producers table. I want to create a field in the producer table that continually gets updated every time a new UPC gets entered in the Inventory. So the producer table can locate the XSAN Number they want and it displays all the UPCs for said XSAN Number in a field. (Combined UPCs?) Thanks a ton!

Hello, I am looking for help with what I thought would be a fairly simple text formatting calculation. I have a text field (originalGenus) that typically has italics applied to it. I have now come across some cases where I need the italics to be removed from certain characters within the field. For example, typically the field is populated with the text "Genus". In some cases, it contains "Genus (Subgenus)". Now, I need both, genus and subgenus to be italicized, but not the brackets. This field is subsequently used in other calculation fields to format a complicated output used for a taxonomic catalogue. I was hoping this would work, but it …

When a user adds a note to our database, a new window opens with fields to add a note. This design is so the user can read information from the client's record while entering a note. There are times where the note-entry window fall's behind the client's record window. I see there is a function of Get(WindowVisible) that will return a number if it is visible or not but i was wondering if there was a function that would make a window visible. What i would like to do is when a user starts a new note, check to see if there is a note-entry window already open and bring it to the foreground so they can save their note before starting another. Hope this makes…

I need to serialize records by category. My database tracks takes for film production. There are many takes on a camera roll and many camera rolls within the table. The takes on a camera roll were shot in a specific order. The takes were shot on video, so they have timecode which we can use to determine the sort order. I need to attribute new "Ink Code" numbers (which are similiar to KeyKode or timecode if you're familiar with film production) to each take. Ink Code numbers count in feet and frames and look like this: 0000+00 The first four digits count the feet. The last two digits after the "+" count the frames. There are 16 frames per…

So I have 1 field with an identifying number on 100 records. On all of those records they identifying number is the same. '00590' Also on those records I have a UPC field. I want to create a new layout that has 2 fields. 1 field I type in the identifying number '00590' I then want to display all the UPCs for records that match '00590' So all I do is in the layout, enter in '00590' and it displays all of 100 UPCs. How can I do this?

Hi, First please accept my apologies if this has been asked before, but I can't seem to find an answer to my specific problem. Within my database if have a "Jobs" table and a "Tasks" table. When a user inputs a Task, they select a job name, and cost, and the task gets sorted by ID into a portal in the relevant Jobs record. What I'd like to be able to do, is to calculate the total cost of all tasks completed for a particular Job and display the result in a field on the Jobs layout. Is this possible? I have attached a erd of my tables. Hope this all makes sense, Best wishes and thanks

Hello. I'm recently back to FileMaker and new to the forum, so thanks for any help. I'm trying to do something that I'm sure is fairly simple, but can not seem to wrap my aging brain around it. I want to toggle a the text in a field between two different type styles--plain and bold, for example. I can set button options for the field and use the TextStyleAdd function to change the text to bold, but I'm not sure how to change it back (without setting up a second TextStyleRemove button). I thought I'd use a simple If...Then calculation to change it. Essentially, one that did something like this: Step 1: If (the text is plain) Step 2: Then …

How does one go about deciding when to use a global field as opposed to a global variable? Sorry... not sure where to ask this one.

I am very new to filemaker and have been having some trouble coming up with a good way to keep track of money owed and paid by clients in out database. I hope I have this in the right spot as it deals mostly with calculation and summary fields. Here is some background on the database. I have three tables (Customers, Jobs, and Accounts Receivable). Customers has a one to many relationship with jobs and jobs has a one to many relationship with accounts receivable These are the fields I have for Accounts Receivable. CustomerID JobID JobPrice PaymentStatus PaymentDate1 PaymentDate2 PaymentType1 PaymentType2 CheckNumber1 CheckNumber2 Pa…

Hi there good people I have been a casual and basic filemaker user for a number of years, starting with the simple form that was included with ClarisWorks all those years ago. I recently decided to challenge myself to write a database to help easily decipher and record manufacturing codes for a hobby I am involved with - Vintage BMX Bikes. To say the least I have stumped myself well and truly and am looking for some assistance. Here is my dilemma: I know for certain the meaning of the sequence of the numbers and letters in the stamped serial number but I can't get my head around transforming it to a database! EG: Bike frame stamped 9143D …

I am evaluating Filemaker for a project. I need to know when will the likes of a sum function start to slow things down on a report. If I have a reports with up to 30,000 sale lines in a year this would require several calculations to summarise each of these eg sum of qty, Price, Tax etc. what is performance going to be like? If these numbers start to rise, what will happen to performance then?

Fields: Date, Tax Year Please note dates are d/m/y as I am in UK. I want Tax Year entry to be automatic depending on Date, e.g. Tax Year '2009-2010' would apply to all records dated 6-4-2009 to 5-4-2010. When I tried a Calc it did not work. I then experimented with a script using single dates instead of a range and found that my Calc (Date < "5/4/2009") is merely operating on the first digit and therefore triggers dates starting with a digit below 5 and ignores month and year, e.g. ignores 6/4/2008, 7/6/2010 but triggers 13/4/2008 and 3/5/2010. I tried a simple find for the range of dates and this worked. I have searched but cannot see what I need …

Forgive me my poor English as I live in Holland. I have a question: Is it possible to show the result of a calculution as text. example: if the result of a calculation is < 12 the result shown has to be "Mini". If the result of a calculation is 12 or 13 the result shown has to be "C-jeugd". Can this be done in Filemaker. Thanks!

I've tried searching the forums and the help file for an answer to this but haven't found one. I have a script which I use to calculate the age of an unpaid invoice that includes the formula: DaysOpen = Get(CurrentDate) - InvoiceDate None of the data is Stored It works fine, except that the number of days doesn't update accurately if the file has been open for more than a day. If I close the file, reopen it and run the script, the number of days changes. Is there some way to force the CurrentDate to update without closing the file, or is there a better way to format the calculation? Thanks.

I'm tracking work orders by using an auto enter serial number. Each time we add a new project a new work order number is generated. We are using the work order numbers to track work assignments in the field and track our work with a paper trail. Now that the year is closing out I need a solution to the following. Can I set the numbers to be year specific. Example, I currently us the prefix 10 in a number field set next to the serial number so the work order number appears as 10- xxx I would like to set a solution to calculate the work order number based on the last 2 digits of the date (11 for next year) coupled by a auto entered serial number to appear the same way on ou…

Hello. Forgive me if this is being posted in the wrong area or if it is a stupid question....this is my first attempt at creating a database with FM so I have LOTS to learn. I have a two fields in my database, one is date of birth and another is age. Age gives me the age in years and months. What I'm trying to do is create a field which for now I'll call Age Group that determines which group out of three the person fits in based on their age. The three groups I need are: Under 16's 16-18 Over 18's So I want a calculation that puts the appropriate group according to the age. Is this possible?

Alright, I'm accepting defeat... I've tried for hours and consulted all the posts on this subject - yet the URL just doesn't work. What am I doing wrong? Open URL file://Macintosh%20HD/Users/michael/Documents/Rheinport%20Media/Internal%20Projects/Art%20Vault%20Development%20Fall%202010/antiquesafes.gif I started with a GetAsText (container with reference) file:antiquesafes.gif filemac://Macintosh HD/Users/michael/Documents/Rheinport Media/Internal Projects/Art Vault Development Fall 2010/ Thank you in advance for your reply.

Hi i am newbie here and my english is not good i try to explain me I make a sampledatabase with 2 fields but on i put 1 field on 1 layout i want to create a script that when i clik a butom next field the first field change in the second field is that possible because i need this to make my dbase so light as possible otherwise i have to make more than 20 layout for 20 different fields i put a example best regard ric test.fp7.zip

Hello Everyone. I am trying to get a count on how many entry dates are in september of 2010. i am using the following calculation If(Month ( attendence::A_DATE )="9" and Year ( attendence::A_DATE )="2010";Count (attendence::A_DATE);"") And I get the count of all the entry dates. How do I get it to only count the September dates? Thanks!!

probably a simple question... but what I'd like to know is how to set a field's value based on the Privilege Set that a user is a member of... For instance, user A is a Member of Privilege Set A..so upon logging in...any new records created automatically set the Location field to A...etc..etc.. Thhnx in advance

Hello everyone i am new in here and to filemaker too. I need some help and hope you guys can help me. I have filemaker 11 i am trying to display on my inventory layout a "header" this being the result of a validation. Meaning i have a field called quantity. I want this header to check on the field quantity so if quantity is below certain level to display for example Re-Order if its between certain level display in Stock and if its over that level display Over stock. i would like to know if you guys know how can i achieve this because i couldn't find anything related to what i am trying to accomplish. Any help would be really appreciated. Tha…

Hi there I apologize for what many will consider a rather elementary question, but this one is somewhat frustrating. I want a field that when clicked upon will toggle between 2 graphics. My question is...I require a script that toggles between the 2 graphics when triggered. The trouble is, if I use a container field then you have to settle for a square/rectangular shape. (I prefer to use a triangular shape.) Ideally, I just want to use a graphic that will be converted into a button, that when triggered will be replaced with another graphic. Makes sense?

Hi, any help would be appreciated with this. I have records of jobs i do for customers, I am trying to find a way to work out the average days between each job we do ( to predict when they will need us next). At present I have a list of jobs with dates i.e. 1/1/10 Purchased 500 gallons 7/1/10 Purchased 500 gallons 18/1/10 Purchased 600 Gallons etc... What I am after is something like: 1/1/10 Purchased 500 gallons 7/1/10 (6 Days) Purchased 500 gallons 18/1/10 ( 11 days) Purchased 600 Gallons The date used is the creation date of the job record. Many thanks for looking.

Hello FM Forums guys and girls! I recently downloaded the trial of FMP and have quickly found how overwhelmingly powerful it is and so I'm having some trouble completing a seemingly simple task. I am trying to get the sum of numbers in a certain field only for records that meet a requirement like having a checkbox checked, radio button selected, or have a field with a certain value. Since just typing that sentence was difficult I've included an image to illustrate an example. In this example, I would like to add all the unpaid records, resulting in one number that is the total unpaid. Thanks!

Hi Forum-Members -) I have a task-DB, in which I want to sort the tasks after the Deadlines automatically. So I have the tasks sorted after the "priority". There is only one problem: If the field Deadline is empty, these tasks are sorted to the beginning! (Maximum of priority!) But if I finished a task, i clear the deadline... that's only one reason for not all of the tasks having deadlines. By the way, it's a pain to write a script for searching and sorting combined. Any help is more than appreciated! Hopefully my English is not tooo bad to understand :-) Michael7419

Every time I open this one database, it asks me if I want to use the system settings or the file settings. It doesn't seem to matter which I choose, the database works properly. But how do I get it to stop asking me to choose when the file is opening?

I have a need to increment a field when I select the button. This field will not always be selected but when it is, I want to find last value and increment by 1, move new value into last value field and move new value into selected field. How do I do this?

Here's something a bit crazy... But - given a field "FirstName", shouldn't the following calculation return "FirstName" only? Instead it returns "T1A • MAIN CIND::FirstName" (TableName::FieldName) Substitute ( GetLayoutObjectAttribute ("FilterSearch1 • " & Left(Get(LayoutTableName); 3); "source"); Case(Left(Get(LayoutTableName); 3) = "T1A"; Substitute (Left(Get(LayoutTableName); 3) & " • MAIN CIND::"; "T1A • MAIN CIND::"; "") );"")

I am working with external oracle database. I don't know how to stop a long Filemaker calculation process. Whenever ---"Summarizing field "xxxx", Records remain to process "xxxx", STOP button --- dialog box shows up starting a very long summarizing process and if I don't want it and I push the STOP button on that dialog box the process just stops fors two seconds starts again and again. The only way I can stop the summarizing process is to force quit the whole File Maker application....which is not good.. Please help me find the good way to stop a long unwanted summarizing process.

I have to keep track of shots on goal in a hockey game. I want to be able to have a field count up by one every time I press a button. Is that possible?

hi how can i make a calculations of s filed "amount" that will do a summery only if filed "check box" value = "yes"

Hiya All, This is probably dead easy but it's got me a little stumped. I've got a table that holds a record for every order we receive. It contains a date field that holds the date the order was created. I'd like to be able to calculate the average number of orders per day. It seems simple enough to calculate how many orders came in on any given day - self-join based on date field, calc field with count (Join Table: Date). But getting an average of that calc field would give a completely skewed result since a day with 6 orders would be counted 6 times, etc. Is there any way of doing this? Optimally I'd like to put all the dates in a portal showing h…

Hi, i just started a database for a Lao/French/English Dictionary. Lao words are often composed juxtaposing some base words. For exemple the word BLUE (si:fa:) is composed with the word COLOR (si:) plus the word SKY (fa:). So with my main word table I have all this words on seperate records and I want to have a field "litteral translation" going to search in the others records the translations of the base words of my composed word. I created 3 fields lao1 lao2 lao3 to put parts of my composed words, and fields trans1 trans2 trans3 to put the correspondant litteral translations. I tried to create an auto entered calculation for trans1 trans2 trans3 but i …

In Form View, I understand that you can have a text label show the contents of a field like so: <> I'm looking to have a similar text label that shows the dimensions of whatever graphic/image has been pasted into a container field named, let's say, "image." Is there a function that returns a string containing this information? Ideally, I'd like the text field to display something like "320 x 240," but being able to see anything at all would be helpful. Is this even possible?

What am I doing wrong? I've tried lots of combinations, except the right one. Case (Get ( CurrentDate ) - Backup_CreationDate & " Day Ago" ; Get ( CurrentDate ) - Backup_CreationDate & " Days Ago") I would like this to display 1 Day Ago or 2 Days Ago not "1 Days Ago" it looks horrid. Thanks

I have a table that contains five different date fields and I am trying to create a status field based on if a date has been entered into into one of the date fields. For example if date received contains a date then the status would be "Open" if date quoted contains a date then the status would be "Quoted" and so on. I have tried using the case function but it seems to only work base on one field. I'm a little lost. Any help would be greatly appreciated. Thank You Anthony

I have 2 tables. One called "Jobs" and the other one "Hours". "Jobs" has the amount of time alloted for that specific job. "Hours" has a number of entries per day that go toward different jobs. My question is, how to I create a field on the "Jobs" table that subtracts the total of hours completed from the "Hours" table based on each job? I've created a report that divides up and sums the hours based on the job number from the "Hours" table but don't know how to use that to subtract from the total allotted on the "Jobs" table.

I have a layout that contains a Month field calculation MonthName(Date) and a MonthSort field calculation Month(Date) to find the creation date of all orders for a year, and display the total by month in a sub-summary by Monthsort. I want to add another search that finds the invoice date of all orders instead, so as an experiment I changed the (Date) to (InvoiceDate). It worked, but I'm unable to figure out a way to create a second set of MonthSort calculations at the same time as the original (Date). It seems as if I can't use anything like Month2, or MonthSort2 because Filemaker doesn't recognize the fields in a calculation. Is there a way to have two Month fields and t…

I recently stumbled across a method of highlighting multiple selections that is relatively easy to use, however, as I did not create the actual method, I'm still trying to figure out exactly how it works. Basically, (and in my limited understanding) what this method does is take the value of the whatever field you define adds it to either a field or variable (in my case I'm using a variable). When you click on another record it records the current values in the variable and then adds the new record's content to the calculation using pilcrows. If you click on a record whose contents are already in the calculation, it removes it. The problem (and I'm not sure …

Hi all This is probably a simple one. I am trying to use the the function Filter() to flag certain characters contained in a field. I would like to flag the use double quote in a field. The problem I have is that double quote is used as a special character to delimit a list of characters to be filtered. How do I take away the special meaning of double quote within my list? Thanks Lee

Hi all, I'm trying to make a "basic" database that will calculate my daily and weekly hours, and also do a calculation that factors in all income tax percentages. The system is based on a bi-weekly paycheck, so my db features a 14-row portal that allows me to visually see all two weeks (should I work 14 days straight) without having to scroll down. I have attached the database to this post with hopes that someone can help me with the daily hours calculations (I work by the hour). I have fields for time clocked in, time clocked out for break, time clocked in from break, and time clocked out. The portal row even features a daily hours field which is what is added…

I have a database that allows an individual to select an item from a drop down box and from there, to see the description of the item, and what number purchase order it relates to. The problem though is that if that item is accounted for in multiple purchase orders, i can only see the first order that it was created in. Is there a way to set up a calculation or field that will display all of the purchase orders that this item occurs in? For example:Purchase Order Layout displays Purchase orders 1,4, and 5. Each of these orders use the item "Metropark Hanger" In the ITEM Layout i have it arranged that a drop box menu displays items such as "Metropark Hanger, Plast…

The result of List(field) comes out in record creation order. Is there any trick to control that? I'm populating a single calculated text field in the main file with a list of field data from a 'many' relationship (thru a join table), and would like to allow the user to control the sort order of the result by modifying a field within the table the list is pulled from. Looking into "create a value list based on values of the field in question, make it a conditional value list based on the relationship ID... then use ValueListItems() to return only the unique values. Works best in a script." That worked. As the target field to make a list from was a calculat…

You folks will soon discover that I'm probably dumb as a rock and not nearly as useful. With that out of the way, here goes... I have a checkbox set with such items as "Drain Fresh Water, Drain Water Heater, Open Sink Valves". I also have a date field. What I would like to do is conditionally format the checkbox field to have a red background if the date field falls between Nov-1 of the current year and Mar-31 of the next. Thanks for the welcome and as before, thanks in advance for your assistance.

Hi all, I am new to FM and new here.. learning as I go, so far so good.. though I am stuck on one calculation. My question is how to write this calculation when one of the fields included in the calculation is empty. I am trying to accomplish a calculation of "days held" for assets that we purchase and then resell. Take the lesser of CurrentDate or DateSold, and subtract the Purchase Date. If DateSold is empty, then CurrentDate - Purchase Date. The result is the number of Days Held. I think I have to use Case but I am not sure. Here is what I have: Case ( IsEmpty ( Date_Sold ) ; Get ( CurrentDate ); If ( Date_Sold < Get ( Cur…

Hi there, I have been trying different methods to achieve the following - and none are correct as far as FMP 11 goes. Perhaps someone can help me ? I need to be able to tell which values in a relationship have the highest count. Parent record = Owner Child record = Shop Child record = Category Child record = Items If in the related items the carrots have sold the most, I need the Parent Owner to know this. Example data: Apples Apples Carrots Carrots Carrots Carrots Carrots Carrots Dried Fruit Dried Fruit The value I want to see is Carrots. The parent record has to be able to know this. The ER is as given abo…

I am trying to get the number of days since a person has checked in... Get(CurrentDate) - Month(A_Date);Day(A_Date);Year(A_Date) Get(CurrentDate)-A_Date I have tried everything that i can think of. thanks for your help

I am currently facing this issue: I have some tables that contain an awful lot of fields (~150 each), that when I need to view as spreadsheet it takes forever to scroll sideways. So, I thought I could separate each field value as a separate record (somewhat similar to data tagging) in a separate table where each record gets a "tag" to identify its source field, the value of the field and the name of the record it has come from. I guess I have to script the process, but I am afraid that due to my limited experience I will produce a too lengthy script or mess it up with loops. Can anyone help me on how to do this? Thanks in advance!

I have a mileage field that calculates how much a person is paid for mileage each day. When they put in one of their appointments it automatically fills in this field based on how much they receive which is found in the employee table. The problem is that at this point it calculates every time they put in an appointment but they only get paid for it once per day. How can I have it check to see if it has already been entered for that date and then leave it blank if one already exists? Thanks!

How can I limit a time field to accept only 15 min increments? ie 10:00 AM, 10:15 AM, 10:30AM, etc.

I'm creating a simple database of plant material, and as I work through the fields and populate each I'd like a live indication of which fields have been updated and which have not. I don't know how to achieve this? Related: I have a few fields that respond to a Yes/No value field. How can I have a live count of how many records have "Yes" in that field?

Hi there, See the file attachment. I came across two dbs. They are used to email messages but there is a bug that I cannot solve. The Email file looks like Outlook Express. You can contact multiples guys using the Cc or Bcc options. However, only the first line (address) is transferred to Outlook Express. From what I saw, the Mailto field plays a great deal in this task. Only for gurus. Thanks in advance Email.zip

Hello All, I am developing a small DB for a re-seller of commercial products. Customers call the User to order products, the User enters an invoice in the DB and then orders the products from the Manufacturer. (No inventory is kept on-hand, either physically or in the DB) The User receives the products from the Manufacturer weekly but with some items backordered. I've already handled subtracting the B/O items on the original invoice. When the B/O items finally arrive, how do I script an invoice maintaining the Orders::_PK that is already half billed/paid? My first thought is to create a B/O Table using ORDERS::_PK and Order_Line_Items::Backorder to create a ne…

Hi I have a Filemaker 8.5 database that among other things I use to import images into container fields. I have a text field "PathName" for the path to the imported image and a text field "FileName" for the file name of the imported image. I am trying to create a calculation to extract some of the text from "PathName" which is never the same number of words or letters from the left or right - so I can't use the LeftWords or RightWords functions. But if I could subtract "FileName" from "PathName" I would be left with a constant where I could use RightWords to get the info I need. Is this possible (or is there another easier way to do it) Examples of the text are …

I have a Text field that I use to enter information in, nothing special about it, just notes. I have a button next to it that inserts the "Current Date" wherever the cursor happens to be. The date shows as 24/08/2010 is there some way I can get it to show as 24-08-2010.

If a user wanted to edit an 'order' and change a field that was part of a relationship definition, how can I get the _fk_ID to repopulate in the related table? SPECIFICALLY: if order 'ORD2010126' is later edited in the Orders layout and the Orders:_FKCampaignID is changed to 'CAMP00020', how do I get the Order_Line_Items::_FKCampaignID for order 'ORD2010126 to update for all the relevant records in Table Orders_Line_Items? I tried an 'UPDATE' script with Set Field, but I guess I don't understand it well enough to make it work. I've attached an image of my relationships.

I have a group that flies several trips a year on different airlines. I am trying to find a script/calc that will figure each passengers YTD Award Miles per airline for inclusion on the report given to the group leader. The leader gets this report every time flights are booked. Fields include; Passenger Name (drop down list), Airline (drop down list), AwardMiles (this trip) and YTD Award miles. The reason for this is so the Group Leader can keep an eye on YTD miles in order to keep or upgrade their passenger status for each airline. This is the best way I know to explain. Please let me know if more info is needed. TIA...

I have a field that contains text.. Here is an example: [color:red]CONCEPTRONIC Discovery Mark VII Forced Convection SMD Reflow Soldering System Electrical: 480 VAC 3 Phase Dimensions: 53” wide x 172” long x 51” tall Shipping weight 2,500 lbs Included are operation and service manual, wiring diagrams, schematics and drawings. This text can be used by users when searching the database, but I see that a query will fail as a result of the quotes used in the dimension description. My question is: How can I comment out those quotes without using the Quote () function? Substitute () doesn't seem to work either. The issue is that I can't have the entire query…

Probably a newbie question... because I am, and I am looking for an easy answer. I have a begin date and an end date field, but I want to have the entries that are made in these two fields return the Week Begin Date and End Date to their respective fields. Now to the books. Thanks in advance for any support.

Hi there, I am importing two fields Now I want to check on import "if field one is empty, set field 2 to empty" It works if I change the value in field 1 manually, then field 2 gets updated, but not on import. Any ideas?

I have a project database with all objects that have to be worked on for the year. There are three main categories - each object will have one category assigned to it (residential, light commercial and commercial) and then each object has a status (unassigned, scheduled, field completed, submitted, billed). What would be the easiest way to get a quick 360 - how many total, how many of those are residential, light commercial, commercial, how many of each are in what status? So far, I can only think of tackling this with a script, but i would love to just have a window on my form that shows those numbers, since I have plenty of screen real estate.... there has to be an easi…

I'm trying to set up my FM invoice file to calculate sales tax - or NOT - depending on whether the job I do is taxable or not. I'm not sure that I've set this up the right way, but here are the fields I currently have: Subtotal (a calculation of Hours X Rate) Sales Tax (a calculation of Subtotal X .0775) Taxable (a Checkbox Set with values list:True, False) Total, a calculation which has yet to be determined It seems to me that the calculation I want performed for 'Total' is as follows: If 'Taxable' is checked True, then Total = Subtotal + Sales Tax If 'Taxable' is checked False, then Total = Subtotal But I don't really know how to set up …

I wonder if there is any way to use the value set in the "Maximum number of characters" (in the menu Field> Validation) in a script. I have not found any function that returns this value. Thanks, Vitor Cedran

I am an educator and a newbee to Filemaker. The students are ranked based upon certain achievement scores. When I print the list in order of achievement, I use the @@ function to print each student’s ranking. Once I have their ranking I would then like to print their names alphabetically. If I rearrange the list alphabetically I cannot keep the original ranking associated with the name. I would appreciate some assistance

Hi everyone need a bit of help. i have a summery report here is the case. Case ( Month ( Date_Invoice ) < 4 ; Year ( Date_Invoice ) & " - Q1"; Month ( Date_Invoice ) < 7 ; Year ( Date_Invoice ) & " - Q2"; Month ( Date_Invoice ) < 9 ; Year ( Date_Invoice ) & " - Q3"; Month ( Date_Invoice ) ≤ 12 ; Year ( Date_Invoice ) & " - Q4" ) the problem is when i get the report Q4 is way off. i have another summery report that i use which automatically takes 4 days from the invoice date here is the let function for this. Let ( [ trudate = Date_Invoice - 4; yr = Year (trudat…

I have a trailing grand summary that is giving me an astronomically ridiculous number, and I can't even figure out what is being added to come to this total. My two relevant tables and the fields I'm working with in this report are: Rolling Projections RP_Month (date field, validated by value list, only allows user to choose/input the first day of any month, i.e., 8/1/2010) RP_Dollars (unstored calculation field, multiplies user-input hours by a client's hourly rate found in another table) RP_Dollars_Sum (Summary field = total of RP_Dollars) OverUnder (Unstored calculation field = RP_Dollars_Sum - Billable Targets::BT_Sum OverUnder_Sum (Summary field =…

What are my options to have users of a shared file view the contents (image) of a container field. The images were imported using the following script step: Import Records [No dialog; Images; Picture and movie files; Import only a reference; Add; mac Roman] The solution file mostly uses the thumbnail images. A script calls up a layout to view a large image as needed. How can I make the large image display on the client computers? Can this be done without changing the way the images are imported?

Is there a calculation that would allow me to get the highest value in a field and then ad 1. I can't think of a function that I could use to get the highest value in the dataset. Thanks Kevin

I have a table called config. In it, I am storing a username, address, phone, email, ect... of info that I want to use in printed forms and other sections of the app. So I made this table and setup global fields. I added a field for last modified - and since globals only store and do not update, I kept it set as a normal field - not a global. I have a layout showing this info, however when I edit any fields, it does not trigger the last mod stamp field to update or change. Is there something I am missing ...? Shouldnt a non global field set for last modified get updated when any of the fields in this record change?

Was wondering if I could get help with a calculation. I have the following fields in a database in a line items/portal thingie (very technical term I know :-P ): Date--Day--Time In--Time Out--Total Hours Basically I want to enter the time a person came in, the time they left and the total hours that they were there. It's simple enough if the hours are within the same day...but how do I get hours to calculate if it went over into the next day judging by the time I entered. Ex. So I enter the following Date: 14/08/10 -- Day: Saturday -- Time in: 10:00 -- Time Out: 9:00 Hours: 23 i.e. - I came in at 10 am on Saturday and left on …

Sum Calculation not Working Need a little help, my example explains my problem. Thanks for any help. SumNotWorking.zip

Having a brain fade here, need a calculation to check a field called "Trips" so that no Trip Name is the same. I no I can use the validate option in the field options but it's cryptic message is not much good to a user. I like to attach the script to a Script Tigger. Many Thanks

How/can I use a backslash in a field calculation (test result): i.e., Field & "" & Field

I need a calculation that will display the contents of 1 field in another without the hyphens. For example... MMM-45963-000-01 Should be displayed as MMM4596300001 I'm using everything I can think of, such as Left and LeftWords functions, Mid functions, Position, etc., but I can't get it to remove them all. I can't predict how many there are, but the most I've seen is 3. If this would be better served using a looping script, I'm okay with that too.

Hey guys, I have 3 tables. Jobs Worksheets People I link a worksheet to a job. I link people to the worksheet. People on a worksheet may not make it onto the job so they're not linked to the job. I display job name at the top of the worksheet by placing the jobname field from the job table at the top of the layout. I want to keep that jobname there for all the layouts, including the people's layout. The reason i can't do this is because the worksheet layout is displaying data from the people table and isn't directly linked to the job table. What field do I have to create that would be stored in (i assume) the worksheet table, that wil…

I need help from those of you more creative than myself. Is there a way that a calculation can find every parenthetical phrase in a text field and move each one to the end of that sentence. Example Input: "The patient's score was average (i.e. Full Scale IQ = 50th percentile) on the Wechsler Scales of Adult Intelligence." Desired Output: "The patient's score was average on the Wechsler Scales of Adult Intelligence (i.e. Full Scale IQ = 50th percentile)." Thanks for your help!

I have Table 3 that aggregates information from two other tables. I have a repeating field. (No, I don't want to turn it into Table 4; it will never have more than 15 rows and the only values that are entered are integers 1 through 20. It would be silly to create Table 4 for such predictable, constrained data.) I have Portal rows From Table 1 and Table 2. I'm bringing in a variety of items and their characteristics. This all works fine. One of the pieces of data is a number. I want to multiply the number in Table 3 repeating field n with the corresponding row number n from the portal data from Table 1 and store this in a Table 3 calculation field. I've…

Hello I'll try to be as clear as possible. I need help with the current date. I have a field which is called CURRENT_DATE, I want when I open my layout as the current date is displayed in my fields current_date. That is what I do as shown in the help .I right click on my field and on the menu I choose "Setup Button", I click on "Perform Script" and I click on "specify". Now I select my script "DateX". My script contains the following code, "[color:red]Insert Current Date [select; MyTable:: current_date]" ... But nothing happens I can not see my current date displayed on my layout. Why... is it possible to do that. Thanks and sorry for my bad english Sylvain.

How to filter records in a portal with date range in FM 11 without relationship? I tried datefield >= $$date1 and datefield <= $$date2 but it displays records only equal to $$date1 (datefield = $$date1).

Greetings everyone, I have a 1 to n relationship. On the "1" side, I have set up a field to sum up a specific field in all the related records on the "n" side. I have set up a script that is executed on a given field modification. It uses the calculated field mentionned above to perform some more calculations. My problem is that the script seems to be executed before the calculation field has been updated so that I am always one step late. Is there a way to go round this ? Thanks in advance for your help. Eloi