Calculation Engine (Define Fields)

I have records that I have a recorded result in a field "food" As I would take in orders they would label the sale of a food with the following "Hamburger" "Cheeseburger" "Hot Dog" "Grilled Cheese" "Philly Steak" I would like to have a real time graph constantly counting each Item and graphing it is some way (always updating ) within the record. And continuing to do so as we add records. How do I do this with a calculation? Count the number of Hamburger cheeseburger, hot dog, Grilled Cheese, philly steak order and graphing the percentage. I can understand how to graph in filemaker but I dont' know how to calculate for the pie chart? How can I calculate the perc…

Hello, I need to do the following within a calculation field: If (Field = "Current"; Left(Name; 7) ; ) But it doesn't seem to work. Can anyone help me out? Thanks, Scott

seems simple ... There's week of year function,, How do I get get Week Of Month ? thank you

Hello, I am tracking my equity profits vs Share market index and I want to calculate the percentage rise in a year in both the profits and the share market index. Usually I enter the realised, unrealised profits in my database at the end of the day (though not daily, fairly regularly) and I also enter the index value in the database. I want to use this data to calculate the percentage rise in my realised, unrealised profits and the index over the year in a report format so that I can have it every year. I am just lost for ideas to make filemaker select the first and last values for the year. Any help is much appreciated Thanks Sanjay

I have buit the following calculation to compare free text with a library of key words, and count the occurrence of the library words in the free text. This works great, but....... The library lists for some words are stems, like "happi" to capture "happier" and "happiness". The solution below works fine for exact matches between target text words and whole(non stem) library words. Is there a way I can capture the partial stem matches, either within the framework I have or in some other way. Text samples will run up to 1,000 words, and libraries may have as many as 500 words. Let([ t = text1 ; r = search_library::sad ; adj_t = Substitute ( t ; ", " ; ¶ ) ; adj_t …

Table A and B are related. In Table B, I would like to see a count of records related from Table A, based on criteria in an unstored calculation field, such as TableA::Status ("Sold", "Available", etc.). In other words, in Table B I would see a count of sold records (related from Table A), available records (related from Table A), etc. Available: 6 Sold: 5 Total: 11 The Total is easy: for example Count (TableA::Record ID) However, I can't build a relationship based on an unstored calculation, such as TableA::Status - so what do I do?? If anyhow possible, I would like to avoid calculation fields in Table A, because there's a lot of records. If I have to add …

I'm sure this is super simple but I'm not sure how to consistently incorporate glyph fonts and regular fonts in the same calc field. In the past I've incorporate Winding arrows with a regular font like Helvetica, but I've been having problems incorporating other Wingding symbols. For instance, when I try to include the darkened enclosed circle containing the number "1" from Wingdings 2, the calc field displays a circle with an "x" inside. When I look at the character code for Wingdings 2, the glyph I want is represented by the number 70 at the top and 5 on the side. The glyph that shows up is 50/5 respectively. I could just change the default text in the calc fie…

Hi all, Can the results of a field be placed in a custom message, I can't find any reference to it. Thanks

I like to archive my projects to my yahoo email account. I would like to know if I could automatically send a copy of my file through a script to my email account. I would like it to automatically name the file with a name and a timestamp to always keep track of the latest version. I just want to find out if there is a way to automate doing this for my files.

I am new here but whenever I search for things in this forum. I get about a million answers that don't relate to my question. I must be doing something wrong. I just looked up changing object color and nothing came up so I am asking here. I like to know if it is possible to change the color of graphical squares and circles in filemaker that are in the layout by calc or do I just have to go to another layout? Thank you

Hello I am wondering how to make a record so the user cannot delete it if it says for example "lock" in the Field "SAVED" but if i is empty in the field "SAVED" it is okay to delete? Thanks

Hi, Let say I want to create a blog in which user can add comments. Each comment can also be commented. I want to create a field that calculates the total number of comments a blog or a comment has. Here is the tables structure Blogs (id, article, c_nbComments...) Comments (id, id_blog, id_parentComment, comment, c_nbComments, ...) Let say I have two groups of Anchor-buoy BL__BLOGS --< bl_COMMENTS CMT__COMMENTS --< cmt_COMMENTS__children So how can I calculate the total number of comments for each blog or comments? Thanks

Greetings I have a series of records which have a text field called numbers for only 4 numbers in the range from 1 to 20. I have a text field for each number 1-20 I would like to find a text function that would put each records 4 numbers into theirmatching name field. Ex. The number 8 would need to go to the field named eight The number 17 would go to the field called seventeen etc. Each record contains only 4 numbers in the field mumber I need to make those 4 numbers go to the right field (matching name) in each record. I need help in making a calculation to do this Thanks

Hi all, i'm having great trouble using get(currentdate) calculation, it outputs 734877. I'm not sure how it has came to this conclusion. The local date is Wednesday 9th Jan 2013. I'm really after the date in dd/mm/yyyy format which is what the local machine is set to. Also the filemaker i'm using is delivered via citrix, if that makes any difference. Thanks Stereophonic

Hello, I am struggling with a calculation based off a value list is FileMaker In a nutshell, I have created a value list drop down menu, in which the user can select 1,2,3,4 or 5 as a rating. I need to translate these values as shown below, in order to use for a calcualtion 1=20% 2=40% 3=60% 4=80% 5=100% What is the easiest way to do this? I have about 20 of these drop downs in my table. This would be simple enough in Excel, but I can’t seem to figure it out in FileMaker. HELP!! Thanks in Advance, Ray

I'd like to create a formula that automatically calculates overtime for each working day. As a standard week (or normal time NT) Monday to Thursday our employees work an 8.5 hour day and on Fridays they work 4 hours. We pay time and a half (overtime OT) for the first 2 hours after the normal time and double time (DT) for anything beyond that. So far in my head i have worked out that if DayOfWeek = 2-5 (Mon - Thurs), then NT is 8.5 hours, and OT is any hours > 8.5 hrs, but less than 10.5 and DT is >10.5 hours and if DayOfWeek = 6 (Fri), then DT is 4 hours, and OT is > 4 but < 6 hrs and DT is > 6 hours and finally, if DayOfWeek = 7 or 1 (S…

I am using the database to store the image files for over 50k products that we sell. Many of them products have the same images. Hence my problem. Currently I am storing the same image in different container fields (it works but takes up a lot of extra space) I could reduce the size of my database by 10x if I could have the database store just one image and then reference that image if it found a duplicate. Please help. [email protected]

Ok, I found this calculation for validating a phone number when it get entered for desired formatting--(330) 555-1234. This works. The problem is that it inserts the error message into the field (as it should according to the script...) when incorrect number of numbers (say that again) is entered--012, 012345678901, etc., so I would like to have an error dialog box pop up instead...how do I go about doing that? AS IS NOW: Case(Length(CustomerPHONE) = 7;Left(CustomerPHONE;3) & "-" & Right(CustomerPHONE;4); (Length(CustomerPHONE) = 10); "(" & Left(CustomerPHONE;3) & ") " & Middle(CustomerPHONE; 4; 3) & "-" & Right(CustomerPHONE;…

My solution needs quite a few calculations, and sometimes they get really complex. It takes quite a lot of time to 1. Go into the Manage database 2. Find correct field 3. Go to Options >> Calculated Value 4. Make a change 5. Save and go out of Manage database 6. See if it worked this time 7 Repeat X times until the result is satisfactory Is there any way to tell the calc field to update the actual calculation from a global field? A Get() function, maybe? Something like MakeACalculationBasedOn (global_field)

I often use a dialog box,,, in this case the previous time a dialog was opened wider, had more text,, It seen that when opening a dialog box the next time, etc... the box is still opened with to much white space. I found I could go Preference, and reset All Dialog box.. I be happy to execute that once in awhile.. but on a individual base it be nice to reset so user gets my text formatted nicer..

Hello I need to make a calculation that can choose a random number from 1 to 30 then place the number in a field named 1st number and then choose another number from 1 to 30 but not use the one used in the 1st number field.This would need to happen 5 times Below is the way it would be done but I don't know how to calculate it. EX. Get a random number from 1 to 30 FM picks 27 27 is placed in the field 1st number. Get a random number from 1 to 30 but doesn't use the contents of field 1st number (27) FM then picks 3 3 is placed in the field 2nd number. Get a random number from 1 to 30 but doesn't use the contents of field 1st number (27) or 2nd Number …

Hello fellow FMP programmers! When passing styled script variables I stumbled on the following effect (FileMaker Pro Advanced 12; Windows7). Paste into DataViewer: Paste onto any layout: As you can see, evaluate and quote keep the bold text style. If used in combination any style is lost. Is this intended behavior? Any work arounds? Thanks for any reply, Rewolfer.

How could I do to set a minimum size for a numerical field. I.e. a number that must always be 10 digits long, or 7... Thank you.

I have a sales report I created, and we have two summary fields that counts how many sales and adds the dollar amounts of each sale. I need to be able to create two separate versions of these summary fields, filtered by customer type (new and remodel). I thought about using a portal filter, but I can filter the same table that layout is associated with. Instead of leaving these as Summary fields, I thought that I could create them as calculation fields. But I don't know what calculation I could use, and where I should start. Any thoughts?

I hope I framed the question clearly. Basically I'm wondering if there is a way to run different scripts from a single calc field that's been turned into a button, thereby simulating multiple buttons. For example, lets say I was simulating a hierarchy inside a portal with an "expand" and "collapse" triangle. Visually reading from left to right, the calc field would show an expanded or collapsed triangle, some space, then some text. The first click expands the triangle while the second click collapses the triangle. This works fine and dandy. But lets say that I also wanted to add some command to the right of the text (in the same field), which would trigger a different…

I have created a page that reports data on our sales people. The area of the page I'm working on currently, has to do with leads and appointments from the current year and previous year. I have both of these areas working, but I want the previous year to also report up to today. For example: Jan-Dec 2012 Leads Appts 400 80 Jan-Dec 2011 300 65 But if today is December 10th, I want it to read last years up til today. Jan-December 2012 Leads Appts 400 80 Jan-December 10th 275 50 I am using a relationship and a calculated fiel…

Hopefully I'm not the only one dealing with some date issues with the turn of the new year. I have a fairly simple formula that I want to make sure will work correctly. Say I wanted to get the WeekOfYear for the date "12/30/2012", filemaker would return the numerical value of 53. WeekOfYear ( "12/30/2012" ) ≥ WeekOfYear ( "10/1/2012" ) would return True, as 53 is greater than 40. However, when the new year turns over and now we are evaluating the formula as WeekOfYear ( "1/1/2013" ) ≥ WeekOfYear ( "10/1/2012" ) the formula now returns false, as 1 is not greater than 40. My question is, if I change this formula to read Get ( CurrentDate ) ≥ WeekOfYear ( "10/…

Hi, I have a client where his calendar year starts April 2013. Not January 2013. So I built a script step in my Start Up script that SETs the Preference Field "Pref Begin Yr" to the current year only if ... If [ Month ( Get ( CurrentDate ) ≥ Preferences::Pref Begin Month and Year ( Get ( CurrentDate ) ) ≠ Preferences::Pref Begin Yr ) ] Set Field [ Preferences::Pref Begin Yr; Year ( Get ( CurrentDate ) ) ] End If This is how i read this script step: 1 ( current month ) ≥ 4 ( preference month ) and 2013 ( current year ) ≠ 2012 ( preference year ) The set field should not SET, but is does How can this expression be valid when 1 …

Hi Folks - I'm pretty new to FM Pro so please excuse my ignorance! I have a database for my students marks - from the marks I am using a nested if to generate a comment depending on the mark gained by the student - it works fine except for the students scoring <35% which it lists as "Although a narrow fail, this would suggest that major revision, past paper work and making use of Supported Study is a must if a Grade C pass is to be achieved." I can't figure out why it won't work - any help gratefully received Happy New Year to All Edddiec the formula I used is listed below If( GetAsNumber(Prelim 2012 overall %) >= 80;"This would suggest a Grade A pa…

Alright folks I'm Confused on the nested If statement function while i understand that the if function is represented as If(Test on ; Result 1; Result 2) , I'm trying to wrap my mind around the nested function , heck i may be using the wrong function all together. What I'm more so looking for is this If(Test 1 ; Result 1(another If Statement with Result one) Result 2). I want to make multiple Items true prior to performing a calculation. SO If ( the Current year (on this layout) = Current year (On Different Table) and If( The Status of "Active" = Status (on Different Table) ; Sum (Field) ; if not then put 0) I have tried this statement and it doesn't w…

Hi, I've set up a calculation field to use for display on a contract form. It's working fine except for one detail. Lookup(musicians::street_address)&" "&Lookup(musicians::postal code_zip) is returning the correct data, but the postal code is returning A1a 1a1. I can't get it to return as upper case. I've tried Lookup(Upper(musicians::postal code_zip)) to no avail. In the table containing the fields being looked up, the postal code is formatted as upper case. I've even tried to change the postal code field (text) to auto enter Upper(self) which doesn't work, and probably shouldn't . . . I was surprised FM accepted the calculation at all. Ideas? Rick. …

I have a field that has a size and typically looks like this: 6 in H x 6 in W How do I take the value of that field and insert a paragraph return after the "x" and removes the blank space before the next character so that it looks like this: 6 in H x 6 in W Thank you for your help.

Hi there... I'm having touble working out the best way to tackle this problem.... We have a stock system where by products can come in at varying prices... sometimes even we can be buying 10 of something and get the 10th item for free. In order to show a fair price for out commision based sales force, it is prefferred to show an averaged stock price on the sales order.. so instead of 9 items at £200 and one at £0 (because it was free) we would show each product at (200/10) ie an averaged price. So I have a products table and a purchase orders table and a stock table... I am trying to work out what the averaged stock price would be from the products table usin…

I admit I still don't have a grasp of the Let function, even after looking at some examples and explanations... I usually stick to the Case function but now I'm encountering a messy calculation and wondering is someone could show me how they would translate it into a Let function or if that is the way to handle it? This is the current calculation for my field c_LineItemPrice Case ( not IsEmpty ( OrderFeeClientPrice ) ; OrderFeeClientPrice ; Quantity ≥ 1 and Quantity < STQtyBreak1 ; STpriceDefault ; Quantity ≥ STQtyBreak1 and Quantity < STQtyBreak2 ; STpriceQtyBreak1 ; Quantity ≥ STQtyBreak2 and Quantity < STQtyBreak3 ; STpriceQtyBreak2 ; Quan…

I tried to search around for the answer, but I was not successful. I have a table of records which each have a category index. So, records are assigned a category of "001," "002," "003" etcetera. I also have a category table with the category index and category label. I am adding a field that is "categoryCount." I would like to know the number of records in the main table assigned to each category. The end goal is to have a layout that looks like this: 001 - "Category A" - 19 Records 002 - "Category B" - 2 Records 003 - "Category C" - 11 Records I had trouble setting up the calculated value to actually count the discrete record sets. Does anyo…

I'm having trouble running a calculated Applescript to set a field. I believe the problem is in the Applescript itself and not in the way I am using Filemaker. The script step is: Perform Applescript ["Set Id to do shell script " & Quote($Cut) & Quote ($tempFileName) & " set cell ID of current record to Id"] Where $cut = "cut -d , -f 1 " And $tempFileName = "/tmp/export.csv" The quotes are not part of the variables, this is just the string used to set them. (i.e. Set Variable[$Cut; Value:"cut -d , -f 1 "]) The error I get is: A """ can't go after this """. But I can't figure out where the bad quote is. I'm not really good with…

Is there a way to format number fields so that it is allowed to have a leading zero? Problem is that we use both mobile phone numbers and state codes with leading zeros. e.g.: 0417 123 456 or 03

This should be simple, but I don't know enough of the functions to find a quick solution. I need to display a custom dialog box only when there are related records present, AND any of those records is stll listed as open (text field selected either open or closed by user). So - if no related records, do nothing. If related records exist, and any of those records are open, display dialog box. I can do the first two conditions, but not sure how to test if any related records are open. Suggestions?

I have been able to create an AppleScript program within filemaker pro which opens and get a value from a plist file I've created. However, I am trying to see if this could be done without the need of a Perform AppleScript. I've browsed onto the net and found something like "ValueListFilter( pList ; pString ; pDir ; pPos ;pType... I did not find such a function in the FileMaker Pro 11 - Functions Reference manual. Am I better to continue with the Perform Apple Script call? The idea, is that I want to be able to copy the "Open Remote Function" I am currently working on onto my client workstation without having to open the code. I want to use a plist fil…

for example, in fınd mode, when I write "...", " * " like these operators in table. all records are coming. I want to cancel these operators for security. ıf it is possible or not.

Hi, I'm pretty new to FileMaker and am trying to create a report and chart that shows the occurrences of a various cylinder sizes that we use in our facility. I have a list of items each of which has a cylinder size assigned. What I am ultimately looking for is a report and pie chart with the slices showing the percentage of use forr each size. I have tried the example from the Filemaker Support page "Counting the Number of Unique Values in a Field" and cant quite figure out what the calculation entries in field "B" should be (I bolded the entries that I don't understand). Below is the description of the four fields that are required. I have replaced my entries where appl…

I know there is a check-box option for a running total. But it does not behave the way I need. I need to have the month-to-date totals to be the same through out the month. I can't but think I am missing something simple. Today Month-to-Date Budget-to-Date Variance I am using these fields to view my sales for the month. Today is a simple field Month-to-Date is a getsummary calculation of the summary field for the field in "today" This is the problem field It shows the same total for however many days have been entered for the month all the way to the last day of the month.

I have searched but can not find how to limit characters in a field. I have text fields (example: name or address) that need to be limited to about 30 characters. I have set the characters limits to 30 (it did nothing) I then added a "Validate by Calculation" setting the length to 30 (Length(My Field) >= 30). That did make it so that when your finshed entering (any anount of characters you want) it will pop up a window saying your beyond the limits of the field (but you don't know that limit until after your all done entering too much text. But what I need it to do is this: Stop the curser at the limit of 30 characters. The person entering text should not be …

I am quite comfortable in programming in SAS. SAS has a first and last function, I wonder if FileMakerPro has something similar. CustomerID, InvoiceNo, ItemNo 1001 5525 1001 1001 5525 1004 1001 5525 1007 1098 5526 2001 2001 5527 3501 Expected result is : CustomerID, InvoiceNo, ItemNo, CostItem 1001 5525 1001 25$ 1001 5525 1004 10$ 1001 5525 1007 10$ 1098 5526 2001 25$ 2001 5527 3501 25$ Given the above table I would like to know if this is possible to do the following calculation in FileMaker Pro. Set verifID to concatenate(CustomerID & InvoiceNo) Sort on verifID ascending if…

I am not looking to reinvent an accounting program as such. I am want to create the tracking tools in Filemaker Pro that I have been using in Excel. It is taking information from several sources in a restaurant to complete the tracking "worksheets". There is sales tracking and labor tracking. Invoices are tracked to collect the purchases for cost of goods sold as well as the other expenses. On a weekly basis or sometimes 10 day/20 day/EOM a proforma income statement is prepared. I have all of the information in tables. Now it is just a matter of assembling an income statement. For those that have done that, can you give me a hint? Do I create a record de…

I'm wondering if there is a standard, or best, method to set a condition on a record, so that is in essence flaged, and not allow certain actions to be taken. In the past, I've used a text field as a blank, and when I need to flag a record, I insert a value, Then I have my scripts check that field and only allow an action if the field is empty. I have also used the presence of a child record in a related file to act as a flag. This works, but I wonder if there is a better, or more robust way, to accomplish this?

I have a fairly complex calculation that works fine except for one situation. Basically it's calc;calc;calc/number_services plus a lot of other items. It doesn't work, of course, if number_services is "0" because you can't divide by zero. It messes up the whole calculation. So just for fun I tried calcs etc/number_services>0 and it worked. I'm quite surprised that FM would accept field>0 as a definition. Am I missing something here? As it turns out I didn't trust this method and set up validation on the number_services field to be not empty and to have a value greater than zero instead. This is academic for sure, but I'm curious all the same.

HI I have FMP 12 Advanced... One of the starter solutions is "content management" however when I change the storage system to "outside the database" the filename Placeholder field goes all odd and shows the path… size etc of the file… How can I just get the file name for this field …?

Howdy, howdy: I have this calc on the 3DBB: Truncate ( ( AdaptsToNewSituations1__lxn + ControlsEmotions1__lxn + DemonstratesProblemSolvingSkills1__lxn + DisplaysSelfConfidence1__lxn + SharesTakesTurnAndCooperatesWithPeers1__lxn + TakesCareOfPersonalProperty1__lxn ) / 6 ; 1 ) The calc works, but it has a serious flaw: it assumes that all six fields will have data in them before an average is calculated. But what if one of them is empty? I don't want to "hard code" the number, 6, in the calc because then the average will be thrown off if one or more fields are empty. Rats. What's a more elegant/…

Ok - i have 10 fields of phone numbers, like home phone, home fax, business phone, business fax, etc. I want to get a list of area codes from these, separated by spaces with unique values, and if the phone numbers get updated I want it to be properly updated. I have used a reduplicate custom function but this removes the duplicates from a return delimited list, so it only works once, not recursively. Thanks for your help.

I want to have a global text field that is a list of all the values from separate records for a specific field. Eg: say I have a table of Users, with field User ID. I want a global text field UsersList to container the User ID from each record in the table. Is there a way to do this without adding the values to the field via a script or something?

Hi I'm new to FM and I'm coming from the excel side ... FM12 is quicker for me to do relational stuff (sorts etc.) but I've run into a simple problem: I have a field with a string of alphanumeric text (6) that requires a space to be inserted in the middle. I cannot find out how I would start this in FM12. I know how to do it in excel ... is it similar? ie. create a new column, generate the result ... etc... ? I've also never had to put a calculation into FM12 .... any thoughts would be helpful. Thanks! Lance

Sorry, I couldn't think of a more accurate topic title for this. And I'm annoyed, because I'm sure this is something simple, but I just can't come up with it at the moment. I'm looking for calculation to determine if a value in a particular field is equal to an existing value from a second field in a related table. For example, in one table there is a set of invoice records. In another table, a user is inputting invoice numbers. If the user enters an invoice number that already exists in the other table, I'll do some conditional formatting to change the color of the field. I thought I had done this before with either the ValueListItems function, the List…

**Disclaimer** I've been around scripting and databases for years(PHP, Perl, Oracle, MySQL, etc) but I've been working with FM Pro Advanced 12 for just a few days now. I apologize for what I'm sure will turn out to be the dumbest question of the year. I have a piece of equipment which costs '$cost' I have an equipment lifespan which is '$years' I have a static cost inflation rate which is 2.5% I have a field which is supposed to estimate the cost of replacing this item at the end if it's lifespan, and that value needs to be automagically populated. This pseudo code will hopefully explain what I'm trying to do: $cost = cost; $years = years; while ($years !…

Hello, I have an events management database. I have a list of Events records in list view, in a layout I call "Event Search" and I wish to sort these according to the most recent that have been modified (or even better, according to most recently visited). The problem is there is a large quantity and deep network of related child records associated with an Event record. I wish to get the most recent modification timestamp from ALL the related records (from tables such as Estimates, Estimate Lines, Planning Document Lines, and more!). I've thought about this various ways: 1) A related table with a field that is updated/calculated anytime a related recor…

In a script I have a variable that holds a calc that build a table::field. The field comes from User input. I need to try to show a list of the field contents as the fields are all repeating with 10 repetions. I will be using a loop to locate the field from the 280 repeating fields on the record. Help. Not my creation. thanks, Jim

HI everyone Im trying to do a field calculation which involves a date range - however I cant work out how to right an operator for a date range between 2 dates. I have something like this: If(course start date > Date ( 1 ; 1 ; 2013 ) <= Date(12 ; 31 ; 2103 ) ; adminfee received; 0) What am I doing wrong please... Basically if the course start date is between 01.01.2013 and 31.12.2103 then i want the admin fee to appear - if not to put a 0 thanks for your help alison

Hello! Feel free to move this to another section if this one is inappropriate. We're using FileMaker 11 as a point of sales system, and I was thinking of new ways to speed up our sales reports. Is it possible to get Filemaker for instance to search a database for all sales completed by a specific salesperson within a specific week or month, and then exporting the results to an Excel file or such? As of today we have to manually search the database in FileMaker and email the results to the salespersons. Having a kind of automatic way of doing this would be great, and would save me a lot of time.

Hi there, I'm back to Filemaker after a long break - and have been told to implement a Calendar. I am using a calculation field for each "Day" - as each field is not so much a date as a list of Date attributes - based on the date - i.e for the 10th of November the multi key list would be: 10/11/2012 monthday10 monthnum11 SecondSaturday I did OK (I think) as long as the Calendar was allowed to start on the first grid square (the first field knew that it was referring to the first of the month) BUT - the boss now wants the 42 day grid - with the first days as well as the last days that are not past of the selected month also being shown. My question is - h…

I have an Invoice System in which I'd like to define a calculation that analyzes all of the invoice's line items for a status of print ready or ship ready, and set's the order's global status accordingly. In other words, if ALL of the line items status are either "print ready" or "ship ready", set the Invice status to "Good." I can handle that calculation if it was always based on one line item, but I don't know how to handle all of the line items. Please help.

Hi, Please could someone help me out, i have a container field which contains a quicktime file (Mp3), when i open and close the database the file stays there when i email it to my ipad to work on filmaker go the container field says file missing, is there a setting i need to change or anything many thanks

I had a field to concatonate a person's FullName: NameFirst&" "&MiddleInitial&" "&LastName&" "&Suffix But I noticed, if the middle initial field was blank I'd still get the additional space between the NameFirst and MiddleName. So I tried modifying the FullName field as follows in an effort to get rid of the additional space in those cases where the MiddleInitial was null (but this didn't work either): Trim(NameFirst)&" "&Trim(MiddleInitial)&" "&Trim(NameLast)&" "&(Suffix) Example above produces: Bill H Muldoon Sr However, if the MiddleInitial field is null the above calculation produces an extra space b…

Hi Guys, Ive got a database, where users can add various conditions - "More Than and Less Than". I've got a formula underneath which works providing I use the proper > < tags in it. I want somehow be able to convert More Than on to > and Less Than into < within the formula. Anyone know of a neat way to do this. If( (gWeeks & Case(Condition="Less Than";"<"; Condition="More Than";">") & Condition_Weeks and Condition_Week_Include = "Yes") and (gWeek_No ≥ Week_No_Start) and (gWeek_No ≤ Week_No_End) and (Supplement_Type = "Add"); gBasic_price_per_week + Supplement_Amount) Thanks Jalz

Looking for a little help I've got a calculation field set up to calculate the record number of a record using the calculation: Get(RecordNumber) It's set up as 'Auto-enter Calculation replaces existing value, Evaluate always' It works fine when the record is first created, but the problem is that if I delete a record behind it, it retains this number in the field. I need to figure out a way to make it go recalculate the field, preferably whenever I access the record - so that's it's always calculated "on the fly". I've played with the script command "replace field contents" and it works when I set it up to recalculate the field - in fact it works across t…

Hi everyone. First of all, I'd like to thank you in advanced for your help, and apoligize for my bad English. Let me explain my situation. I attach an example of my solution. I have my clients, with an ID number ('CUIT/CUIL'), and one date field called 'Cierre de ejercicio'. I also have a table ('base_d_impuestos') in which each record ('impuesto 1' , 'impuesto 2', 'impuesto 3', etc) has different dates ('january 1', "january 2", "january 3"; "february 1", "february 2", etc) that I'll have to choose according to the two fields of my clients. I mean: when I create a record in the layout "Impuestos", called 'impuesto 1', and the 'cuit/cuil' of my related client is last …

Is there a way to have FileMaker Pro add text (a word or phrase) to a field across a found set of records? What I wish to do is similar to what the Replace Field Contents function does, except that instead of replacing the contents of the field, I wish to add the new text to what is already in the field. Do I need to write any kind of a scrip to perform this function? If so can you guide me how to do it?

I would greatly appreciate any advice. I think what I am trying to do is simple, I just can not figure it out. I have a field "LotA" and a field "LotAcres". I am trying to write a calculation for LotAcres field that will automatically put 4.6 in to the field if the "LotA" field is NOT empty... If the "LotA" field is empty then the "LotAcres" field I want to remain empty. Thank you for your help.

I often receive names strings which vary in format, eg Harris, John Dr Harris, Dr John Dr John Harris John Harris I would like to extract the component names. My plan - which is my question - is to find the word number of the title (eg Dr=3), Once I have the word number for the title I can use various middlewords to extract the name components. BTW titles are likely to including a variety of options. Dr, A/Prof, Prof, Ms, Mrs, Mr For info, I have a clunky workaround for components which might be read as more than one word eg A/Prof, Smith-Harris by using substitute - "/" "slash", "-" to "hyphen, etc extract via middlewords and then substitute back. …

Hello everyone, I am trying to create a calculation that checks to see if the number in a field falls within a certain range and thereby assign it a value based on that. For instance, if the number is 34, then assign it a 3 (to state it simply). I've tried experimenting with the Case function, but to no avail. The problem I am having is setting it up so that it checks for being in a specific range, like 11 to 20, 21 to 30, etc. How would I setup the calculation to check for being within a specific range? And is there a better option than Case for this? Thanks in Advance, Joshua

I am looking to allow a user to input data in a foreign language, namely Chinese. Is there a way to allow FileMaker to use foreign language keyboards and such? I've seen a way to do it with labels where you check for the system language and then display a field based on that, with predefined data. But how would inputting work for the text fields and does Filemaker accept foreign characters?

I have three portals on a single layout, and would like to count the rows in one portal and return the result to a field. Again, not much success. Thanks for your help, Jason

I am sure this is simple, but I can't figure it out. Need to extract the file name after 'remote:' and before 'FILE' remote:photo_todo.rtf FILE:jbDeliverables/deliverable_list_document/photo_todo.rtf Thoughts?

I'm a bit puzzled by this. I'm not sure why the function IsEmpty ( Get ( ActiveFieldName ) ) does not evaluate properly. Nor does IsEmpty ( GetFieldName(Evaluate(Get(ActiveFieldName)))) or any other variation. My goal is simple: have the function evaluate whether the active field is empty or not. Using Data Viewer, my test results return the following: IsEmpty (yourFieldhere) - returns the proper value (1 if empty) Get (ActiveFieldName) - returns the proper value (the unqualified name of the active field) GetFieldName(Evaluate(Get(ActiveFieldName))) - returns the proper value (the fully-qualified name of the active field) IsEmpty ( Get ( ActiveF…

I want to have a modification date remain the same for each record, when I have to do a mass change (e.g. adding a new field). I tried turning off off the auto-enter switch for period of time while I performed the mass change, and that seems to work, but then I have to remember to turn auto-enter back on. This gets to be a hassle when the customer keeps asking for changes and additions. Does anyone know of a better solution? TIA

Hello Board, Again, thank you for all your help recently. I've learned a lot about Looping, Counting and Variables and have really put them to work several times over. We use a database here with a field that contains a barcode number. It's 10 digits long. Last night, i changed the field over from 'Number' to 'Text'; because we're about to start scanning alphanumeric codes into that field. It went OK and you can now input those alphanumeric codes into the field. However, when i use our barcode reader to scan into that field, it puts a carriage return at the end of the number. This wasn't happening before. Anyone know why? This can't be the way wi…

This calc should produce True¶1but it produces 1 (num1 and num2 are number fields and this calc result is set to text) and there is correct data in the fields. Case ( num1 = 5 ; "True" ) & ¶ & not num1 and not num2 Obvious type cast issue? Probably but I cannot pin down how it determines the data type here. FM clearly evaluated it as number result so all non-numbers disappear producing 1. IIRC, FM returns the data type of the last evaluated portion of a calculation so I looked to the Order of Operations. PEMDAS says Case() with its parentheses would evaluate first so it would make sense then that the boolean (blue) would evaluate last an…

Hello, Well my first question here . I´m working with Filemaker since summer and this forum is always a great help for me from the beginning. Let´s see if someone can help me (please excuse my bad english) because this is breaking my head. I have the following structure: Table: Articles ----------------- ID of Article Category 1 (contains ID of category) Category 2 (contains ID of category) Category 3 (contains ID of category) Category 4 (contains ID of category) Table: Categories -------------------- ID of Category Category name I need to combine the four product categories names in one single calculation field for our header…

I'm developing a solution for a Windows client; however, I'm a Mac developer and am building the database on a Mac initially. The client needs to use certain container fields for copy/pasting screenshots. Thus, I can't use "store a reference" here. I'm running into an odd file size discrepancy: 1. A screen image (1024x768) copied and pasted into a container field on the client's computer is roughly 3MB in size. 2. If I click the container field and copy the screenshot myself (on a Mac) and open in Preview it's around 72K. 3. If I then do a screen copy of the same image from Preview and paste it back to the container field it's 272K. I can live wi…

Hello, I have a question on how to create a field with the calculation (or any other suggestions) that would display a field in my database that shows a list where i'll have the number of items per category. In other words, let's say I have 3 different tables. table 1: default home page table 2: lists of sports table 3: sports team in my default home page, i have a portal shows the sports team on left column and the lists of sports the team is apart of. now what I want to do is instead of showing the sports team on the left column, i want a field that counts the number of teams in each sports. IE: Number of Teams Sports…

Hi, I've got a bunch of ISBN 13 values in a field like the following: 9780000013712.jpg I want to remove the ".jpg" so I have just the ISBN 13 value. Don't know how to do this. Thanks! David

Hello I am posting a filemaker file that I made to show how I am trying to separate pasted data from the yellowpages online into separate field. The problem is that there are a lot of variations of text and that is why I can't figure this out. I am a complete newbie here and wanted to make a contribution to this forum by posting an example that I am trying to solve and hopefully people could help me make the calculation to make this happen. I have included the url address for each record to show where it came from and the pasted information from that page resides in the field called yellowpage copy, I did the first record manually but the others I didn't have a clu…

Hello All! I am stuck on this problem. I have created a packaging/converting database where I want to create a batch or set of records that are unique but copied from the first record of the set but based on a value that tells the file how many to create. I have included a screen capture of what I am trying to do. The "Total Across Jumbo" is listed in the top row of the dashboard, this will create a batch or set of "4" unique records but copy all information with the exception of the serial number or "Parent Roll ID". So the second set of records would be 5 through 8 and then 9 through 12 and so on.... The trigger could be a button to start the batch copy but…

Hello. I want to run an occasional script which says: Make Field 1 the same value as the lowest of Fields 2, 3, 4, 5 and 6. So if I had: Field 2 = 27 Field 3 = 7 Field 4 = 236 Field 5 = 85 Field 6 = 103 The script would put 7 in Field 1 Anyone know how to do it?! Thanks Philip

Hi there Having a little problem and wondering if someone can help me. Im on FM10 BTW. I have an event database with fields EventName and DateOfEvent. I've set a Script Trigger so that whenever anyone does a find on an EventName the events found are put in date order. But it would be really useful if it automatically went to the next event after todays date (they happen once a month, so usually within a 31 day period). I have a TodaysDate field as well. Can anyone help me with this? I would really appreciate some assistance. Sorry I just realised I called this topic 'Go to next record...' but it should say 'Go to record...'

I have a field that sometimes has a " symbol in it. Hi I have a field that sometimes has a " symbol in it. - this field will be exported to a .csv file and imported into a web server. My website people told me to replace the " with " so the import script their end understands the difference between field contents and the start and end of each field. I've got this far , but am having trouble with the last part Substitute (Product name ; """ ; " " ") I can get it half working if i put this in - but when i remove the spaces , it doesn't accept it. any thoughts welcome !

I am creating a calculation as part of a menu set and I wonder if there is a way to create a 'Set Variable' type calculation that for e.g. $Layout 1 = "Layout 1" Within the Menu set Action: Perform Script, I would be able to use only one script that could; Go to Layout: $Layout 1 Does that make sense? Make the Let function?

Mac OS 10.7.5 I receive orders via Excel in .xls format. Personalized products. Personalization is thus: 44184 Name: Alexandra Obviously I wish to remove the " 44184 Name: " With spaces etc. equals 12 characters/spaces. I have this working in other data base using MONO (MONO ; 12 ; 20 ; ) New data base is essentially the same set up with like column names, will not function. I have the Mono column formatted as Text in FM as well as the .xls file. Tried changing format to number or General in the .xls file with no different results. Only difference in the receipt of the files from the old DB and the new one, the file I receive in o…

I have got a global field for the user to select from a drop down menu. I can't deny access to them because the field needs to be accessed in order to use the drop down menu. If the user clicks twice within the field, they can actually enter a value that is not on the value list. I have validation setup so that if this occurs, the user receives a message. I don't like the message. Isn't there a calculation that I can run as part of a script trigger that basically says; If field is modified to a a value that is not part of the value list, revert field? Thanks so much for your help in advance and have a fab day.

I actually got this information a while back from this forum and it works fine. My question is trying to understand how it works. If anyone can help me and explain this in English, I would appreciate it. Let me explain what I have. In brief, I have two related tables, students and grades. There are multiple grade records per student. My objective is to get a printout by class of the students and their courses and grades. The heading has the names of the courses, the body has the last and first name followed by the grades directly under the course name. In the grades table we have the student_id, course name and grade, one record for each of the students courses that the…

I have 2 fields: Year (number), Skunks (Text: yes or no). I want to calculate the ratio of #skunks per year. I created the following summary field: sCountYear = sCount(Year) when sorted by Year sCountSkunks = sCount(Skunks) when sorted by Year I create a subsummary part when sorted by year to calculate the number of surveys done per year, and the number of skunks (no sightings) occurred each year. I need a summary field that will calculate the proportion of skunks per year for each year (sCountYear/sCountSkunks), but I can't make a summary field perform a calculation, and a calculation field isn't going to give me subsummary values by year, only a trailin…

I have a field which shows the Total Cost of a number of items. I also have a field to show the Total Value of the items. The Total Cost field is a calculation field. The Total Value field is a number field for the operator to complete. What I would like to do is have the default Total Value = Total Cost, while allowing it to be overwritten by the operator. If I make the Total Value field a Calculation field then it can't be overwritten by the operator If I make it a Number field with an auto-enter calculation saying =Total Cost it just doesn't seem to work. I've tried GetField. I've tried creating a second occurrence of the table and using Lookup. I…

Hi, getting really into my new filemaker database, but hitting a few blocks on the way, i would like to convert numbers into dates, in access i used Dateserial to convert it, for example is there a function to change '2102' to read 'February 2021' so in may database i get yymm as numbers but need to change them into a date any help would be greatly appreciated many thanks

I have a container field that does not allow me to insert the file types I am looking for. I tried this with a script, but it did nothing, and when I manually right click on the container, only Insert Picture, Insert QuickTime and Insert File and clickable. What is the reasoning behind this? Cheers!

Hi all, wondering if anyone could help me out, i have moved from access to filemaker, i can do a bit of vba in access and excel but new to filemaker scripts can someone help me out i want to shorten the text depending on the length can someone help convert the below into filemaker language IIF[LotLengh]=17,Left([LotNumber],Len([LotNumber])-9),IIF[LotLengh]=18,Left([LotNumber],Len([LotNumber])-11) how would you write this in filemaker specify calculation, many thanks

I am trying to clean up some of my fields and I'm trying having a hard time figuring out the different between these two (and which one I should use, since I think they produce the same result???) 1) Calculation field type (recalculating when needed) 2) Number field type (auto-enter calculation; replaces existing value) I am assuming that these two have the same result; but are there advantages/disadvantages to using one strategy over another?

Hi, I have many line feeds in the format, 2009-09M170 to 2011-09F493 there are S's and D's too in the mix where I need to get rid of the year (the first four), the hyphen and the '09', which is consistent in all records (it's not 2010-10M398, it's 2010-09M398) and end up with M170 or F493 or M398 Thanks!

I DL'd this function from BrianDunning.com and it is pretty useful. However, I need to establish a global variable value via an IF statement berfore the Let statement. Filemaker doesn't seem to like what I've done. I have seen IF statements after Let but I can't get it to work BEFORE the Let or anywhere else. What am I doing wrong?

I have a field - Item #, which is calculated based on some criteria - item code, and next item # next item # is a number in format 0001 for example an item # could be: A0001 - arising from item code & next item # now, when i increase next item # (by next item # + 1) it goes to 2, instead of 0002 and subsequent item #'s become A2 instead of A0002. how can i do this increment without losing the formatting I have assigned?

Hi guys! Yes, I'm a freak still using FM 5.5v1... I hope there's someone out there who can help with this: 1.- I have a number field called "Number". In Options -> Validation -> I want to set-up the validation by a calculation as follows: Only accept if the number is from 20 to 30 or from 50 to 60 (only those two specific ranges). 2.- I have another number field called "Number 2". In Options -> Validation -> I want to set-up the validation by a calculation as follows: Only accept if the number is from 1 to 19 or from 31 to 49 or from 61 to 99 (only those three specific ranges). What's the calculation I should write on ea…

I have a family center database wherein people, more specifically, children often reside in multiple households. A field labled HHDType can have one of two values, "Primary" and "Additional"... which represents two types of households they could live in. Each household has a repetitive field called activityDateSpan (allowing for up to two repetition) indicating a startDate and endDate. These dates represent the following: rep 1 = the date an individual began association within a particular household and rep2 = when the association is terminated (repetition#2). I have a calculation called cHousehold which identifies all the possible households this person resi…