Calculation Engine (Define Fields)

I have a sample invoice with radio buttons that display >30 days >60 days ...... etc. I having trouble figuring the calculation based off of the most recent date of the customers payment. This is how I see it in my head but I can't figure out the calculation correctly. This is not correctly formatted, just pseudo. IF Balance < 0 and (payment_date >30 but <61 ) THEN past_due ">30 days" IF Balance < 0 and (payment_date >60 but <91 ) THEN past_due ">60 days" and so on........ Any help would be appreciated.

Hi, I have a field that imports from my email. It pulls in the email address and the name at the same time so it looks like this: John Smith I really need to trim this by either a calculation or a script that just goes through and processes the records so that it looks like: [email protected] I have to trim the name and also the < and > symbols. Any ideas? With great appreciation! Randy

hi every one. can anybody have a lock at this pleas ? "If ( Contacts Bill To::tax = 1; Round ( (Subtotal-Discount)*(Sales Tax Rate/100); 2 ))" is this calculation ok?

I've done some searching and some testing but I can't seem to get Get ( ActiveLayoutObjectName ) to do anything for me. My latest test is a very simple script. Show Custom Dialog [ Message: Get ( ActiveLayoutObjectName ); Buttons: “OK”, “Cancel” ] The script is run by clicking on a button that is on a tab panel that has an object name set. All I need is to know what tab panel is active.

We are in need of a FMP database that can Calculate an RFC 2104-compliant HMAC with the SHA256 hash algorithm. This is a solution to a new requirement that Amazon.com has: http://docs.amazonwebservices.com/AWSECommerceService/latest/DG/index.html?rest-signature.html So we need to take our Access Key and Calculate an RFC 2104-compliant HMAC with the SHA256 hash algorithm. Here is an example: Calculate an RFC 2104-compliant HMAC with the SHA256 hash algorithm with our Secret Access Key: 1234567890. After encoding it should be: Nace+U3Az4OhN7tISqgs1vdLBHBEijWcBeCqL5xN9xg= Any help would be great as this is way over my head☺ …

Hello. My problem is something that should be a simple thing. I just need a calculation to give me the next letter from the one endeared on an other field. "a" on the first field, "b" the result of the calculation. I have tried "substitute" and "case" giving all the possible solutions, but I don't like it. Is there an other way?

Hi, I have a field Status, it's a defined list. The list is Pending, Confirmed, Paid, Declined The value can be any of them, BUT If the value is Pending, 60 days after the field DateReferred I want it to change to Confirmed. ALSO it can be changed to Confirmed before the 60 days has passed. This sort of works as a Calculation, but doesn't allow for input when a record is created. What would be the best way to do this? To have the field auto-update. TIA, jim

I've been racking my brain trying to remember the name of a function that will take the input of a field and then 'filter' it through a string for compliance with the characters in that string. For example: x=instring("abcdefghijklmnopqrstuvwxyz") .. would make sure that x only contains lower character letters. I've seen this and have looked in 4 books and the "DEVELOPER's CARD for fm PRO" but can't find it. If you know what I am looking for, I would appreciate it if you would pass me the name... thanks...

I am fairly new to FMP but not to programming. I have managed to figure out how to generate a monthly report based off of current date but I can't figure out how to use the dropdown calendar to do it. This is what I used for current date. Enter Find Mode [] Set Field [iNVOICES:date_post;Month (Get ( CurrentDate ) )] Perform Find [] The dropdown's name is xdate. I've tried to make INVOICES:date_post = xdate then Perform Find but it's not working.

Hello, I have an INVOICE table with a portal showing ITEMS sorted by ID, with a Field for the $ amount. It looks like this: 1 LineItem $25 2 LineItem $50 3 LineItem $10 I have a nice grand summary of $85 at the bottom of my invoice. But I would like a calculation to have a RunningSubtotal that shows in each of the LineItems, summarizing only up to the current ITEM portal record. It would look like this: 1 LineItem $25 $25 2 LineItem $50 $75 3 LineItem $10 $85 I've seen that using getNthRecord function, I can get a field value from a single other record (e.g. 3 records prior) but I can't figure out how to summarize a running subtotal for…

Hello All I have a string of data "abcd" i would like to present as a list: A b c d An advice?

Below is a formula using the case function for determining which range the date I specify falls into and it should return 1, 2, 3, or default 0. Currently, it works fine if the date specified is within 2007, i.e., a 1 appears. If I specify a 2008 or 2009 date, it returns the default 0. What am I doing wrong? BTW, 2009 is intentionally left open ended. Thanks for the help. Leonard Case (Search_date ≥ Date( 1 ; 1 ; 2007 ) ; Search_date ≤ Date( 12 ; 31 ; 2007 ) ; "01" ; Search_date ≥ Date( 1 ; 1 ; 2008 ) ; Search_date ≤ Date( 12 ; 31 ; 2008 ) ; "02" ; Search_date ≥ Date( 1 ; 1 ; 2009 ) ; "03" ; 0)

I have been working on a database I have "inherited". It was probably designed in Filemaker 4. There are several fields where the designer used a question mark in the field name. For example ?MF is the name of one field for a Male Female check-box. Someone recently warned me about using question marks in names, but they did not say why. Do I need to be concerned about these field names? Filemaker has given me warnings about other special characters in field and table names, but not about question marks.

I have a text field that has a "list" calculation set up in it. It works great - however to make the data show up in this new field i need to recheck a check box to have any content initially show up. My question is: Can I "refresh" these boxes so that they have the list info put into that field? I have 30,000 records so i'd prefer not to do them one by one.

I am wondering if there is any way to automatically update inserted, referenced files when I need to give my Filemaker database to someone else to use on their computer.

I am trying to find out if there is a filemaker plug in or some way to pull exif header data from a Jpeg photo that resides in a container field in a table I have made. I am specifically asking for one on the mac platform.

Hello, I tried to search for an answer here but I probably was not using the correct terminology so my apologies if this has been answered somewhere.... I have a small, quick and dirty (flat) file with 29 records. There are three container fields. One holds a picture. The pictures are usually around 500 K to 1 MB. These are pictures that I did not create and I don't have a lot of graphics capabilities on my computer (or much experience in that area). The other two container fields are for multiple sheet Excel files which are on my computer (but that I likewise did not create). The only way I could find to get these Excel files into the DB was to drag them into t…

I am trying to use validation for students and inventory items. I have 3 tables: students, inventory and line items. Within the line items table I have a layout called "checkout." The first field in the checkout is studentID (which is also the join field) and a "go" button next to it. When the "go" button is hit, I want it to check against the student table StudentID. I created the validation area for the field and all seems to be except... when I hit the go button, it goes straight to the other layout without validation. However, if I click away from the field, it will check. How do I automate this? thanks

How can I get a list of all Sundays for the current year in this format? 1/4/2009 1/11/2009 1/18/2009 and so on for the entire year... TIA

Hi, I want to take a date recorded in a filed and add one month onto it. I have found one way that works by using the calculation: Day ( DD Start Date ) & "/" & Month ( DD Start Date ) + 1 & "/" & Year ( DD Start Date ) "DD Start Date" is the original field. This actually works which surprised me, but it fails when the next month is January i.e. the year has to increase by one. I assume there is an easier way to do this?

Hi all. This is my first attempt at asking for help here, so here goes. I'm trying to build (what I think is) a simple calculation field and I'm running into some trouble. I'd like to sort through a series of records for the dates when a student was absent, filter whether on that date they were absent or tardy, and calculate the number of times in a given month they were absent. Seems simple enough-- what I've tried for a calculation is below and perhaps reveals how little I truly understand FM. Suggestions welcome. ValueCount ( FilterValues ( List ( absence::absent_or_tardy) & (MonthName ( absence::date_absent ) = "September" ) ; "absent") ) …

I have a table called routines that is linked to a table called effects. The effects table has a field called TIMEINMINUTES on the layout for routines, I have a portal where you can pick effects (basically assigning them to the routine) I would like to get a total on the timeinminutes but when I create a summary in the effects table and put it on the routines layout, I only get the data from the first record in the portal. For example: Routine A has Effect 1 and Effect 2 listed via portal. Effect 1 has a time of 10 mins and Effect 2 has a time of 15 mins. I created a summary field in EFFECTS and put that field on the routines table, but …

I have a situation where I would like to be able to password protect only a few records of a database . Is this possible. also is there a way to let the password protection go after a year and find its release from a site maybe with an atomic clock so it would be accurate. thanks

Hi everyone, I'm writing out a series of questionnaires for a medical research group, and my people are asking me to create answer fields that appear as their original wording (e.g. "yes" or "no") in the form, but are codified into numbers (e.g. yes=1, no=0) in the table, so they can easily input that data into their system. Is this one of those obscure requests from boss to employee that takes the utmost tact to explain to them that it's impossible, or is there a simple way to do this? Thanks!

:bang: I have to make a database for a friend who has garden shows . there are spaces for each show at specific tables. the majority of the exhibitors are the same and he prints out a roster. the naming is as follows; 1st table = A the 4th exhibitor = 4 so the printout becomes A;4 = jeremy's garden in the database it would say table a 3rd space = Melissa's flowers table a 4th space = Jeremy's garden table a 5th space = Patane's Landscaping but sometimes for whatever reason someone new is inserted between , or ex. jeremy's garden and Patane's landscaping this changes Patane's Landscaping to number 6 at the table instead…

I want to validate the input in my Date field by a calculation. I have the fields: EmployeeID Date It has to check if the date already exists for that user and if the date is older than tomorrow. thanks in advance

It is some time since I last used Filemaker in anger. I know that I can do this but I can not remember how. I have 2 tables customers and Conversations. In the conversation (child) table I have a next chase date which I want to copy the last one into the customer table. I have completely forgotten how to create this calculation. Help as always is very appreciated.

Trying to create a math test that displays the answer with 3 other similar answers. Example file works, but I am sure that there is a better way of doing it. Math.zip

I am trying to add a table to my Bingo database that tracks Jackpots for certain games. Every session, the pot grows until it hits either 5000 or 10000 depending on the game. When it does, there is no pot growth until it is won. How do i keep a running balance and then pull that balance into my main table so that when I enter a new record it shows the jackpot balance. Thanks

I have found a couple of similar threads about this but none the solutions quite fit my requirements. I have a 'Products' table and in that table I have a field that holds the the quantity sold of a particular product. I also have a 'current product' (with Yes/No drop down selection) and a Category field (with pre-defined categories , again with a drop down menu) . I would like a field that holds the sales 'ranking' based on the fact is A) a current product and B. the product category. The threads that I have found suggest a self-relationship but I can't seem to get this to work. Any help would be much appreciated. I have attached a basic version of …

What is the best/most efficient way to achieve this: I have a table called "Animals". There are 3 pertinent ID fields: Animal_ID (unique table ID) Sire_ID Dam_ID The dam and sire IDs are unique Animal_IDs related to the "child" Animal_ID. A "Child" Animal_ID may be a dam or sire in other "child" record, but not all "child" IDs will be parents. I need to create a portal that only includes Ancestry or "parent" (dam and sire) animals. It would be a subset of the full animal list. I've tried several ways to achieve this, but can't seem to get either a calc field or list function to work. Thanks.

I'm building a simple list of items with quantities and total amount. In my next calculation I must calculate the price of an item and substract the price of another item previously entered. I'm not sure how to do that. Must I perform a find to go to the desired item followed by a Get (ActiveFieldContents) in my calculation or is there another function specifically design for this? Thank you. Isa

Last night I shut down FMP without issues. However this morning I discovered one of my scripts which checks Get ( RecordOpenState ) was set greater than zero. Not knowing in which table the unsaved record was located I built a script to loop through all records in all tables and commit each record without dialogue. Nevertheless FMP interrupted the script asking whether to save the record. Is there a better technique to ensure all records are properly handled should FMP crash with a power outage? Unsaved records must surely occur now and again. Recommendations?

we are hosting the database on FileMaker server 10 advanced and connecting to it from various machines running FileMaker pro 10. Our problem is with the container fields. We are storing quicktime movie references in a few of these fields. If one person inserts a quicktime movie from their machine, everytime they opens the database, they see and can play the quicktime movie. However, if someone else opens it from another machine, they get file not found. The qt movie is inserted from a shared folder on the server and the reference path is as follows. servernameVideoShare001.mov I can't figure out why other machines cant see the movie when they v…

I am asking if there is a script way to change the fill color of a field. along with that is there a way to change it only from color to original as in a click on click off behavior. I would like to be able to do this. thanks you in advance for your care and consideration.

I would like to make a script that would drop a pic into a container field and then make it blank by clicking on the container field. The picture would reside in another table

Hi all, We have a table called FMRI There are two fields that we would like to set as semi unique value: 1.Subj_num 2. FM_subj_num For both fields I setup Validation “Not empty” What we like to do is, prevent users from creating duplicated record with the following fields: subj_num and FM_Subj_num . Such as: In ideal situation we would like it such as: Record 1: subj_num = 2 and FM_SUBJ_NUM = 3 Record 2: Subj_num = 10 and FM_subj_num = 3 But if users create two records like this then inform users that there is a duplicated subj_num and FM_subj_num already exist in database: Record 1: subj_num = 10 FM_SUBJ_NUM = 3 Record…

I don't know for sure where this question should go... but this seems right to me. I use the open URL to get XML product data from Amazon and they are changing things to require a signature. Old: http://webservices.amazon.com/onca/xml?Service=AWSECommerceService&AWSAccessKeyId=*******&Operation=ItemLookup&ItemId=9781585672844&IdType=EAN&ResponseGroup=small New: http://webservices.amazon.com/onca/xml?AWSAccessKeyId=*******&IdType=EAN&ItemId=9781585672844&Operation=ItemLookup&ResponseGroup=small&Service=AWSECommerceService&Timestamp=2009-08-10T22%3A45%3A28.000Z&Signature=9%2BTIDVYxBNBQB72mvmb%2Bnxjc%2BjE2LzG…

Sorry for asking a probably very basic question, but I' didn't find this simple case either in FMPro help or searching the forums. I'm simply trying to manually parse a csv-file from a number of text, date, time and number fields. I've tried a number of combinations around the field names, but haven't succeeded to get the "" 's around the values. GetAsText(DateField1)&","GetAsText(TimeField1)&","GetAsText(NumberField1)&","&TextField1&","&¶ should produce something like (with correct prefixes) "1.1.2009","12:30","8.5","Abc" I've tried to put " and "" (among others) around the field names, but there is always something wrong w…

Hi Everyone, I am trying to make a calculation for our service database what I'm trying to do is as follows. date machine setup 1/8/09 date machine in for service 10/8/09 = 10 days machine was used for 16 hours, 16 divided by 10 = 1.6 hours per day so how I'm trying to do it is as follows. i have 3 fields One Used hours calc field: Hours Out - machine hours = x used days calc field: Date out - Setup date = x Daily Average field: ( "maintenance: 1: Average Hours" ; "maintenance: 1: Average Days" ) But it is returning -2990000 i have obviously done something really dumb, but i just don't get it. Can anyone help???

Is it possible to have a calculation that converts two ways: If I have a weight field for grams and a weight field for pounds (or for any weight this was the first thing that popped in my head) is it possible to convert no matter which weight field is entered? I can convert on to the other but I cant figure out how to do both...if possible. Jim

Im not finding it but is there something to increment by letters? 1a 1b 1c kind of thing? TIA

Hi people I'm having a bit of trouble here, and I've tried trawling through previous posts but haven't really found anything helpful. Problem: I'm trying to put together a formula that will allow me to calculate the turnaround time it's students assessment to be marked. I have two dates, 'date_received' and 'date_marked', and obviously the turnaround time would be the difference between these two dates. What I would like to do however, is exclude the weekends in this calculation, so if an assessment was received on the Friday, but marked on the Monday, then the turnaround time would be "1", and not "3". Anyone have any ideas on how to do this? I'…

Hi, I'm trying to round a calculation field (time). The simple calculation is RestTime/RestTimeTotal*PayableRestTime (all times). For example, with the following values: 0:07/27:00*27:00 the calculated value is 0:06:59,999999 (hhmmss) If I set the time format to hhmm, the value is 0:06 instead of 0:07. Is there a simple way to correctly round up the calculated time value, I seem to have a mental block with this one...? Thanks in advance, JariV field

is there any way to write a calculation that would list all the values of a certain field, within the found set. this would be used in a 'set field' script step i really really need this fast, if someone could try to help. also the script will be a loop that repeats 12,000 times, so keep that in mind; i'd like it to run quickly. also, i don't know why, but i made one attempt to do this by exporting field contents to a .tab file and then setting that file as the url in a webviewer and then using getlayoutobject attribute, but it didn't work because the webviewer wouldn't open the .tab file. i don't get why. did somehing change between versions 8.5 and 10 (i'…

I need to take a date field that is not always today's date (example: 8/1/2009) and change the output to yyyy-mm-dd I'm trying to use a calc field as text since I get a "?" if I use it as date: Let ( [y = Right ( date ; 4 ) ;m = Left ( date ; 2 ) ;d = Middle ( date ; 4 ; 2 )] ; Substitute ( y & "-" & m & "-" & d ; "/" ; "-" )) However since the date field isn't 08/01/2009 I can't get a consistent output. I looked at some custom functions but can't quite find what I need ... I think I'm close but maybe I'm going about this wrong? Help is appreciated.

I have 2 files with the same calculation field in both files. It works in 1 file but not the other. I have no idea why. Any insight? The table relation is the same in both files - tableA relates to tableB thru recID = recID_tableA other fields populate fine. TableA field is a calculation field if(tableB::mark = "yes"; tableB::num; "nop") In the first file, the calculation returns tableB::num. In the 2nd file, it returns "nop" when it should return tableB::num. I'm stumped. I've deleted the field and recreated it, I've tried different options that should deliver the same result. The end result is the same - it works in the 1st file but…

Hi guys, Have a really basic question here, I'm just starting out with FileMaker and I would really appreciate a little advice? I have a form in which I'd like to choose a client name from a list, drawn from a value list which is taken from another table (a client contact list). I then want another field beneath that which looks up that client's reference number from the referenced table (the client contact list). I managed to achieve this earlier but somehow broke it - could anyone give me some pointers here as to what I should do to get this functioning? It's the lookup function that's broken - I can select the client's name in the first field…

Hi, I've been learning FileMaker over the last few months. One thing I haven't really approached is loops, and this is the first time that I think I really need to use them, although I hope if I'm wrong that you'll let me know. I want to have a queue system where each new record is given the highest rank and I'd like to have this feature: a text box where you can enter a new rank (let's say X) for a record and have a script move the record currently at X to X+1, and so on. If you've used any ranking feature, like Netflix' queue, it should work very similarly. I don't want to ask anyone to do the grunt work for me, obviously, but I'd really appreci…

Hi I have created a field called "Turnaround" that lets me know when a job was required. I would like to show in a summary how many jobs were: Same Day Next Day 2 Day 3 Day 4 Day I have created a field called "Same_Day_Count" and would like to have a Count Calculation that only counts the Same Day's in the Turnaround field. Any ideas?

Okay, I've played with this too long. I want to be able to use 1 double quote (") to search in an HTML string (so I can remove it). Since I need to repeat this process many times, I tried to insert the quote into a global text field using various constructions including (but not limited to) the Quote() function, and escaping the string. Will someone kindly show me the correct syntax? Thanks, Steve

I would like to have two calculation fields which will give me the value of a number field rounded up to the nearest 25 and rounded down to the nearest 25. Is there a simple way to do that? Thanks much!

I have been searching but could not find a thread that explains this: I need to perform validation so that a calculation field is unique. This is the situation, we work in a field where our contacts switch companies a lot. If someone tries to enter a name in company B, I want validation to check to make sure that that contact doesn't already exist in company A or C. If it does, it should warn them so they can decide to switch companies for that contact or create a new contact with the same name. Please link me to a thread if you know where it is! Also, I found a website that recommended a self join based on the calculation that concatenates the first a…

Certain songwriters want their name listed first in the "Copyright Notice" when they co-write a song. The "Copyright Notice" in my sample database (see attached database) is putting the songwriter names in alphabetical order. I would like the order of songwriter names listed in the "Copyright Notice" to follow the order (top to bottom) listed in the portal that is above the blue "Copyright Notice" area. Also, in the "Copyright Notice" is the list of publishers (owners) of the song. This left to right list of publishers needs to be listed in corresponding order with the particular songwriter that the publisher represents. TheEgoFactor.fp7.zip

Hi, i'm trying to make a text function that will take the contents of a text field and return a sort of summarised version.. TextA = 2 x Take Away Old Appliance @ £15 each 1 x Take Away Old Appliance @ £15 each 2 x Take Away Old Appliance @ £30 each 1 x Take Away Old Appliance @ £25 each 3 x Take Away Old Appliance @ £40 each 2 x Take Away Old Appliance @ £40 each And im trying to make TextB = 3 x Take Away Old Appliance @ £15 each 2 x Take Away Old Appliance @ £30 each 1 x Take Away Old Appliance @ £25 each 5 x Take Away Old Appliance @ £40 each Can this be done?

I have a simple sales order solution that uses a line items table which contains Fields for part number, description, price and so on. So far so good. Some line items that are added to the sales order do not contain part numbers. So I want to create an item number calculation field that will only sequentially Number line items that contain parts. I have accomplished this using a script But a calculation field would be a cleaner way of doing it, I just can’t get the Calculation quite right. Any help would be greatly appreciated. Thank You Anthony

I have a table with a text field [Field_B] containing values between 00001 and 01000. In a script I am performing a find to restrict the recordset to only include records containing certain criteria in another field [Field_A]. After this is complete I then am trying to use the MAX function to return the highest value in Field B that remains in the recordset. However it seems to be picking the lowest number. For example. this is what I have left after a Find Field_A Field_B A 00250 A 00375 A 00400 MAX (table::Field_: returns 00250 Any ideas where to look?

Hi all friends Do you think ( or know ) if global container fields have a path ? Something like: Get( FilePath ) & "/" & gContainer I know that this is a strange idea but nevertheless I would ask.

I have FM10 Advanced and I extended the sample inventory that comes with this version and included several other fields. I'm quite happy with this except when it comes to inventory on hand. You see I have inventory on hand (being the amount we purchased) The next three fields are for the three locations the stock will be sent to. So field 1 shows the total stock purchased: Field 2 shows the amount of stock in warehouse A and deducts from the total field, Field 3 shows the amount of stock in warehouse B and deducts from the total field and so on. My problem is..... when I make a sale from one of the warehouses how do I get that field to deduct it from the total …

Hello all, I am very new to Filemaker and trying to work my way through self study and exploration of FMP. Ive been putting together a small database that tracks income and expenses. There is one table for all transactions. There is a second table that defines the monthly periods. I am trying to get a list view where the period record pulls in only the transactions for that timeframe. I cannot seem to get it working. I am attaching my fp7 file in hopes someone can look at it and point out the error of my ways. Im sure its something basic, but being new to FMP has me doing alot of headscratching! Tabbed_Template.zip

how do i require a character/s in a field? like for example the @ sign on my email field and http:// on my web field. thank you

I have a field that isn't recalculating: Field type; Date Auto Enter Calculation: Get (CurrentDate) Storage: None (Auto create....) Commit record and Refresh Window, flush doesn't cause recalculation either. The field is a date field as opposed to a calc field because I am doing reporting using the date.

Hi Im trying to build a solution for my company to use and I would like to have the facility to be able to create a record that enables me to select colours that appear on a job from a Pantone library. I work in the graphics industry and each new record ( job ) I book in will require the user to pick anything from 1 to six colours. I would like to build in a relationship with a Pantone database that I can just type the code ie 1905C and then have it fill in the rest and include a sample of its colour. any ideas?

Hi gang! New to FileMaker and am working on a Contact database. I have built a CONTACT table and an activity table with a relationship of ACTIVITY to CONTACT. There are various different types of activity (email, phone call etc) that are stored as records in the ACTIVITY table. What I am trying to accomplish is a field that shows how many times a particular activity has been attempted with a particular contact. So if I pull up Sally I can see in my portal to the ACTIVITY table that the last activity entry was phonecall #3 for example.. I want the value stored with the record. So if my last three activities were phone calls I would see phonecall 1, 2, a…

I am presently trying to perform some black magic with repeating fields. I want to take a repeating field and output only certain lines of it based either on an inline variable (say, Char (1) = "$") or on a corresponding repeating table with true false values. Unfortunately my knowledge of filemaker scripting is quite shaky, and lacking traditional variables with which to increment my way through the series of lines (and any condition to stop on), I am rather stuck. The first character of any repetition after the first no longer appears to be 0. Maybe my fuzziness is theoretical. If logic in a repeating field is applied only in a lump sum, then everything I am doing is un…

Is there a calculation that will take the text from one field and based on the occurrence of a certain character, delete everything before it? E.g. I have a field called Song Title. In the song title, we might have values like "Christmas Song - w/Horns", "w/Organ" or "Christmas Song". I want to create a new field that will describe what song version is for the song/record. So the new Version field (calc) would show a result of "w/Horns", "w/Organ" or "". Does anyone out there know a way to do this? Thank you:)

Ive reached a point where I have become brain dead and cannot think how to approach something, so Im turning to you guys and gals to help kick the cobwebs out of me... I have two tables: Income Expenses The income table has all the transactions of money coming in The expenses table has all the outgoing spending I am looking to do two things and am drawing a blank in my head: 1. I have an entry layout for each table. I would like to put a box showing all the records for each table. I tried a portal, but I cant seem to get that working since its not connecting to a different table, it basically needs to show the records from itself in the bo…

I am new to filemaker and am having some difficulties figuring out how to set up a calculation for transfer of data to a new layout. The salesman field has 3 employees in a drop down list. When a particular salesman has a successful sale, I would like the information (appt date, product, etc) from that layout to be transferred to a layout for just that salesman. That way I can view the results (but only when a sale is made) for each of the salesmen. Any advice on how to make this work? Thanks!

Hi all, Basic stuff here but cant make this work. Trying to get a count of invoices for each salesperson. Made a summary field called count_salesperson and in options made a count of Salesperson. I made a layout and got a unsorted list of sales people with the same count. Close but after trying 4 or 5 layouts I cant get it to count the number of invoices each person did and give me a list. I think the problem starts w my summary field being wrong. Last time i really had to play with data it was a query of the table, group by the field, and sort. Thanks for any help.

I have recently made the switch from PC to Mac and have discovered that in Filemaker the many hundreds of containers I have set up that have links to media files wont play, because they are PC-based OLE files and won't launch in Mac. I gather there is some way to automate Filemaker to get the address of those links in those containers, reattach them - which I'm guessing might solve my problem without me spending a week doing them all by hand. (Why doesn't Filemaker just have a button for this???) After a lot of searching, I've discovered the following link of advice online. I believe they are addressing my issue, but as someone who is not particularly File…

Hi all, can you help how to calculate what date it will be in a number of days after a particular date ?

I'm very new to Filemaker, but fast in learning. I've searched for how I may be able to do this, and would appreciate any advice or ideas how to: Problem to solve: I have two tables related (A & : Table B has data that may change, field X Table A, Field Y needs update to what Table B, field X is when each new record is created. Table B, Field X may or may not change over time. Table A, Field Y will over time have records added, and only should what Table B, Field X at the time of the record creation. Another way to say it: Table B, Field X starts as 1 In july, I created in Table A, four records 7/1, 7/2, 7/3 (and at the time rec…

I need to build in a feature to track a process flow based on dates. Ie, the ship date is X, lead time for tooling is Y, the production time is Z, so from these create a back-off schedule. That is the gist of it, but with about 15 to 20 more steps. How can I work with dates in calcs? Adding days, weeks, etc. all based on the end date (ship date). thanks

is it possible to have the auto-complete feature pop-up the values in an order of most frequent instead of alphabetical? I do not have it set up with a relationship. Its just by selecting auto-complete in the field definition. any help/advice much appreciated!

I imported over 16k records from an Excel spreadsheet into Filemaker 10. In Excel, the phone numbers formated as (222) 222-2222. After import, I noticed all the phone numbers are now one long string (2222222222). Is there some way to reformat existing phone numbers? The phone field is currently set as text. Any help would be appreciated. thanks.

Hi, I have been struggling with this. I have a database of items for sale. Each item can have a constant discount, so "Widget A" always gets a discount of 10%, this of course is easy to calculate: Price * Discount (.1) But what if I wanted "Widget B" to have a 10% discount from 0-100 units, 15% from 101-500 units, 20% from 500+ units? Discount becomes a dynamic variable based on Quantity. Does anyone have any tips for this kind of calculation?

Hi All, I am working on a filtered portal with a global field to select a state. I would like to include an option for "All" so I have a field that the relationship is based on with a calculation that looks like this so far xstate = Case ( xstate_select = "All" ; ? ;xstate_select ) So I need some help on how to populate the field with the list of states, either from the data or from my states value list. Actaully, now that I think of it. I would also like to create a value list of only the states I have records for. Any help on this would be appreciated as well. Thanks in advance Ron

Hi there everyone, I've got a fairly hefty production database going, for a hire company. In this database, the user creates a client record (in tbl_clients), then creates an invoice for this client (tbl_sales), importing line items from tbl_stock into a portal on the invoice layout - standard sort of thing. In addition to the client's details being on the invoice, there are venue details (name, address, postcode, etc). These were originally manually entered, but now i'd like to make it so that for a few venues that are used regularly, putting in the venue name automatically fills in the rest of the details. What's the best way of doing this? Creating a tbl_venu…

Ugh, I just don't know how to do this. I would like to create a field in which all records are displayed for 5 years prior to today's date. Please help tks Amy

Still trying to adjust - whatever happened to the Status functions? Can't get my mind around date calculations - what I need is to compare a due date to the current date, in order to identify delinquencies. In FM 6 there was a Status(Current Date) function that could be used for testing - but what is it's eqivalent in FM 7?

I'm getting a message that "Days Left" is a field that can't be found. What am I missing here? Case ( duedate - z_todaysDate > 0; "Abs ( duedate - z_todaysDate ) & "Days Left"; duedate - z_todaysDate > 0; "Abs ( duedate - z_todaysDate ) & " Days Past"; )

Im having trouble inserting a date range and need some help. I have a Word Order Due Date = 7/23/09 I created a report to see all my work orders by week so im able to get the field "work Orer due date" and turn it into Week of year which for the date of 7/23/09 is Week of year "30" Unfortunately this number 30 doesn't mean a whole lot to a lot of people here so ist here a way to get a date range based on the Work order Due date OR the Week of year? If possible i would like to have "7/20/2009-7/24/2009" OR week of "7/20/2009" Any way of doing this.. thanks

I'm guessing the answer is fairly obvious (or else I'm making this too complicated), but ... I have two fields, field1 and field2. I have another field, whichField, which uses a value list containing field1 and field2, so that the user can select a field on which to perform an operation in a script. In the script, I want to get the value of the field1 or field2 (in the active record) being referred to by whichField. If whichField is set to field1, and field1 (in the active record) contains "cat", I want to get that value. I've looked in the functions, but am either overlooking something, or am thinking about this the wrong way.

This is puzzling. I have two date Global date fields: g.start g.end Sometimes, while manipulating the 'calendar' in g.start I get this: [color:red]"The value of this field must be a valid date in the range of 1 to 4000 and should look like "12/25/2003" 1) It only happens on g.start 2) It is happening while I manipulate the calendar so how can FM NOT like what I select? 3) I can't 'make' it do it. Sometimes it does. Sometimes it doesn't. It happens about 20% of the time.

I am new to FMP and am building a db that will record stats for Scabble. I am trying to calculate the win percentage. So if i have player 1 and player 2 fields, how do i calculate the number of times player 1 score is greater than player 2 and express it as a percentage? Any help would be greatly appreciated. Cheers Mark

I've been asked to updated our companys VAT database and one of the requirements is to auto enter the current vat period. The vat period ID ranges 1-8 eg period 3 runs 1jul2009 - 30sep2009 I want the currentVatPeriod to be set to 3 as todays date is during this date range the problem is that I am really struggling to get it to work. Logically I have came up with the following Calculation currentVatPeriod = VatPeriod::VatPeriod WHERE (get(currentDate) ≥ VatPeriod::Start AND get(currentDate) ≤ VatPeriod::End) any help would be greatly appreciated Marc

I have a database of quicktime video files (all referenced, ie not embedded). I basically want to click the container field to view the teaching videos. I have set up the container field (ref video file) as a button to open a new window and view the video file - this works fine. However the viewing size of the container in the new window is fixed. I can not find anyway to vary the dimensional size of a container field based on the known video file dimension. These files vary from smaller web based video up to HD Video (The video database has 2 fields for the x & y dimension of the file called 'videox' & videoy'. Any suggestions?

I am trying to dynamically grab the the fully qualified field/table name from a exit trigger script for updating the same field in another record. I have normally use a cogitation of Get(ActiveFieldName) & Get (ActiveFieldTableName). Thought I could simplfy this by using: GetFieldName(self), but get the error message "This Function can not be used in this expression". I also tried using:$name= GetFieldName(Evaluate(Get(ActiveFieldName))) But get "?" for the value when exiting the field. What I am doing wrong here?

I have: (ORDERS) orderID relationID Name Description TotalTimeWorked (HOURS) hourID employeeID orderID [10] TimeWorked [10] Date TotalTime I want to calculate in field orders::TotalTimeWorked howmany hours the employees have worked on an order. If I use Sum(HOURS::TimeWorked) it works fine untill an employee works on 2 order in one day and it also adds the hours worked from the other order.

Hello, I am trying to sort records based on a summary field from a related table, but FM9 won't seem to do it. I have a table JOBS, a table CONTRACTORS. Each job can have one contractor. In the JOBS table, I also have a summary field TOTAL_JOBS, which is a COUNT OF JOB_ID (unique id field). I have set up another table occurence of both CONTRACTORS and JOBS, and want to view how many jobs each contractor has done, then sort them by that number. I'm in a list layout, and have all the contractors listed. Next to that, the related summary field JOBS::TOTAL_JOBS, which displays the correct number of total jobs done by each contractor. However, I can't for t…

I have looked through the entire calculation forum and have not been able to locate a solution to my question. I just moved my student database up to Filemaker 9. I thought while I was in the process of adding some new fields I would try to set up a calculation for my students who have to take medicine, to insure that the Nurse does not overlook a scheduled dispense. If I have a student who brings a medicine to school on Monday 8-10-09 and they need to take it every two days for the next 14 days. Is the a calculation I can implement to show that today is the day she has to take the medicine, and then again in two days. Also, can someone tell me how to add a…

I have a layout that I use to track time spent on certain projects. I have assigned 2 buttons, Start, and End that the user uses when signing out of a project. Each project has a unique Project Number assigned to it that a new record is created for it when a new task is being done to it and signed out. For example: Project 101 is signed out for design work by Joe, a new record for Project 101 is created, Joe hits the Start button and the current Date and Time are entered into their respective fields. When Joe's finished he hits the End button and an end time and date in field is eneterd with that current info. The problem arrises when someone does not hit the En…

I have a layout based on Stock: On that layout is related table portal based on StockTransactions. Above the portal is a global field called gOptionCommTrade. In the portal I have an 'Expenses" FIELD that calculates expenses and uses gOptionCommTrade. When I change the value of gOptionCommTrade, it changes (of course) all the previously entered Expenses amounts to reflect the new value in gOptionCommTrade. I tried to change the expenses formula: If (TransactionType="Expire"; "";contracts*Compute::gOptionCommTrade+Compute::gOptionCommission) ...so that I can 'store' the calculated value, but can't because the formula uses a Global Value. Wh…

Hi all, I am looking for a calculation to clean up duplicate data in a multi-line key. For example, what I have is: D01P09 D01P10 D01P09 D01P10 D01P09 D01P10 but what I want is: D01P09 D01P10 The number of entries that are duplicated varies. Any help is much appreciated. Thanks. -shawn

I have an issue i cant seem to find a resolution for. My database was deisnged to enter information on checks received. I created a report layout so that i can list all of the checks, grouping them by who they were from. On this layout, i have a field for check #, check amount, and is grouped by the payer. My question is, how can i create a calculation that will give me the total of the check amounts for each payer, as well as a grand total of all checks. I have tried using the total of option, but is seems to give me the amount of the check which is highlighted. thanks in advance.

ive seen it done before in FM3 years ago and i cant figure it out. Im simply looking for a script to enter a call back date 2days out automatically. ive tried everything and cant get it (// + 2) doesnt do it... lol please help

Dear all, I have a table with a field that can have the status: -Quotes -Orders -Invoice What I am trying to achieve is, if that if the status is Invoice it has to use the next number in line. So let's say the last invoice was 55, it becomes 55 + 1. I all order cases (quotes, orders) the Invoice ID field has to remain empty. I've tried many ways, but I don't how to make the calculation from the last record with a populated Invoice ID field +1. Kind Regards, Jochem

I am trying to write a script to edit a current record. I have information in the header that will not change. In the body I have a notes field that I want to be able to press a button to add a new note and you will see the information still in the header. It also would need IWP capability. at present IWP has the edit current record function in the side bar. But I will have users of the database that has no knowledge of filemaker so I'm trying to simplify. Can any one help?

I have a portal with a calculation field called Net: Case (TransactionType="Buy" ; ((Abs(stkNumShrs)*Abs(StkPriceShr))+Expenses)*-1;((Abs(stkNumShrs)*Abs(StkPriceShr))-Expenses)) This works on the portal. But, when I produce a report and put the [color:red]same fields on the report (Sub Summary Section) it produces a "?" mark. Got any ideas? Got the answer. The NET field was too narrow. Onward through the fog....

Hi all Wasn't sure if this should go here or in the import/export forum, so forgive me if it's in the wrong spot. Here's the problem, and I *think* a calculation will solve it, 'cause I've seen some pretty slick calculations to modify and format field data. I don't even know where to start though, 'cause calculations are one of my weak points! I'm migrating some data from an older database into my company's new one. In the old database, there was a field labeled "contact name" which was abused with data designating both a mother and father's first names. At least they were consistent - the data in this field is "Mo: name Fa: name". There are about 1200 record…