Calculation Engine (Define Fields)

Hi all, I am trying to set up a text substitution that can look up a matching text in another table and substitute new text from another field in that same table record. I am working with a database that contains words that should have greek unicode symbols in them, like NFκB, however they have NFkB written. I am trying to design a calculation that can match against a list of words and replace it based on that match. Right now it seems to get stuck on the first record in the table it is looking into. It won't scan through the records in that table. Thanks for any help you can offer.

hi there, sorry if there is already another topic like this, but i couldn't find it. i have one db with 3 fields ( name, start, end ) with about 40 different ranges (start-end) and another db with 2 fields (name , value). now i want to check if the value lies within the range (start -end ) and show only those who fit. anyone ?

Hi, I'm having trouble figuring out how to calculate totals for different categories. I've attached a photo to show some sample data. I need to get a separate total for the BC category and a separate total for AB category. I have a feeling I'm missing something totally obvious. Thanks in advance, Peter

Hi. I have a database with several tables that are linked, and need to display values of some fields from these various tables onto a particular layout. If the layout I am on is the same as the one that contains the field value I wish to display, then the value is correct, yet making reference to fields on layouts whose table is one other than the field being displayed then I get nothing or zero. I was thinking of setting a global variable that could pick up the value of the other table's field but dont know if it's possible to display a global variable directly onto a layout. Anybody got an idea how I could do this? Thanks in advance. Greg

I am trying to find a site that has a downloadable list, or at the very least a screen scrape-able, list of percent modifiers for calculating cost of living between major US cities. I have been to the BLS and spent a lot of time trying to find some "modifier" value list based upon standard CPI values, but I can find no such beast, and what is there is really not useful for doing the conversions. Does anybody know of a website that has a list of values one could use to perform calcs to determine the cost of living between metropolitan areas?

I'm trying to make a calculation for a field that Gets the Repetition # of a repeating field. My ultimate goal is to make odd numbered repetitions (containing a price) to be added to a subtotal field AND the even numbered repetitions (containing a receipt number) to return as 0... thus, not being amended to the subtotal field. In addition, I need the subtotals to correspond to the Expense Type field (something that I don't even know if it is possible) I can't seem to get it to work... any ideas? Can someone point me in the right direction? Please see attached file. Thanks in advance Report.zip

I am trying to track purchases against purchase orders and generate a report to show the balance left on the P.O. I also want to track actual expenses against budgeted expenses by purchase code. I have a database set up for Purchase Orders, with the assigned PO Number. A second database has the purchase records. I have repeating fields for the Purchase code, Description and Amount, with a subtotal at the bottom. My P.O. Balance Report is set up in 3 parts, Header, Body and sub-summary sorted by P.O. Number. This works well, except two of the PO's do not calculate the balance correctly. They each add in the second purchase amount field twice. I have been told I…

Ok. So this is new. I have an items table that stores information about items my warehouse has in stock. I haven't changed anything on this database in more than a week. I have the items table connected by item id to request details table. That is connected by request id to supply request table. I have a portal to the request details table on my supply request layout so I can enter the details of orders straight from there. Pretty standard ordering set up, right? I thought so. It's been working great for over a year. Yesterday when I tried to enter one of the items (out of almost 4,000 which work perfectly fine)into my order details portal, it gave me this error: …

Long time reader, first time poster. I've found this site to be extremely helpful. I'm bringing an older database back to life with up to date information. I have no problems with any of the fields on pre-existing records, but when I create a new record there are certain text fields that I can't type in. Any ideas on how to solve this? Not sure if this helps or not, but when I go into layout mode the fields I can't edit have a yellow magnifying glass next to them. The fields I can edit have a green magnifying glass. Thanks for any help!

Hello, my accounting department just tasked me with figuring out how to print a report they want. I am fairly new to FM and figured I would ask for some help. We have a list of job numbers and when we search for one we sort it by date, however, when we print it gives total hours worked on that job based on a date basis. I.E all the hours works 8/1/10. They want me to find a way to print the report so it is total hours by month. Any help would be appreciated.

Hi, I want to create a field in a table that auto-calculates based on a calculation of many related records More specifically, I have a table of Contacts (name, e-mail, etc.) and a Table with a one-to-many relationship called Events that are events relating to these people. e.g., Phone call with Fred on August 6th. I want to create a field in contacts which can be displayed in list view and will hold the date of the last event relating to the person. i.e., automatically look at all the related events, find the most recent and output the date. I want this to be updated automatically (E.g., a new event gets entered, the field changes) and work for exist…

Hi. I am working on a simple database to track transactions by date. I was planning to use each record as a date, so I have 1 field for date, 1 for type of transaction, 1 for amount of transaction and 1 for the state. Each has 15 repetitions to input the transactions we do per day. Now, I need to take this and get it to come out in a report similar to a pivot table in excel, showing the transaction dates on the left (vertical) and states on top (horizontal), so that for example, it says that on July 3rd the total transactions were for $500.00. How do I get this done? I also need totals for both the row and columns.

Im working on a calculation that references a pre-edfined date field. The if statement i am tryin to create is: if datefield < currentdate and > datefiled + 30 This is the exact calculation i have entered but it does not appear to work: TechSupport::SW PD 2 > Get ( CurrentDate) and TechSupport::SW PD 2 < TechSupport::SW PD 2 + 30 To give you some background on what i am tryin to accomplish: The date field is a maintenance date, so lets say that their maintenance expires 7/31/10. They have a 30 day grace period from the date of expiration. so i want to mark the record to show that they are in a crace period if the current date is a…

I’m running into issues with a script I’ve written that takes the contents of a field and then adds additional information to the existing contents. I am populating a single field from multiple records using the substitute and List commands. When the user puts a report up for review, a script is run that captures the contents of a field and assigns it to a variable. The script then replaces the contents of the field with a set field command using the Substitute and List commands and then tacks on the variable to the end of the contents. This works, but the problem is that every time a report is reviewed and submitted (this can happen multiple times) the conten…

I have a calculation field that takes an expiration date and subtracts if from the current system date to display the number of days left between the two The current calculation is set to: ExpDate - Get ( CurrentDate ) This is used to display the number of days left until a quote expires. What i would like to somehow do is set the field so that if the result is a negative number, display it as a 0 instead of the negative number. Is this possible?

I'm in the process of developing an Expense Report Database that is VERY similar to the preset Expense Report template of Filemaker 9... There are repeating fields that span a period of 1 week (7 days) for a total of 24 possible repetitions. I have everything set up fine and dandy... all of my calculations are working properly, the sum of fields, subtotals, totals, Rate per Km., etc... So here's the problem: There is a sudden need to include the receipt number (whether it be a series of numbers or letters) somewhere to validate the expense... but because these are calculation fields it adds the receipt numbers into the totals. Can anyone think of a wa…

I have a calculation field that needs to be rounded to two decimal places. I have tried rounding the calculation field itself as well as formatting it in a Send Mail script step, doesn't work anywhere. Calculation result is Number. Calculation field [color:blue]Round ( Sum ( Accessions::calTotalCharges ) ; 2 ) Send Mail script step [color:blue]Round ( Projects::calTotalCharges ; 2 ) Replacing the field reference with a number, it rounds fine. Can I not use ROUND with a field reference?

Hi Guys, Hopefully someone can help me out here. I am looking for a way to get a field to display the most frequent value of a field in another table which is related to it. I have a Client table which contains ClientName, ClientID, and MemberOfStaff fields. A Service Table which contains ServiceName, ClientID, and MemberOfStaff. Looking for a way for a field MemberOfStaff in the Client table to display the most frequent value in the MemberOfStaff field in the Service table when records are related by equal ClientID's. Can't figure out how to do this, hopefully someone can help!

Hi everyone! I have this little issue and couldn't find any answer in other topics. I hope you can help me. Thanks in advance! I want to compare 2 dates: the current date, and the one written by the user. I want to know if the date written by the user is more than 60 days older than the day when the user is writing. I mean, the user writes a date of a specific day (e.g. 07/15/2010). I'd want a calculation field that tells me if there are more than 60 days between the date written by the user, and the day when the user is writing (today 08/02/2010). Please let me know if my bad English isn't good enough to be clear. Thanks again!

Problem: In many different databases (all based on a common template DB I made some months ago,) suddenly FM Pro started hanging with a 30-second beach-ball pause on every attempt to create a new record. It would then continue working normally -- the pause only occurred on every new record creation. Proximate Cause: A text field I call "CreationHostIPAddress", set to Auto-Enter Calculation, as follows: Get ( SystemIPAddress ) In normal conditions, this calc populates the field with the IP address of the local machine. It had been working fine for many months. A week or two ago, I installed a software VPN client on my machine (VPNTracker 6..) It t…

I've made a check box for yes or no on my report layout. The people using this particular layout currently have just a guest account because I only wanted them to view the reports. Now I want them to be able to click a yes or no box. Is there a way to just give them access to one item?

How to convert number to Date Exaple; total number =5968 to 17 year,2 month, 0 day thanks

Is there a way to calculate the number of records on the last page in preview mode. Thank You Anthony

I have a portal that has three fields. Date Class Period Time Difference I am trying to write a script that will look at the 10th row, this being the 10th tardy the student recieved and have it email me that this student has received his/her 10th tardy. Of course this has to work for 2500 students. I looked at go to portal row but not sure what to do from here.

Complex nesting help needed badly scenario I have 4 sizes in inches in 4 fields named len1,len2,len3,len4 I need to get the material yeild from this, the board sizes is in 2 feet increments up to 12 feet max for ease I convert all the inches to 1/16th inches so 48 inches = 768 and ofcourse my max board size is 2304 I need to know out of 4 pieces which can feet out of a 12 foot board and which needs additional material without providing waste so the field I have are "field name" "sample Value in that field" len1 48 len2 36 len3 37 len4 48 I convert them and combine them in all 15 differnt ways in fields named "…

how can a lock a field that can not be edited in a time that field "a" has a parameter "lock"?

Hello I have a report that runs an aging report for our invoices. In the report it shows a current (less than 30 days), 30 days, 60 days and 90 days past due with each timeline totaled by a summary of the total due and then a trailing grand summary at the end. I need to display a percentage of the total due for each timeline, i.e. a calculation of the amount 30 days past due divided by the total grand summary of all * 100 to get the percentage of the total due for each. Is there a way to do this? Thanks in advance!

Interesting last two: Evaluate ( "2 + 1" ) = 3 Evaluate ( "- 2 + 1" ) = -1 Evaluate ( "+ + + 2 + + + 1" ) = 3 Evaluate ( "+ + + 2 + - + 1" ) = 1

Okay, so I've looked through various questions and can't really find the answer to mine. The problem though is that I'm not fully literate in FM-speak so please bear with me. I have a business where I sell different items. I've set up a database to record all the customer info including the items sold. Here's what I'm having trouble figuring out: When a customer buys 3 of Item A and 2 of Item B, I want to be able to indicate the quantity next to that item. However, I can't figure out how to get a list of the 50 items that I sell into a field. All I can think of to do is to have an individual field name for each item that I could put the quantity into. I'm sure ther…

Im working on scripting a report and i want to perform a find on a specific field to include a date range. Im having some difficulties on how to perform the find to include a date range in the calculation. Basically what i am trying to do is pull the report to include everything for the next 3 days: Get ( CurrentDate ) ... Get ( CurrentDate ) + 3 Entering this calculation appears invalid. What would be the proper way to calculate this? Thanks

Total Newbie!!! First post, up all hours going in circles. I have to put out a book summarizing results from a specialised dog show. I am trying to create fields that would report one result for a specified group. Not sure if I should post this in calculations or scripts, don't have a clue if I am even on the right track. I have 2 related tables, the only relationship I have set is Entry#. 1) Entry info - w/fields like name, color, sex, etc 2) Evaluation results - w/fields like height, muzzle length, coat quality, etc. I am trying to put together a statistics page, summarizing field results form the 106 entered dogs. For example, I want to determine the average …

Is there anything in the new version for Filemaker to find the Jpeg if the original Jpeg was moved and it was store in a filemaker record as a reference? Is there maybe a plugin or something that can find the jpeg and restore the link?

I have a printing layout that I need a timestamp done automatically. When the user is on the student record and presses the "Print" button I have a layout that prints certain information. I have a calculated field that automatically puts a timestamp on the printed form. But I noticed that when it puts a time on a printed form. That time stays on any other form I print. It other words the time does not change with the clock. Here is what I have. Field = Timestamp Result = calculation The calculaiton is: Get(currenttimestamp)

Hi to everybody! I'm new here in this forum and, furthermore, my mother language isn't English so I apology for any mistake :-) Here below my question. Small network of 5 Macintosh computers with OS from 10.4 to 10.6; one computer has 10.6 Server. Filemaker 5.5 and a file (.fp5 obviously) with a container graphic field linking to .jpg files. Both file and folders with the .jpg are located in the computer with 10.6 Server. It happens that the very 1st who opens this file can see properly the images for all the records, the linking from the container fields works fine. The 2nd who opens the file (both by doubleclicking the icon or via the "hos…

I have a database that I need to print to forms, that are sorted according to three fields. ie Company Name, Rep Name, Client Name, where each company has a number of Reps and each rep has a number of Clients. Each form will have a printed list of the Clients for each Rep and the Company Name and will be numbered sequentially, I need the number to restart its count when the Company name changes. Any ideas?

In our contact-management layout, I've created a tab that displays a contact's giving history (in a portal with related data from a table with donation records), plus a few statistics - how many donations the contact has made, average and total amounts, date of most recent gift, etc. Is there a way to make a calculated field that will display stats for the last 12 months and/or year-to-date? Thanks!

The database im working on has a primary ID and a secondary ID. The primary is a phone number while the secondary is the clients name. The secondary ID needs to be in the following format: Last First I currently have a field for the clients name that i would like to copy from, but how would i copy the name and reverse the first and last names?

Hi Experts, I was just playing through the MAX function according to file maker Pro 11 advanced help. ( for repeating fields But it seem that there is a bug in the function. Please see attachment. Please confirm this. What can be the get around solution? Regards SY Lim

Hello everyone, I inherited a database with a field that included the names of contacts in this format: "LastName, FirstName" I know there has to be a simple calculation to reverse that to "FirstName LastName", but I cannot figure this out. Please help. Thanks.

I work in the music business. Over the years songwriters have emailed their lyrics to me. I have copied and pasted these lyrics into a regular text field in my database. After I get the text into the field, I change most of the text to UPPERCASE. If, at a later date, I copy this text out of the field and paste it into the body of an outgoing email, my UPPERCASE adjustments to the lyric are gone ... the lyric reverts back to its original format. This does not happen if I copy the lyric from my database into a Word Document. Any thoughts on what I'm doing wrong? Please don't tell me that I need to go through every record in my database and manually change something ;-)

From FM9 to FM11 I have manually assigned a rank after sorting sports carnival results for each event. Is there a way of automating the rank once a results is entered in a time/distance field in the rank field? Can it also trigger a predefined lookup script for rank results? Scripting I must admit is my weakness - single step scripts are fine.

I have had a look at Install OnTimer Script and notice that is calculates in second(s) intervals to make use of it. I want to write a script (like a cron job) to enable a particular (existing) script to run every Monday morning at one minute past midnight. (not every seven days, but specifically Monday 00:01:00). Can you help me with this please?

I have a database, see attached, that has two tables one table called "inventory" for items that we have in our inventory, currently about 400,000 records. The other table called "bad" has items that we want to remove from our inventory. The only field that we can use is the items name but as the item names are just a bit different in each table there are no exact matches. If you see our current setup there are about 1/5 of the items it catches but the rest are just not exactly the same. Is there any way to pattern match the words from one table to another and based on a percentage of matched words or make some type of way to remove these few hundred items from the larger…

Hi all: I am working with fp5, and I have a customer database that also manages contact events. I have the file customers related to a contactevents file. Customers are searched and if contact is needed then the type of contact is put into the field called Tasks. Since a customer can have multiple types of contacts from searching, each contact task is put on a line in the field. For example, customer1 has multiple contact tasks. The Tasks field has these values separated by a return: New Customer Upcoming Event Here is where the problem comes. If a better way is out there I'd love to use it instead of this. The user enters the results of the contact …

I have a repeating container field that is used on my layouts to "highlight" whichever layout the user is on (the change layout script sets a graphic into the relevant repetition). I have attached a small file showing the setup. The repetitions are cleared at the start of every change layout script (there are only 3 layouts at present so this is done by 3 steps, clearing each of the 3 repetitions individually) before the new layout highlight repetition is set. The number of layouts in the file will need to increase and I don't want to keep adding individual steps to clear each repetition in turn. I have tried to do this in one script step using Extend and G…

I'm trying to extract DOE, JOHN B & JANE C/ DOE JOHN B JANE C into these fields. First Name: JOHN B Last Name: DOE Spouse Name: JANE C I have tried many different calculations, and I'm only successful with the last name and the spouse name (first word and last word) I don't seem to be able to make the First Name (middle words) to work. Any suggestions?

Hi All, I've just joined as my database use has went up somewhat. I am writing a database for a music project and I'm having problems with calculations on texts. I have one field chord 'chord in c tonic' which will contain a chord in text description (with music symbols from ASCII char set) like Cm(add 9)/E♭ I want to transpose this chord in C tonic and present it in different tonics i.e. the same chord in D, E, F tonic (aka musical transposition). So the chord Cmin(add 9)/E♭ would be D♭min(add 9)/E in D♭ tonic ...and so on for all the other notes of the chromatic scale. I thought the best way to achieve this would be have …

I might be being a bit dumb asking this question but I’m totally stumped and would appreciate some help. I have a table which is made up of event records. Then I have a report layout which shows a lot of numerical information about these events, with a summary for each field. Let me show you a simple example Event 1 (field 1 = 10) Event 2 (field 1 = 9) Event 3 (field 1 = 20,) Trailing summary - (Summary of field 1 = 39) So for example I have 45 events each month. Each event record has a field that gives me ‘Total Attendees’ for that specific event and another for date of the event. So the layout above would list the events in any selected month…

I've yet to understand how to handle calcs involving a timestamp. In the current situation I need to fetch the current timestamp and then add 5 minutes to it. Much appreciated.

Looking for quick help. I've set up the following fields: DAYS OUTSTANDING (this returns a calculated number result based on the number of days since the booking date). late fee 1 = 22 late fee 2 = 33 late fee 3 = 66. NOW: = Late Fee Calculated (this field should display the relevant late fee for a particular record, based on the number displayed in the DAYS OUTSTANDING field). I am trying: Case ( DAYS OUTSTANDING >14<31 ; late fee 1; DAYS OUTSTANDING >30<61 ; late fee 2; DAYS OUTSTANDING >60<91 ; late fee 3; ) =================================== If ( DAYS OUTSTANDING >14<31 ; late fee 1; …

Hi everyone, I am building up a database which stores products with all corresponding prices in Euros and USD. The data is imported from an external Excel file, from two usd and eur price columns. The case is, some providers offer their prices in euros, some of them in dollars. Thus, every time there is a filled USD field, the corresponding EUR field for the record is empty, and viceversa. I need to calculate the missing price with a fixed exchange rate. /* Originally I wanted to make a global dropdown to show "all prices in euros"/"all prices in dollars" but came into much trouble on my way, so I decided to simplify the thing display both prices on the same …

I have a text block field which contains lines of text separated by carriage returns and I was wondering if it was possible to remove all carriage returns that do not contain any text on a line. Thank You for any thoughts on the matter.

I am using a serial plugin to receive data into a field, I need to manipulate this data so that I get the most recently received reading each time. The data feeds into a holding field, when it populates into the field it appears as either 1 reading, 2 readings or 3 readings at a time with the newest one being the last one to appear in the field. Each reading has a leading and ending identifier (I can't enter them here as the character set isn't supported). The data looks like this: 3.156G 4.541G The leading identifier, 3 spaces, THE DATA I WANT, a G and an ending identifier. Going by the above, my end result would be "4.541" Can an…

I am trying to create a variable that contains a file path that is based upon an employer name. The problem is that occasionally an employer name will contain a "/" in it and I cannot save records as a PDF as it results in an invalid file path. I'm trying to find a way to set my file path variable to account for this but I haven't been able to wrap my head around using the substitute command to do this. I'm able to successfully change the "/" to a "-" but I can't get the calculation to retain the rest of my employer name, just to parts that I replace. So basically, I want this to take; "Barts Beeswax/Honey" and change it to "Barts Beeswax-Honey" any time an e…

Hi all I have 4000 records of steel in a separate table . Each steel is assign a grade and p number. The problem is that some steel p number changes with the thickness of steel changes. Example A36 steel is a p number 1 when the steel is less than or equal .750" in thickness But becomes a p number 2 when over .750" in thickness. User of the welding program enters the steel grade and the p number field lookup the p number. everything is good when the steel grade has only one p number but when the steel has more than one p number because of thickness the program has a chance of not being accurate. I tried a separate calculation field to populated the p number …

Hi All, I'm looking to have filemaker automatically place a decimal point so that if the user puts in the following information, it results in the following number formats: 35 = 0.35 350 = 3.50 3500 = 35.00 35000 = 350.00 I'm sure that there is an easy way to do this and hoping someone will help me out.

Dear mates Sometimes, I feel like I have to learn FM again, and I am happy to (try to) do that. I have invoices, with the gross amount and date (amongst other values). I have the invoices linked to the addresses. I can show the sum af all invoices. Now I want to split that up on the last five years. What would you recommend to use? I know how to do it "old style" with a script which feeds five different cells. Is that still the way to go? Cheers

Hi, I'm trying to built something that can give not only give me total number of carton, but also to create lines based on a max number per record.. the goal is this. I have to put 10 pieces per box, so if the qty is greater than 10 i need the db to create as many lines as needed. For example for the first line i would just have 1 box of 3. for the second line of 17 i would have 2 boxes (1 with 10 and the 2nd one with 7), for the third line i would have 5 boxes (4 with 10 and the last box with 3) Qty Catalog # 3 427027 17 427028 43 427029 This at the end would create a packing list. the maximum pieces per box are 10 for a single c…

I've searched and found many topics on this forum, but I didn't find any that address my question yet. My apologies if this has been answered many times before. I'm using two timestamp fields to "start" and "end" a job. I then calculate the time elapsed as such: start (Timestamp field set by a button & script) end (same) c_time_elapsed = If ( IsEmpty ( end ) ; Get ( CurrentTime ) ; end - start ) Calculation result is Time My problem: Sometimes c_time_elapsed shows up with an AM or PM attached to it. And, other times it shows up with a 12 hour offset. I don't understand that, but it throws off the subtraction. For instance: (th…

I apologize if this post is in the wrong section, but I think it belongs here. I am creating a solution to be used for a class sign up schedule where students will come to me to sign up for classes which I input into the solution. The problem is I am having to manually keep track of how many students are currently signed up for each class so I don't overload them. I have been trying in vain to figure out a way to have a field(/portal?) that shows me all the available classes, and how many students are currently signed up for them. This field would have to constantly update itself every time I add a student to a class. I'm pretty new to Filemaker, and this problem ha…

I could not find a subforum to post a question about container fields so I think it should go here. I have a file that is shared on a network. I would like to be able to insert PDF files into a container field on one of the computers and have another computer on the network be able to open the PDF. I can see the file in the container field from both computers but I cannot figure out how to make the PDF open. How do you do this?

Greets, everybody: This should be relatively straightforward but I guess I'm not using the correct calc. What I want to do is not display the Status Toolbar on the solution I'm working on so I'm "scripting and buttoning" the bare-bones commands from there into the UI. Now the tricky part: the record counting. In the attached .png example, I'm using Get(FoundCount) and Get(TotalRecordCount) for the "5/81" part...but what about the "3"? What would be the calc so as a user steps through a found set a field/calc would display the record number of the found set? I tried Get(RecordNumber) but that gives the "Show All" record number, not the record number in the f…

Hi I have a calculation field returning text of several fields and I would like to add a star rating to the end of the list of text. I have a field called STARS which has a number (1-5 entered), how can I force the calculation to return an asterisk for the number of stars equivalent to the number in the STARS field ??

Hopefully this is an easy fix! END GOAL: Two separate date fields (one is imported) need to match. If match, the calc will say 'MATCH', if not it will be blank. PROBLEM: The imported dates come from a SQL database from somewhere else and don't have a leading zero (0) for the month, whereas in filemaker it does include a leading 0 (e.g. 01 compared to 1) CALC: This is what I have so far, and it works fine on 2 character months, but won't on the ones without leading 0's. Any ways to fix this issue? Case ( Imported_CertDate_MOPR = Training History::Training Date History;"MATCH" ) "Imported_CertDate_MOPR" is the imported dates that DO …

I'm using FileMaker Pro 11. My application manages equipment rentals. The items available for rental are represented by table InventoryItems. The rental invoices are represented by table Invoices. The line items for each rental are represented by table InvoiceItems. A simplified view of the structure: InventoryItems: InventoryItemID Name Quantity Invoices InvoiceID CustomerName StartDate and Time EndDate and Time InvoiceItems InvoiceItemID InvoiceID InventoryItemID QuantityNeeded The problem I have is that I need to know, for a given date, what inventory is available for rental. In other words, given a date, I want to be…

The end result of what I'm doing is to create a trend line for a graph that shows how many records were created by week for a specified date range. I've been able to create the summary fields and breaks to show a chart by week and cumulative. I know how to calculate a trend line. But the problem is coming up with a way to calculate this trend within the FileMaker realm. The problem I'm having is finding out what the average is for each summary part. Each summary break is divided by week of the year and sorted by this number. As far as creating a calculation/summary is concerned, how can I know which summary I'm currently on? I suppose I could script t…

Hi, I work for a business and we are setting up our database. We created all of our records but somehow duplicates for every record were made. Now it seems that the best way to eliminate this problem is to create a script based on each record's Unique ID. Unfortunately we did not create any Unique ID's when we built the database. I went in and created a new field to be Auto-Enter with a Serial number but it did not generate serial numbers for the current records (the field is there, but it's empty). How can I have this be retroactive for all records in the database? Or, is there an easier way to get rid of multiple records that doesn't involve deleting them…

I have a simple solution with a few fields that are setup with field validation. The validations are that the field must contain a value from a specified value list. However, I am allowing the user to overide this validation, as it may be necessary to continue completeing the record at that time. I would like to either flag that record at the time of the validation error, or run a script later over a series of records looking for the records that may have had a validation error. My intention then, is to update a value list if necessary, to allow that entry, if it should come up again later or correct the user in who is inputing the wrong information Any ideas…

Hey Guys, I need a hand with a calculation? I have a situation where I am keeping an inventory log. Inventory is defined by its barcode, then there are different lots of each barcode. I was able to make a calculation/relationship that would keep a running total of the reagent on hand for each lot of reagent. (highlighted in blue) what I would like though is a field that will have the total of the reagent at the time of entry of that record. So instead of saying 424 in the last column, which is the total of that particular lot in stock, line 2 would say 150 and remain that way. I tried doing it in lookup, calculation and a calculated value. Thing is …

Hi, My layout consists of 5 indexed tables: charge1 charge2 charge3 charge4 charge5 I'm looking for calculation help with my count calculation: Charge1 = "Assault" I would like it to function to add charge 1-5 the C_summary is : AssaultCount Summary = Total of c_Assault

Can anyone suggest a way through my brick wall. I am trying to append the above abbreviations to a date so far I have: If (Day ( IssueDate ) = 1 or 21 or 31; "st" ;"" ) If the date is 23rd the result is 23st which not what I expected, I have tried everything I can think of maybe I am missing something obvious.

I cant seem to get a caculation to work. I have attached my relationship graph with this post. My problem is that on a layout based on Artworks I am able to show AcceptedArtistDetails::cCommissionDec and can also enter CommissionDetails::RequestedAmount which is entered through a related field on the layout. The Requested amount is used in a calculation which is a field within the Artworks table; all works fine. However I have tried to show the result of CommissionDetails::cCommission who's formula is RequestedAmount * AcceptedArtistDetails::cCommissionDec and nothing is displayed. I have tried storing the CommissionDetails::cCommission calculation as unstored a…

Hi All, I wonder if anyone can help me. I've tried to create a function that extracts a filename off the end of a filepath on a mac of the format: "RAID:Thisfolder:anotherfolder:Thefilename.pdf" I thought this should be relatively easy - using a RIGHT function, and using Position to find the last ":". But Position returns the wrong position count, and I can't see why. Any ideas? Richard.

Hello, I have been trying to make a simple layout to perform some basic calculations, but I encountered some troubles with the IF Function, I wonder if somebody can help me out? That's what I wanted to achieve. I have let's say 3 different debts (1000, 2000, 10000) for each debt I know I got to pay a monthly rate of let's say (500, 1000, 1000) I managed thru repetition fields to get a layout displaying the various months of the years and the resulting, diminishing total debt 1,2,3 something like: This part I managed. What I didn't manage is the IF function in the "Total to pay each month" Results should be like: The possibly i…

Hi all! I'm returning to FM after having used it years ago in a more rudimentary manner. But I've hit a baffling roadblock regarding date formats. The trouble seems to be that slashes in my dates are being removed, causing an error message in FMP. This show up even when I run the tutorial. When, as directed, I try a date-range search of "1/1/2009...6/30/2009", I get an error: "The provided find criteria is not valid. Enter a valid request before proceeding." And, at that point, the slashes have been removed from my entered text, so it now shows "1 1 2009...6 30 2009". Even when I add slashes by hand, though.... same error. In my own database proje…

FMS 11 Adv FMP 11 Adv noticed this in the server sample file in the adjust window script in a calc it's there between IsValidExpression ( expression ) and KanaHankaku ( text ) it is not listed in calc functions on a local database , only one served, let me correct that, it is only in the fm sample database, uploading a homegrown db does not make IsWeb available in the functions list FM Help search returns no results, and it's not listed under functions fmp11 users guide search returns no results FileMaker® Pro 11 Advanced Development Guide search returns no results found no reference on the web also no results searching: FileMaker® Ser…

I don't really know how to ask the question so I can't find the answer that is surely very obvious. I have a field that is a number, and as that number goes over 50 and approaches 100 I would like a calculation to return a number that goes from 48 down to 24. Can you help? Thank you.

Hi, how do I reference images from a shared hosting server. Images are stored in a container field named imagecontainer. I created a calc field named testcontainer with "Calculation result as Container" and inserted in the calculation: Substitute ( imagecontainer ; "imagemac:/GV/media_database" ; "http://myserver.net/media_database" ) to rewrite the path to my hosting server where the image is located. The URL the field generates is correct. For testing purposes I created a calc field named testpath with "Result as Text" to output the path of my image container (field: testcontainer). If I right-click on the field testpath and choose "Open http://myserver.net/images/ima…

Schedule table as follows; Date Name Time -- entered military style, for example, 0900, 0930, etc. Duration -- either "1" for 30 minutes; "2" for 60 minutes. Time II -- Case (Duration = "2";Time ( Left ( Time ; 2 ) ; Right ( Time ; 2 ) + 30 ; 0 ); "") .... so, for example, if Time = 0900 and duration is "2", "0930", etc. -- "0930" and is formated by hhmm, 24 hour, zero before hour, no "separator", etc. to match the format of Time. Validation for Name required: Name value for a pre-existing record with same Time/Date is not used for a new record at the same Time/Date; or Name value for a pre-existing record with Time II/Date is not used for…

I have a picture of a class of students. I would like to have a record with the students name and have a square object around the persons face to that record. I am just wondering if there is some way to do this in filemaker. I would like to load the class picture in the background and have coordinates which move the object for each record so I can clearly show the persons face that match the name in the name field, Is this possible with a plug in or within filemaker 10 somehow.

Hi, All I want to do is summarize the numbers in one field of a found set. When a user clicks a certain button on one of my layouts, a script is executed that: 1. Goes to another layout--a Table View only layout 2. Sorts records by date This new layout has a column called "Payment Amount" with all number values. I added a Summary field to the table called "Total" whose options are set to Total the Payment Amount. But when I add a Trailing Grand Summary to the layout and put the Total field in it, it doesn't work at all. In browse mode, it just says "Total." I feel like it should be very simple to summarize the values of one field in a given found se…

I am trying to validate two fields that are foreign keys to a parent record. Each field is dependent upon a name field to have a value and if the validation fails then I want to use the auto-enter calculation replaces existing result feature to wipe the value of the foreign key value. The problem is that one of the fields is working perfectly and the other refuses to work at all. The most frustrating part of this fact is that both are extremely simple relationships and are not working as intended. First calculated field; Field Name: _kf_DepartmentContractID Calculated value: If (IsEmpty(E_000141_Department_Contacts);"") E_000141_Department_Contacts is…

Hello, I have been trying (unsuccessfully) to calculate a portion of an existing field into a new field in FM9 PRO. I have tried Replace, Substitute and even Trim to complete it, but since I am new to scripting I have not been able to get the desired result. I have a field that contains a part number (3M-1234; DWT-CP-3429; ACM-00-98-A) and I want the calculated result of the new field to just be the characters before the first "-" (3M; DWT; ACM). Is it possible to extract that? If so, how? Any help would be appreciated. Thanks.

This is likely going to be a very rudimentary sounding question, but here goes..... Is there a quick and efficient way of determining the repetition number of the next available(empty) repetition in a repeating field? I'd settle for being able to determine the number of repetitions a field is defined to have. I make a point of not using repeating fields for the most part, but they do have their uses.

Is there a simple way to add, say 30 minutes to a specific time -- for example, for the value "9:30" the calc creates "10:00"?

Anyone aware of a way of getting a calculation field to always reflect a summary of the record IDs of the current found count? Meaning, if the Record ID fields of my current found set are 4, 7 and 9 (three records), can I create a calculation that shows: 4 7 9 ?? My overall goal is to create a relationship to show a portal of whatever the found count is without having to script in my own Find process that always sets a global to the record IDs of the current found set. The new List function doesn't seem to be of much help here. Thanks

Hi guys, I've designed a punch card solution in FM10 to help employees clock in and out. I have a calculation field (Hours_work) that outputs the payable work hours based information collected in a weekly report. If no clock-in time and/or clock-out time is registered, I want the Hours_work field to output 0. How do I do that? Also, does anyone see any issues with my script steps below?. Here's the script: Round( (Hour( ClockOut_time - ClockIn_time )*60 + Minute( ClockOut_time - ClockIn_time)) / 60 - Hours_lunch; 2) + If( GetAsNumber(ClockOut_time) <= GetAsNumber(ClockIn_time); 24; 0) Round( (Hour( ClockOut_time - ClockI…

I have a field that is currently set to Automatically Enter the Modification time of the record. Is it possible to have the field pull the modification time only once? I am trying to calculate the start and end time of a note by using an auto enter creation and modification fields. The problem is that if at any time the user goes back to edit a note, the original modification time will be overwritten and results in an inaccurate time. Thanks in Advance

Okay, Robin, one of my colleagues, pointed this one out to me. I’ve never run across it before, because…because I’m perfect, and never mistype in the calc engine! Yeah…yeah, that’s it. Anyway, to reproduce: 1.open up the calc engine (Data Viewer will do) 2. Add an expression to the Watch pane, and copy/paste this into the calc dialog: $test $test $test $test $test $test $test 3. Click on Monitor 4. Scratch your head in befuddlement. You’d think FM would bark once you try to commit this expression, since there aren’t any operators…but no. I’ve monkeyed with the contents of the variable to see if anything useful can be done…

Hello I have a new problem to solve and I'm not to sure how. I have a table that shows Percentage of accomplishment for a type of job. The same job can be accomplished over several weeks and I need to find the percentage between the two last entries. example of the data entry Date-------- Job % 28-05-2010 15% 04-06-2010 75% 18-06-2010 100% How can I calculate the difference in percentage from the last two entries (result being 25%) and ignore the previous week(s)? Keep in mind that there can be several weeks between two entry. I'd prefer to have a calculation field for this but it can also be integrated into a script. I will use this data to…

Hey Guys, I am trying to set up a catalog system that groups sheet music based on the following criteria: 1) Category (Concert, Marches, Christmas, Jazz Band) 2) Grade (anything from 0.5 to 5.0 in increments of .5) 3) A sequential 3 digit number within each Category and Grade To keep like categories and grades organized on the shelf, I would like to auto create a Catalog No. that is formatted as XX-00-000 whereas the first two letters are the first two letters of the category, the 2nd two numbers are the grade (multiplied x 10 to remove the decimal) and the last three digits is automatically generated after Category and Grade are selected, so that each…

Is it possible to define the autoenter option for a field like this: Evaluate("$$Error & Self";$$Error) in order to make an Error Log? (Provided you have a script step in every script which captures errors to the $$Error variable)

Hello I would like to set an alarm in Filemaker but I don't always have filemaker on. Is there any plug in or cross platform capability to "Turn on Filemaker file" from an alarm to sound the alarm so to speak. Thank you

I am working on a project where I need the ASCII value for every character in the text field. For example I need to convert some text to 115 111 109 101 32 116 101 120 I found this handy custom function for getting the ASCII character value , but I'm having a hard time getting my mind around how to loop through each character in the text field in order to perform the replace. Thanks.

Hi All, I have two fields, A & B A is set to: If ( IsEmpty ( database::A); "" ; "A: " & database::A B is set to: If ( IsEmpty ( database:::; "" ; "B: " & database:: Works well, if noting is found in A then no result is show and the same for B. I need to be able to make so if a records is found in B then A is not shown regardless of whether it has data or not This cal is beyond me so I'm asking for your help Thanks Tony

Hi, Is there a simple way to round up numbers to next x,0, x,25, x,50 or x,75? For example, 16,58 would become 16,75. Thanks in advance, Jari V.

Hello I need to bring the percentage of achievement of a task in a related table and I just can't find the way. I have a table in which I store the % of achievement of a particular task. The % is updated every week until is reach 100%. So I'm looking for a way to bring the last entry (closer from today's date) % in a related table. So far I only managed to get the first entry whatever the date is. Any idea? Thank you

Anyone know how to get date of the 1st of current month? For example, todays date is 06/15/2010. I want calc. to produce date of 06/01/2010. Thank you!

Hello, I would appreciate any help with this. I have a script trigger, onObjectExit, for a text field on my layout. This field is a dependency for an unstored calculation field on a related table occurrence. So when the user is leaving this field my script triggers and I attempt to get the value of the related unstored calculation field but the value has not been updated yet. What is strange is that I have a text field on this same layout that is displaying this same calculation field and it is updated right before my script executes - I know that by running the script debugger. Is there a reason the calculated field would show as updated on my layout but no…

Hi all. I am trying to populate a field using a value list. Can anyone help?