Calculation Engine (Define Fields)

Hello, I'm working in a database that, for whatever inane reason, has a date field that was created as a text field. Now, years later, I have to do a find for a range of dates (an entire fiscal year, over 1,000 records) using this text field and of course, it's not working. I can't change the field to a date field without messing up the data from thousands of previous records (or can I?), and I can't figure out if there is a way to create a date field to replace this text field, while pulling in the values from this text field. Help please and thank you for any ideas or suggestions.

Hello, My objective in the attached file is to be able to enter security data [will become various mutual funds] and have the price change between each successive record for a particular security calculated. I thought it would be great to be able to just enter the data on one interface [layout] and have serial ids for each security. ie. if the security is 'Apple', each time one entered a new record the [calculated] serial was set in a sequence specific to 'Apple', if the security was 'golden' then it would have its own [calculated] serial sequence. So I found an example file, serial by category, and have used it here. On another forum I also located an an…

I am solve a problem with populating a tracking number field when you re-enter a order number. Our order number field is set to not "allow for modifications". If I turn off the "allow for modifications" it works fine. The problem is that I dont want to allow for modifications of an order number. Any thoughts on how this can be done?? thanks Mark Burris

Greets, all: This is what I get for switching to decaffeinated coffee: I can't figure out how to modify this calc so it truncates the prefix to a URL: Let( [ adj = Substitute( Url__lxt; "//"; "§" ); pos = Position( adj; "/"; 1; 1 ); top = Case( pos; Left( adj; pos - 1 ); adj ) ]; Substitute( top; "§"; "//" ) ) As is, the calc does a nice job of truncating a URL's path so: http://www.career-launcher.com/help/how_to_find ...becomes... http://www.career-launcher.com However, I want to truncate the URL even further by removing everything to the left of the URL so it reads: career-launcher.com …

Can anyone help with the following problem I am trying to over come? I have a database where as a support desk log all support calls. Very simple Start Date Start Time (creation time and Date) we also have SLA times in this database set in hours. Our service department is open Monday to Friday from 9am until 5pm. I would like a field that shows me the total number of hours and minutes a call has been logged for minus non working hours. I have adapted a calculation I have found on the web but it seems to be ignoring non working hours... Can anyone please help? I would be very grateful (Int ((call_date - call_date) / 7) * 5 + Mod (Get(CurrentDate) -cal…

Dear all Can someone help me out with this one?: I need to calculate days and month based on two fields: 'Starting date' and 'End date', so it adds up to a number. The number should be calculated as follows: Basic fee (268 $), 532 $ pr extra month from starting date and finally 19 $ pr extra days Ex. 'Starting Date': July 28. 'End Date': September 30. - Basic fee: 268 $ - July 28 til 27 September: 2*532$ = 1.064 $ - 28. september till 30. september 3*19 $ = 57 $ Total: 1389 $

Hi all, I've got a contacts table which is the parent of a roles table. I'd like to put together a calculation (presumably an IF statement) that calculates whether a particular contact performs a particular role. So let's say I have a field in the contacts layout for an author biography. I only want this to be filled out if the contact's role is an author or illustrator. I would also use a similar calculation in other fields determining whether or not to include particular contacts in an export. Can this be done with a calculation? Or should it be done with a find? I'm not sure how to accomplish it. Cheers.

Hello, First I don't know if I'm in the good section and hope you'll not be angry !!! I have a multivalued field which receives through a script value 1 in the case I click on ; supposing that empty is represented by underscore I have a succession looking like 111_ _ 1 _ 11 In a second step I need to get the list of 1 and empty ; but the function List(myMuliValued) returns only 111111 How can I either : replace the empty values by 0 before calling List ? fill all the values with 0 when creating the record ? Thanks for your ideas, Noël I know how to do wth a script, but I hoped in calculation....

Hi, I am trying to calculate the difference between . I have a Person table, Object table, and Measurement table. There could be multiple types of measurement and multiple times of measurement. For instance, John has a fish. He measures the length of the fish and the weight of the fist. I am trying to calculate the change in each type of measurement. Currently, my method is to have fields in the Measurement table to calculate the difference using the Let and Case function. For example, lengthchange_c = Let ( [difference= Case( Get (RecordNumber)>1;legnth- GetNthRecord (length; Get (RecordNumber)-1) ); last…

Hi, I have a tbl_Grid set up in my database. It displays as a portal based off the Plate_ID number chosen, with each ID number being the unique identifier in the portal. There are 10 records per Plate_ID so the portal is 2x5. I need to add a field "Count" to my table. What I need this field to do is total the number of "x"'s imported from Excel based off the SIID field. In the example below the count for SIID 111111 would be '8'. That number would appear in every portal square associated with SIID 111111. At the same time, all "x" squares would turn red (that part is easy). I need to know the calculation to get the count though. Thanks *EDIT* I …

Greetings; I have a field called "Needs site image" and i have another field called "Image Location". Image location will have the URL location to where the image is stored on the site. so for example http://www.delightsoftheflesh.com/images/Dildos/DIBA0339.jpg what i was wondering is if there was a way that you can make a caculation box "Needs site image" to see if the URL for "Image Location" is a vaild address and see if the image excists on the site. Sometimes images are accidently deleted and not uploaded and i would like to have a Calculation field where i can search for all the records with broke image locations, Thanks

Specific problem (columnar reporting and script deciding how many columns) I am figuring months and if another field says "comparative" then I want to double the number of months. I found calc to use can not figure it. 12 * ( Year ( end ) - Year ( start ) ) + Month ( end ) - Month ( start ) + 1 + ( ADMIN::gReportStyle = "Comparative" ) * 2 If dates are 4/1/2011 - 8/31/2011, it produces 5 like it should if style is "Contiguous" but produces 7 and it should produce 10 if "Comparative". I am being stubborn and do not want to switch to using Case. I know the order is wrong but I tried 20 combinations and still can not see it. btw the calc did not…

I've tried searching for prior posts on this subject, but got little to nowhere. I'm not even sure of the langauge/vocabulary to use to search. Feel free to point me in a correct direction. In my Excel spreadsheet, I use the 'Index' function to lookup data from a 'Standards' sheet. The data on this sheet is arranged in a table with columns & rows. 'Index' allows me to specify which cell to lookup the data from, according to the column & row. I'm trying to do the same with FMP. I want to lookup the specific value from this 'Standards' table. The data in this table is arranged in columns (according to each test result) and rows (according to the Records)…

Hypothetically: 1) If I use virtual list for 1,000 records in 15-field reports table so 15,000 global variables (shortened here to GV) are created, and the file can not be closed to clear them, does it matter if they are left? In searching, there is mention of tripping over same value so sometimes individual GVs are cleared and so I clear them also but I wonder why. Does not the programmer control GVs anyway so it gets either value or blank when it is called when it gets its value. 2) When might I see effects of too many GVs loaded at once? Are they kept on server and pulled down when needed or are they kept on person's computer? GVs are individual pe…

I have a Filemaker solution that is used to record length measurements at an inspection station. I've created a layout to summarize the data to obtain the average and standard deviation of the numerous records. I'd now like to calculate two values - average PLUS 3 times the standard deviation and average MINUS 3 times the standard deviation. Is this possible? I tried creating a calculation field, but it based the results on all records, not summarized by the particular part. Thanks, Bill

Well lets see if I can get this right ... I have been looking but can't seem to find quite what I'm looking for. I need to substitute every occurrence of ":" that is between each occurrence of <Name>*</Name> but not between <DateTime>*</DateTime> within a text field where the occurrence of <Name>*</Name> may be once or may be 50 times. Why would ":" be in there? It shouldn't be but someone did anyway.... Example: <Name>John Jones: C/O Jack Jones</Name><DateTime>10-17-2011T15:30:25</DateTime> <Name>Jackie Smith</Name><DateTime>10-17-2011T15:30:25</DateTime> <Name&…

Basic question, but I haven't been able to figure it out from reading previous threads. I have a Purchase Orders table and a Products table. I've figured out how to create a drop down list consisting of the catalog numbers of my products for selecting when making a new purchase order. I would like both the Artist and Tile fields from my Products table to auto-fill in the Purchase Order table's Description field based on what I've selected as the Catalog Number when filling in a new PO. Any help is very appreciated and admired.

Guys, I have the following calculaiton: Case ( Service = "Call" and Type = "International" and Number = "xxxxxxxxxx", 0.30 , etc... On the xxxxxxxxxx I want to specify a number range, e.g., from 100 to 100000000. When I do searches I simply enter 100...100000000 because "..." refers to range but in the calculation it won't work. How should I do it?

Hello! I've Mac OS 10.6.8 with FM 5.5v1 (yes, I know I need to upgrade but bare with me on this one): I have a field named "Duration" that indicates time duration in hours, minutes and seconds, example: 0:32:11 That means 0 hours, 32 minutes and 11 seconds. I want to create a calculation field where such data is converted into minutes rounded up, examples: 0:32:11 would be converted to 33 0:56:05 would be converted to 57 1:28:19 would be converted to 89 How should the calculation be? Best!

Hello all, I'm working on a research study and am trying to track enrollment over the course of the study, both by week and by month. My current model for tracking summary fields has worked fine for more contained variables with fewer outcomes, but this one demonstrates the naivete of my model. MY GOAL: Track the number of participants that enroll each week (or month) over time and plot a bar graph of enrollment per time period over time. MY METHOD TO DATE (I'm new): I have assigned each record an integer to code for the "study week" in which they were enrolled. That is to say, if they were enrolled in the first week of the study, the variable "ASSI…

I'm working on building a new invoicing system. One of our billable items is an update that runs every Thursday. We bill on a monthly basis. What's the best way of calculating the number of Thursdays in a specified month? As a part of some other calculations used in this model, I do have specific month (i.e. "August", "September") and year (i.e. "2010", "2011") fields. Piggybacking those would be great.

Hi there, I have a layout that has data from Table 1, a Name andDate field, with a portal for Value field. The Value field is stored in Table 2 and autopopulates depending on what Name is chosen from Table 1. I need the ability to override the data in the Value field, but NOT change it's original value in Table 2. Is this possible? For example if I enter the Name "House 1" and "2000" pops up in the Value field. I need to the ability to change it to "1500" for example, but the next time I pull up "House 1", have "2000" autopopulate again. Basically the values hardly ever change with the records, hence the looked up table of values, but they DO change occasiona…

Hi All, I have a table called "Vessel Logbook" which is used to record daily fuel consumption for our companies vessels. I have 6 Fields "Vessel Name", "Date", "Consumption", "Loaded", "Transferred" and "R.O.B" (Remaining on Board). What i am looking for is a calculation for the "R.O.B" field which will automatically calculate the R.O.B of the vessel for that day. eg. If Vessel Name = Vessel 1 R.O.B = Date - 1 (previous Day) - "Consumption" + "Loaded" - "Transferred" to do this with 1 vessel i can do but as we have over 20 vessels i am stuck, can someone help with this?

Hi, the Get ( SystemDrive ) returns the drive where FM is installed. Is is possible to get the drive name of a file stored as reference in a container field? Thanks!

Everybody probably knows how to do this except me. How do I permit a user to save application preferences so that they carry forward from one session to another without the possibility of another user changing the preference value?

I do not know its name and that is why I can not find it. If I am in a field and I want to know what type of field it is, whether check box, popup, radio button, edit etc what is that called? It is not FieldType. I looked all through Get and Design. Can someone please help me out? Oh. I mean if I want my script to know what it is. I will know by looking but my scripts do not have eyes. :laugh:

Hello- I would like to be able to automate the navigation to a field if the contents of another field matches a field name. example If I have a field named for each primary color Field (RED) Field (YELLOW) Field (BLUE) if the contents of another field (CHOICE)= any of my field names then navigate to that field So if the user enters "red" in the field (CHOICE) the cursor goes to the field (RED) and if the user enters "yellow" in the field (CHOICE) the cursor goes to the field (YELLOW) and if the user enters "Blue" in the field (CHOICE) the cursor goes to the field (BLUE) how can i make this happen in a script i appreciate your he…

Hello I would like to know if someone could help me make a script that would 1. Save a record as a pdf 2. Name the pdf from a field 3 Automatically send the pdf to an email address in another field. (email field) All in one script. Thank you

Hi, For sake of simplicity I have warehouses that I want to record whether they have maintenance checks done on them or not. I have the warehouses in a check-box layout, and I check each warehouse that has been screened. This goes into the database with a date associated with them. In a related table is the sqft for each of the warehouses. What I want is a report that basically looks like a table view where you enter a date and the table populates with all the warehouses in one column, and the related square feet of each house would populate the second column. The problem I am having is since this is a checkbox field, I am ending up with one row of data wi…

Is there a way to get the filemaker internal foreignID if using a mysql datasource.

I have a table where we do our estimates. This table is used by 10 people. The estimates are comprised of estimates made by telephone, by email, and by a physical person entering our office. I have already created calculations and summaries in this table, even with the percentages for each type of estimate based on the total estimates. I have another table with the the actual contracts that have been made and the summary count of contracts made by each person. This table is used by the same 10 people. Now i need to make another table to give me the summaries of all the data from above by month. The percent of estimates that have become contracts and etc. My pr…

I am trying to code conditional formatting based on whether or not a single value in a checkbox set is checked (regardless of the other values). I found this FM Support answer that says clicking a single box returns results based only on that checkbox, but when I write a calculation, this is not the case. For example, I have a field with four possible values. If I use the calculation [ table::field = "value 1"] to run conditional formatting on a related field, it is only applied when "value 1" is checked and all other checkboxes are empty. I want it to apply formatting independent of other checkbox data. What am I doing wrong? Thanks for your help!

Greets from Ceti Alpha 5 (otherwise known as Texas): I'm trying to create a calculation that counts the number of characters in a field, but am having trouble inserting a thousands separator for the text output. Here's what I have so far: Case( IsEmpty( JobDescription__lxt ) ; "Count: 0" ; "Count: " & Length( JobDescription__lxt ) ) The only addition I could come up with injecting into the calc is: Left (JobDescription__lxt;1) & "," & Right(JobDescription__lxt;3); Left (JobDescription__lxt;2) & "," & Right(JobDescription__lxt;3); Left (JobDescription__lxt;3) & "," & Right(JobDescription__lxt;3) ...but I…

I'm looking to create a calculation in my table that will tell me how many items were sold of a particular sku in the last 30 days. I was thinking of using the count function but I'm not sure how that would work with dates. Any ideas?

I have a text field that contains text verbiage along with 7 digit numbers scattered throughout. I want to find all the 7 digit numbers and convert them to XXXXXXX. Substitute should work if I can define the search text as "all 7 digit numbers." Is that possible to do? Perhaps a custom function. Thanks

Hi All, I want to validate a field to be not empty and contain a specific number of digits if the text in another field meets specified criteria. I have a field called Account_type, if the choice in that field is "Account" then I want to force the inclusion of a ten digit account number in the Account_Number field but only if the text "Account" is specified. I know this should be an easy If or Case statement but I am not getting it right.

Hi, Im not sure what i am trying to do is called, but I certainly appreciate any pointers you can offer, or link to other posts that may have delt with the same issue. I have multiple tables containing different types of data about a customers various systems. the data is collected/entered in form of an inspection which is dated upon creation then we print the inspection report. When the report is printed, the inspection is marked as printed in a field called "PrintedYN" They default to "N" then are changed to "Y" when the report is created. What I'd like to do is create a report(?)/Layout of some sort(?) that tells me which customer has data not printed. So,…

Could it be Alzheimer's? I want to do the following, and it should be easy but I'm lost. I have a relational database. Parent record is a client with a unique Client_ID of, say, "ABC-123". Case managers make case notes about the client. Case notes need to have serial numbers based upon the related parent file Client_ID. For example, the case notes for client ABC-123 should be numbered 123-001, 123-002, 123-003, etc. This should be like a FileMaker 101 kind of problem, so send me back to kindergarten. Help.

Further to an earlier, somewhat lengthy thread: http://fmforums.com/forum/topic/80142-create-auto-numbering-field/ Everything was working fantastically, until I foolishly re-sorted the database. Now I've got a problem. To recap: I have a "serial" number that is generated from each record's position when the fields are ordered in a particular way. (In other words, the serial number is constant for a given record, regardless of how the data are actually sorted.) This is done by first creating a field which joins together 3 fields, on which the order is based. Substitute (List (Type; Title; Subtitle); &#182;; " ") The main table has a ≥…

I have a little problem with date formatting. I’m in the UK, and all over our database the dates format perfectly in UK format, except for in one area. That is if I use a date field in the middle of text in the body of an email that is created from the Send Email script step. So the date 31st December 2011 which appears everywhere as 31/12/11 looks like this in the email: “whatever text is to the left 12/31/11 whatever text is to the right” So this means that we are sending emails out with the following explanation: “whatever text is to the left 12/31/11 (date may appear in US format) whatever text is to the right”. This wouldn’t be such a …

Hello I'm going nut over dates again. I need to do a "simple" monthly maintenance schedule. First table is the hardware with description, picture etc. Second table Line_Items shows as a portal in the hardware table this is where the maintenance is selected. It has 5 visible fields Maintenance description - Starting month - Ending month - Frequency - Next maintenance (Frequency choices = weekly, bimonthly, monthly, trimester ... yearly) I need to figure the month of the next schedule maintenance to build a monthly report. The report find criteria is the Next Maintenance field. Exemple: we're in november: --description ------start …

Hey, I have a calculation that I can't even begin to guess at the solution; all help appreciated. We have a database of students in various stages of the admissions process (eg: Inquired, Visiting, Applied, etc). We keep a history of their changes in status, including the status, the effective date of change and the amount of time elapsed since the previous change in status. So far, so good. Our admission director would like a report showing the number of students in each stage of the process (no problem), the amount of time it's taking to move them between stages (no problem), and the percentage conversion rate (problem). That last means, if we have 10…

HI All! I need to generate a report that reflects the monetary bonuses which are payable to actors when certain thresholds of box office revenue are generated by ticket sales of a motion picture. This one has had me tearing my hair out all day... 1. The thresholds are a concatenation of various factors: a. The Production Cost of the motion picture. This may be expressed as 1x Cost, 1.5x Cost, 2x Cost, etc. This Production Cost field is merely a number (in the given example, the field values would be 1, 1.5 and 2 respectively). b. The cost of prints and advertising (P&A). Along with the prior factor, the threshold may not be expressed as:…

Wondering if I could get a little help with this one. I have a portal sorting solution that uses calculated sort fields. That works fine. I also have an indicator on each column header to indicate the current sort and direction. I have just added the ability to shift click a second column header to sort by a second column. Again this all works fine. Where I am stuck is unravelling the nested case statement in the indicator field to be able to indicate the second sort order. Currently I have Case ( xsort_key = "ticket_status"; Case(xsort_order="0" ; "▲"; Case (xsort_key = "ticket_status"; Case(xsort_order="1" ; "▼";"")))) What I would like to add is a…

I have file because I want to understand theory when I see 'not IsEmpty(FilterValues( stuff ;;; stuff ))' ... I did not get it and I still don't. I thought I did but I don't. Please in file. A vl list and a field with a value. two calculations same but reversed. How can they both give me the same results? one should filter the other. Can someone please explain this strangeness? It must be too simple that I am trying to look higher up for it. Theory_filterLists.zip

Anyone know where I might recover a bunch of handy calculations I have always had listed on the "Watch" tab of the data viewer? Any idea how they dissappeared? Thanks

Hello Everyone I have a layout which is called thebusiness and in that layout there is a webviewer. I have a hidden layout in a remote table called randompages in which I have only 2 fields called URL and Seconds. I would like the user to be able to see different urls in the webviewer in the layout thebusiness. There are 10 URls in randompages. I want the urls in the webviewer in the layout thebusiness to display for the number of seconds entered in the field Seconds and then move to the next record and do the same thing in that next record. I would like the whole thing to loop. The effect is like a slide show in the webviewer with timed views of the urls and th…

Is there a way in filemaker to calculate angle of slope from rise & run?

Please help. I'm a newbie, but I'm hopeful that what I want to do here is possible. : / I have several fields for my products (size, color, design, etc.), each with from 12 to 30 values each. I would like to assign a number to each value in each field, then, based on the combination of these fields, produce a unique number (ITEM CODE) for each product. Here is an example of what I would like to do: SIZE Field -small (assign number "1") -medium (assign number "2") -large (assign number "3") COLOR field -red (assign number "01") -blue (assign number "02") -yellow (assign number "03") -etc... DESIGN field -square (assign nu…

Substitute ( field ; "/" ; "\"\" ) I can not get it to work. I want to find all / in a field and change it to \. I realize I am mis-escaping it but I do not understand it. I have been sitting in the calculation box for 15 minutes now and cannot get out. Or maybe I've been sitting here for days ... it feels like it. I don't know what it is called so I can't look it up. I tried 'escaping' but no results. I tried \ but not enough characters.

a lot of calculations (and scripts too) busted for me when i moved files from fp5 to fp7 format, simply because the leftwords and rightwords function does not longer evaluate the same way. for example, in fp5, the expression 10/5/2011 is three words, but in fp7 its only one word. in time fields, it gets even better: in the fp5 world a time is three words, but in fp7, depending on the users system settings, the time might be 11:06:43 PM (two words) or 23:06:43 (one word). maybe i don't see the forest for the trees, but how do you go about fixing calcs that depend on the proper number of words when you can't even be sure how many there will be? thanks, …

hello Everyone; i am trying to figure out where to start with this or what am i missing. i am attempting to : save primary ID & foreign Key fields along with an order ID on current table ( invoice table) Sometimes i need to "HOLD" the record for later and everything i have seen shows me that i only need to create a list. TO me this makes no sense. Or is it just me. Eg. If i do a script design like this: Save Current record [ commit ] copy invoice ID into a new field ( held_records ) conditionally format a button that was grayed out to be either Green/ red & show text " UNHOLD " record(s) If user chooses to "UNHOLD" rec…

I have a Personnel table with a "Last" field. On a a related table's layout, there's a field that displays all "Last" names from the "Personnel" table in a drop down (Safety Clearance:Worker 1). I would like to set it up so that the following happens when choosing a name in the "worker 1" field: (for user who's Personnel::Last = "Smith", and Account Name = "jsmith", 2nd user- "Doe", "jdoe" respectively ) Initially, the "Worker 1" field with the Personnel::Last dropdown is empty. Initially, jdoe is logged in instead of jsmith (or anyone besides jsmith) IF Worker 1 = Personnel::Last for current user THEN commit record, go to next field ELSE Reve…

New to FM11. I have a pretty simple invoicing database that uses two related tables "EquipmentBilling" and "Invoices" "Invoices" has a calculation field Discount that determines a discount based on what department a user is from: If ( EquipmentBilling::Department <> "25380000" ; 0; If (Equipment_Used="Imaging Core Computers"; 0; If (Equipment_Used="Fortessa Flow Cytometer"; 0; If (Equipment_Used="Training"; 0 ;Cost * .75)))) The problem is that processing of the first line of this calculation, I guess passing the value of the department from the related EquipmentBilling to Invoices appears slow. So a database user enters a record but no dis…

Can you help? I am trying to use a calculation to populate a repeating field. I did it with a script... and now I want to do it as a calc to speed up the process. I have a file name... I'm trying to identify all the positions of the character / in the file path. So I have a repeating field with 15 repeats. I set the field as a calc... and then the first calc is Position ( server_string ; "/" ; 0 ; 1 ) Works great, now I want repeating field 2 to grab the position of the 2nd instance, repeat 3 to grab the position of the 3rd instance and so on. I know how to get data from repeating fields, but how do I put the data in it as part of the calc…

Hello: I need to create a calculation field that takes "Field 1" adds leading 0's based adding up to 8 digits. Must be 8 digits Adds leading 0's assumes two decimal points (last two digits are two decimal points) Filed 1 Caclulation Field 1 = 00000100 1.2 = 00000120 12 = 00001200 120 = 00012000 120.50 = 00012050 Thanks for any help, I'm stumped. JW

Hey everyone; just wondering if it is Good practice or choice whether to use a function like "UUID" or just teh plain built-in filemaker serial number option? thanks, -i

Hello, I'm trying to search a large text field that contains a list of medications to see if any one of a large list of more specific drug names is listed. I have a second field with different classes of drugs and we are trying to both: 1.) get a list of specific drugs being taken and 2.) see which classes the drugs belong to. My idea was to use patterncount() on the list of actual medications and make a calculation field using case() to check off the drug classes based on the contents of the medication list. I am having trouble figuring out quite how to do this because it seems my search string would need to contain multiple words with an "or" relationship …

Is the topic title possible without tailoring each calculation via its relationship to suit the sub summary? In other works my field is the product of two summary fields and the result always resolves via its relationship and not the subsummary.

Hello all, I moved a folder and all of my container fields now show error messages because they map to the older folder location and not he new one. I found this amazingly inspiring solution online: http://help.filemaker.com/app/answers/detail/a_id/549/kw/patterncount Unfortunately, when I get to the last part, the ImagePath field I created is grayed out, so I cannot equate it to File Name for the import. Does anyone have any ideas why that might be? The images are all PDF documents imported as Quicktime. I don't know if that matters, but I have a feeling it might. Anyone able to help? Thanks! B

Hello, I apologize in advance as I am still very new to FM Pro. I have been tasked with making a DB in FM Pro 11 That manages a record of Clients and Individual Contacts with those clients. As part of the DB I have to generate reports which collect certain statistics for our funding bodies. While working on this I got a little confused and was hoping someone here could set me straight As part of my brief I am having to import a a bunch of old records from an excel spreadsheet. One key stat I have to calculate is the time interval between a contact being requested and contact actually being made with a client. I have the two dates in t…

Hi there, I have a Table with about 5 fields in it, including one Serial Number field which just increments by 1. The one other unique ID field is the EU_ID field, which is a 9 digit unique number. I have a script that imports data from Excel into this table. How can I set it so if a EU_ID already exists in the database, it can't be added again? I tried changing the EU_ID field to "Unique Value, Custom Message if Failed" and that didnt work. I am assuming it didnt work because that would only catch if the EU_ID was duplicated in the same Excel sheet. Any ideas? Thanks

Ive been using Filemaker for a number of years and when I created the database, I set the customers number to be auto created as a serial number incremented by 1 each time a record was created. I now have 3000 customers that starts with 1, 2, 3, 4, - 3000. Is there away to modifier the existing numbers so as customer 1 is now 0001 and 2 is now 0002 etc.

Howdy, all: I'm trying to tweak the CF in the attached solution to accommodate our hockey-crazed neighbors to the north so that when a user enters a date into the Date__lxd field (e.g., 09.28.2011), selects dd-MON-yyyy from _g__DateFormatChoice__gxt's pop-up menu, the output in Date__lct will be 28-SEP-2011. Here's the text of the CF: Let( date = GetAsDate( the_date ); Case( IsEmpty( date ); ""; Substitute( format; [ "dd"; Right( "0" & Day( date ); 2 ) ]; [ "mm"; Right( "0" & Month( date ); 2 ) ]; [ "yyyy"; Year( date ) ]; [ "yy"; Right( "0" & Year( date ); 2 ) ] ) ) ) Along these …

I have a field with an employees full name (Christian Name and Surname). I know i can use a LEFT(Full Name Field;1) to get the first letter of a word. But is there a way to split an entire word into another field. I want to have a value list attached to a field, this will list the full names of employee's. I also want to have 2 separate fields, lets say Field1=Christian Name, and Field2=Surname. Is there a way when a full name is selected from the value list, that the Christian name is placed into Field1 and the Surname is placed into Field2 without manually typing them in?

I'm hoping that someone can help me with a lookup problem. Briefly, I have two tables in a database, one with the reference data in it and the other with the active records. I'm developing a chemical structure drawing package that will allow students to practice drawing molecules that have a particular functional group in them. They select the functional group they want to draw, the first table then does a calculation based on that which returns a question number at random within a pre-set number of related records, and based on the question number generated, a lookup function fills the field data from the second table. The problem that I have is that when I create a new …

I'm not sure how to approach this. I see I can use patterncount to count on a single string, but not any (is valid?). Within a given text string, I want to substitute "A" for every Nth occurrence of any whole word matching an item in list "B". Example: He walked to the store, checked his pocket and realizing he forgot his wallet, he groaned. result Bob walked to the store, checked his pocket and realizing he forgot his wallet, Bob groaned. List in B, count any of: He he his His A= Bob

hey everyone. i have an issue. I am wanting to do something like my user enters cake flavor & then qty and it appears on a portal, however. I would also like there to be a running total on the page somewhere that can calculate the total as they are entered. i am using a portal ---child over my text field, then qty the same. Only thing is how to calculate a running total. thanks, -i p.s. _ Attached file may help those who see what i mean fridays Copy.fp7.zip

I have been using "repeating fields" for the following calc. I am now switching to "Line Items" and my problem is this. The calc works fine for individual item pricing, but I need it to give me the Lb. Price for each item based upon the entire pounds of the order, not just the one line item. (each line item also has an order number unique to each order". Your help would be very appreciated as I'm a little over my head here. Pound Sell Price If(CasesOrderedSum = 0, TextToNum(""), If(Extend(Sales Detail::OrderType) = "Direct", If(NetWtCalcSum <= Lb1 , SellLbCalc1, If(NetWtCalcSum >= Lb1 + 1 and NetWtCalcSum <= Lb2 , SellLbCalc2, If(NetWtCalcSum >=…

Greetings all and thanks in advance for any help. I'm very new to FMP and am trying to setup a database at work for customer contact managment. Mainly it's just to make my life easier because my employer dosnt have any system in place and I've been given the wonderfull task of making cold call inquiries. FUN... Anyway so far I've got things workeing pretty well I can create a record search even set up a special field to make checking the company names against those already in the database so I wouldnt end up with too many duplicates and didnt have to retype the company name over and over. Any way that's beside the point. What I'm looking to do now is even mor…

Hi There I need to know how to make the first letter of the 1st word that I input to be capitalized. I feel as I should know the answer to this but my mind is blank. Hope someone can help. Thanks

I got the portal row number to work thanks to great input from you folks. Another question regarding wonderful portals. I am exporting content to a legacy system. The export works perfectly for the first row of a portal but I don't know how to export portal row content in order. Go to Portal Row works for the script I have in the DB but setting up a script using portal row next does not change the the content in the text export. The script I am using runs without issue regarding the first row but I don't know the drill to modify the script to export portal row 2-last to text. Any help on this would be terrific . TIA

I'm looking to create a field in an invoice solution that will parse and evaluate math expressions on the fly. For example, I would like to be able to enter in the "Quantity" field, an expression such as 19+123, or 37.56/7, and have the field automagically convert it to the appropriate result. Any ideas on how to do this? Is there a custom function or plug in for this? Thanks much, George Page seabreezefarm.net

I have a database which stores records, each of which has a "Type", as defined by a field with a value list. I want to create a unique reference number for each record based on its SORTED position, in conjunction with the type of field. In other words, the records are sorted by Type, then alphabetically within each type, and I want to have a field on the report which shows each record's numerical order. And yes, if I add a new record, then they all get a new number allocated to them. Here's the catch: What might be really nice is for each type's number to start at a round number, thus: 1-99 for the first type; 100-199 for the next type, and so on. …

I'm just getting started so bear with me. I've just imported a group of employee's who can retire after 25 years of service. I need to calculate the their retirement date and the age they will be when they retire. Thanks, T.

Hi all, I'm new to the forum--and new to programming FMP for my boss! I understand the logical concepts of programming, and how the FMP UI works for table relationships and scripts, but I don't know specific function commands. Now I'm trying to create a database for the manufacturing company I work for, and can't find a function to create a text string I want in a calculated field. Apologies if this question has already been answered, but I can't figure out how to word a search for this!! I have two tables related exactly like the "Contact Management" and "Notes" tables in the starter template: each record in table 1 has an "k_ID" number that matches a group o…

I try to find out a formula to calculate UPDATE age,,, here is the formula I use right now Year(Get(CurrentDate)) - Year(DateOfBirth) but it will not update, for example: DateOfBirth = 09/18/1980 Today= 09/17/2011 so,, today age is 29 it should turn to 30 tomorrow,,, but the formula wont update the age..... any solution, thank you

hi! I would like to ask for help to solve a numbering problem. I have 3 fields. It should work the following way. Field3 should get/generate the automatic number by watching cell field1 and field2. the values of field1 and field2 would be assigned manually. like this: field1 field2 field3 2008.002 04.05 1 2008.002 04.05 2 2008.002 04 1 2008.002 04.05 3 2007.011 04 1 2007.011 04 2 So if there are already identical field 1 and 2, than the ordinal number should jump +1. And all of this should happen automatically. Thx!

Have Fields FirstName and Surname.+ Name How to calc FirstName and Surname to = Name

I am trying create a single calculation field to combine these 5 text fields values with a custom delimiter (comma, bullet, etc.), but remove the keywords that are identical across the different tag categories. For instance: Adventure, Adventure, Horror, Horror > Adventure, Horror I have created a music track tag database that allows me to pull genre and mood descriptions from a related table and apply to my records via check box. I have the tags (keywords) arranged into different types of track tags, Arrangement (instruments), Industry (industry specific terms), Genre, Application (Music For..) and Descriptive. Some keywords are in multiple catego…

I know this has been covered before but I can't get the other solutions to work specifically for what I want to do. Job_Number Table Hours Logged Table Job_No Field (related) Job_No Field Number_of_hours Employee Name In table form here is what I get. (For Hours Logged Table) Job_No Employee 1 Mike 1 Mike 1 Mike 1 Jason 1 Sandra 1 Sandra I want a calculation that will = Jason, Mike, Sandra How can I do this?

Hey Gang; just was wondering why would you use the "Evaluate function in this " Case ( Get ( SortState ) = 0 or Get ( SortState ) = 2; Evaluate ( "Let ( " & ~sortVarIndicator & " = " & Quote ( "" ) & "; False )"); ~isSameSortField and Get ( SortState ); Evaluate ( "Let ( " & ~sortVarIndicator & " = " & Quote ( ~sortIndicator ) & "; True )"); instead of only just using the let ( ) function. - sometimes i dont know why i should use Evaluate & just Let () . can someone explain? -ian

Hi I a having a problem trying to keep a running total on paper stock, i think it must be pretty simple but i just can't work it out. I have got 3 fields one is 'amount in stock' the other is 'remove stock' the other 'add stock'. I want to have amount of stock starting on 0 and then be able to enter an amount in add stock say 1000 and then be able to enter say 100 in remove stock and then amount in stock should show 900. The problem i am having is that when i clear 'remove stock' and 'add stock' 'amount in stock' goes to zero wheras i want it to remember that it was on 900. Hope this makes sense and that someone can help. Thanks paul.

I am sure there is an explanation for this but I have a calculation field set to add a numerical value if a date is entered into another field. A related table then uses the numerical value as part of a calculation in the related table. My original table then enters the related calculated value in a field. It only does this for the record I was working in. I then have to click in the field of each of the other related records for the value to change. Is there a way round this? I could write a loop script that starts at the first record and enters the field in question of every record in turn but can't help wondering why it doesn't do it automatically.

hi, I want to make the phone format as dynamic.

Hello, I hope this is the right place to post. I have to change a field from calculation to number with an auto-enter calculation in order to give me the option of overriding the calculation and entering a value manually. When I make the change the values previously calculated are deleted. Is there a way to restore those values without having to re-enter information in one of the field used in the calculation? Thank you for your help.

I am trying to calculate the time elapsed based on two set of fields. I have two fields to capture the date and time something was discovered and another two fields in the same record to capture when it was responded to. I am trying to calculate the time elapsed between these two composite points in time but can only find information on calculating minute elapsed between two hour/minute fields or days elapsed between two date fields. Can anyone help me refine my strategy or create a calculation to perform this calculation? And be warned - I'm a relative novice to FM (but learning very quickly!), so be gentle with me!

I am stuck with the extend function. i have tried but no luck in getting anything to work. Can someone take a look at my file & point me in right direction? thanks, extend.fp7.zip

Hi all I can't explain myself why this calculation gives strange results if the field "range" do not start from A0. That field contains something like A0-A7 and I want the result to be: A0 A1 A2 A3 A4 A5 A6 A7 There can be many calculations, but I writed this one: Let([ start = MiddleWords ( range ; 1 ; 1 ) ; end = MiddleWords ( range ; 2 ; 1 ) ; string = Left ( 123456789 ; Length ( 10^( end - start ) ) - 1 ) ]; start & Substitute ( string ; [ 1 ; ¶ & SerialIncrement ( start ; 1 ) ] ; [ 2 ; ¶ & SerialIncrement ( start ; 2 ) ] ; [ 3 ; ¶ & SerialIncrement ( start ; 3 ) ] ; [ 4 ; ¶ & SerialIncr…

Hi guys, I currently have a grid in a layout (8x4) designed with portals. I have each cell conditionally formatted so when I tap on the cell it autopopulates todays date and the cell turns red. This is done via a script (to populate the date) and conditional formatting (for the color) What I want, and I am not sure its possible, is for the cell to turn a differnet color depending on the day of the week. 7 days a week. 7 colors. Is that possible? If not, can it be formatted based off the day number? i.e, 01, 08, 15, 22, 29 would be yellow, 02, 09, 16, 23, 30 would be green...... **EDIT**- I would prefer actually if it could be based off the day number (o…

I'm drawing a blank on this. I'd like to determine if it is 1am, and if so run a script on the robot I've set up. If I do If ( Hour ( GetCurrentTime) =1 ) it seems to run at both 1am and 1pm. It's probably simple. Do I need to use timestamp and patterncount for "am"?

My files are organized by number, each year that they are a client. Ex: First year the file has a 1, second year the file has a 2. I also have a field called total of all charges, which is linked to the file number. Whats the best/easiest way to summarize these fields across multiple file numbers? thanks

Hello everyone again, I have been doing some research and what i come up with tagging a record is usually: global field custom functions I would like something like a checkbox => status -> "HOLD INVOICE"; when that checkbox is held , it saves the file but doesnt retrieve in my reports for end of day, etc & when i uncheck the value would be " " & then i can go about my business. What would be the BEST way of doing this from a scalable aspect and not just get it done now way. thanks, -ian

I want users of my database to be able to enter a web site URL into a field in my database. Anyone familiar with Microsoft Outlook will be familiar with the Web Page Address field in the Contact form. When the user enters a value into this field and leaves the field, Outlook converts the value into an active link. When the user clicks on the link in this field on the Contact form, the web browser opens and goes to the address. I want users to be able to do the same thing on my FileMaker form layout. How do I do this? I've used a text field for this Web Page Address field. Should I be using a different field type?

Is it possible for a global calculation field in Table A to reference the SortState of Table B?

Hi Everyone, I've got a pretty standard setup of an order table and an orderlineitems table. I'm doing a print report which shows each order and the associated line items by finding a set of these line items, sorting by the orderid field, and then putting certain fields from the order (like order total, and customer name) into the subsummary. The order total is basically the dollar amounts of the line items added up plus taxes, shipping, and other fees. How can I do a grand summary of Order::Total when my report layout table is OrderLineItems ? I tried a calculation in the order lineitems table: GetSummary(Order::SumTotal ; OrderLineItems::OrderID) …

All I'm not sure if I've got a problem or I'm just panicking. I have a layout called "Sorted." I have a specific sort script that I only use on this layout. The table associated with the layout "Sorted" has 3 calculated fields that return values dependent upon the sort results. My problem/question is what happens when I go to other layouts? I'm never going to use the sort script I associate with the layout "Sorted" on other layouts. But, other layouts will be sorted based upon different criteria. So, is there a way to have a calculation only "work" (I'm sure that's the wrong phrase) or calculate based on the layout it is on? I want it t…

I'm a relatively new FM user but have been forced to hit the ground running and am doing my best to learn along the way. This particular problem is giving me some trouble. In generic terms, I have several fields in each record that have multiple possible responses (e.g. "Completed", "Incomplete", "Pending Data"). I would like to keep a running count of these responses for all records in the database - one for each field of interest. So basically, I can already look at the summary page for each individual record and see which tasks need to be completed, but I also want to be able to look at the database globally and use the existing data to say "X number of records st…

I have a status field in a system that seems to change for reasons that no one can quite explain. Clearly an operator or a script of some sort is inadvertently touching the data in that field. To help troubleshoot I'd like to set up a field level audit trail to record the contents and modification history of that field over time. I seem recall that folks use the evaluate function to create a trail like this, but I'm unable to replicate the process. Can anyone help with this technique, or provide some other tip that will allow me to track data on that one field's history? Thanks in advance for any suggestions.

I have a field for people names and a field for their birthdays. I like to have a field that calculates the remaining days for their birthdays so that I can sort the upcoming birthdays in an ascending or descending order. Thanks.