Calculation Engine (Define Fields)

Hello, I have been trying to work out how to calculate the time difference between two timestamps, but appreciating non working hours and weekends. Basically I have a support database which creates a timestamp when a support job is raised. I'd like to be able to define a response time for four different types of support requests we receive, such as: High priority = 2Hrs Medium Priority = 4Hrs Low priority = 8Hrs Our working time is M-F, 9:00 to 18:00. So for example if a support job is logged at 17:00 on a Monday as high priority (2Hrs), then our target response would be by 10am the next day (Tuesday). Equally if the call was logged at 17:00 on…

Hello all! I have a drop-down list with numbers (1,2,3,4,5) and another one with numbers, but could be anything else, letters, codes etc. What I want is to create a field where a result will appear when someone selects one value from one field and another from the second field. Let me give you an example Let's name the first field "numbers" and the second "codes". What I want is to write a script, where numbers=1, codes=A, result=100 numbers=1, codes=B, result=125 numbers=3, codes=B, result=375 or the values from the code field will equal something like A=100, B=125, C=150 ... W=737 etc Is it possible and if so, how? Thank you very much!

I'm working on updating a database solution in which there is a Field, call it TYPE, which has been used with a Checkbox data-entry field. In the updated solution I am going to convert this field into a related table so that the multiple values can be manipulated on a more granular level. So, I understand that TYPE is currently a Text field with multiple values separated by carriage returns. I know I've done this before, but have forgotten how it is that I managed to take the data within a field like this and split the carriage-returned values into individual records in a child table. A looping script, I'm thinking? But I'm forgetting what the steps are -…

FileMaker Developer 7. Normally I use my SetCell custom function to create a vector or matrix of cells with data. However, I'm in a situation where I must use a repeating field for purposes of screen layout and alignment. And no, a portal won't work here. I can use a script to populate the repeating field one element of a time--there appears to be no way to increment the element index and loop. Is there? Also: is there a way to clear the repetitions all at once (at the beginning) without having to step through each element? I have formatted the repeating field to have scroll bars--but these won't display! I can see the first 27 repetitions on the screen …

Hi, I have a field in a db that has a value list attached to it and checkboxes. I need to make calculation fields that will look at the field and if it contains certain text, will put "T" in the calculation field. Field Name: FEDHAZ Value List Name: Hazard Category Values: a. Fire b. Reactive c. Pressure Release d. Acute Health e. Chronic Health Since a chemical can have more than one of these values, and be checked in any order, there's quite a bit of variation as to what text the field can contain. I need to make a field, Acute, that if the field contains the "d. Acute Health" text, it will fill the Acute field with the letter "T". I co…

Hi, Could someone help me with creating calculated field which filter my table twice and then take specific value as value for field for example: for ( field 1 = 1) for ( field 2 = 2) use field 3 How to create for loop in filemaker so i can nested Filter ( textToFilter ; filterText ) twice to filter table content and get field 3 as a result. Any ideas? Thanks!

One of the features I would like to add to my database is a reminder of actions that need to be done today for any given project. For example, if I need to contact a specific printer on February 3rd for Project 1, and contact another printer on February 10th for Project 5, then when these dates roll around I want a reminder to appear at the top of the Project layout. I've set up a table for Special Instructions, and portals in the Project layout to add dates and special instructions notes. I've also set up a calculation field (Reminder) on the Project layout that pulls the content from the special instructions notes field and displays it at the top of the Projec…

Hello, I am Trying to figure out how to do a layout/Calculation that I need to do the following: I already have it so that it breaks out records into sequences and calculates total costs. Within the Total Costs there is a possibility of 3 Facilities that are placing bids. I would like to have something set up so that I can see the summary of Each vendor. The field name I have is "Facility" and there are 3 possible options. "Facility 1", "Facility 2", and "Facility 3". So I would like to calculate the costs of the found records for each facility. Would appreciate any help on how to do this!! THANKS FMF!

I have a calculated field that tests for the presence of the word "Guard" in a text field. If present, the word "SEALED" is inserted into that calculated field. And that works...but, if the word Guard is not present, the calculated field shows a question mark instead of being empty. Here's the calc I'm using (result text, do not evaluate..., do not store...): If ( PatternCount ( Electronic Recording::Caption: ; "Guard" ) ; "SEALED" ; Electronic Recording::SEALEDtranscript ) Thanks, Kevin

The following custom function allows for variables to receive names "on the fly" SetGlobalVariable [name; value; repetition] Parameters: name, repetition, value Evaluate(" Let( $$" & name & "[" & repetition & "] = \"" & value & "\" ; \"\" ) " ) The naming portion works fine (feed it Get(LayoutName) for example), however in the value portion, it replaces line breaks ("¶") with spaces (" ")? If you feed the function a value which contains a list, such as Raw apple Juicy mango Bad apricot it stores it as Raw apple Juicy mango Bad apricot I would like it to keep the line breaks so that I can w…

below are three layout first one called Tracking activities form where users input there information...the second one is a report layout that holds the required information when report is generated...and the third one is a reporting tool that allows a users to select what report he wants and date range which then would check and display all the records in the report layout....but am not sure how to exactly do this..with the scripting.... Please assist <removed link> My idea is when a user has keyed in all the information in the form and save...he can go to the reporting tool layout and have a drop down list with all the reports names...and can choose d…

Is there a way to string together field names in a Not IsEmpty calculation instead of writing: Not IsEmpty (field1) and Not IsEmpty (field2) and Not IsEmpty (field3) etc., etc.? Or, in the alternative, is there a way to represent a range of consecutive fields such as Not IsEmpty (field1~field12)? Thanks, Kevin

Need some help with what seems like it should be simple. Would like to increment a number in a table based on the value of another field. If contractnumber = contractnumber then contractcount is incremented What I am currently doing (which doesn't work entirely) is a self-join calculation that uses Count. Count ( sj_invoiceCount::contractNumber) This works for the most part but does not place a value (the number 1) in the first record. The results I am looking for are in records 1-10 but what actually happens is shown in records 11-15. invoicenumbers.fp7.zip

Hi, I was wondering if it is possible to mimic the summary field type by a calculation so that I can minimize the fields in a table. I have a people, object and measurement table that stores different type of measurement. Let's say a person has two objects (i.e. cat and dog) and the object's height and weight have been measured three times. For the person, I am interested in getting the sum of each type of measurement (height and weight) for the first measurement and then sum of all the subsequent measurements. What I am currently doing is defining two additional fields in the measurement table: height_baseline (calculation to determine if this is the first or f…

I am trying to define a calculation to check if the name field has changed from the next record, and add a counter if so like: name order DESC A 3 A 3 B 2 C 1 Case(Get(Postnumber)=Get(FoundPosts);1;Let([ this=name; next=GetNthRecord(name;Get(Postnumber)+1); value=GetNthRecord(order DESC;Get(Postnumber)+1)]; Case(Exact(this;next);value;value+1))) (I've translated the get functions from swedish to english as I remember them - if wrong it's my bad) The calculation above functions fine up till 173 records. Adding record 174 the calculation starts displaying "?". Sample file attached. I have tried declaring va…

I have a simple time entry collection filemaker go solution that hooks up to my server Employee ID, Date, Time start, Time end I need to validate that the employee doesn't overlap times (enter 8-5 twice for the same date). I'm not quite sure how to go about it. Can anyone point me in the right direction? Thanks bunches!!!

Not Sure if This is in the Right Area to Ask this Alright Here is what i have , I have a Layout that includes to following items 1) Billing Type..................................... 2)Contract Amount...................................... 3) Billing total Weekly--------------------------------------------A Number--------------------------------------------- Answer= Contract Amount / 52 Monthly ------------------------------------------A Number--------------------------------------------- Answer= Contract Amount / 12 Quarterly ----------------------------------------A Number--------------------------------------------- Answer= Contract Amount / 4 Semi - A…

Hi all, I am new to the forum and appreciate any help. I am working with a data base import. After import, I need to extract different data within a single field. Here is the example: 1995 Ford Escort Wagon, new transmission, tags, $900 obo. Groveland,CA. Call 209-922-0000 The field is called "description" I have been able to cut the first line prior to the comma, but can't seem to get any of the rest with any accuracy. The order changes with each record except for first line. I would like to be able to extract the following: First Line, Dollars, City, State and paste into separate fields. I apologize in advance if this is a rudimentary q…

In one layout, I'd like to display multiple aggregate information grouped different ways at the same time. For example, oversimplifying the database: Group-----Image-----Value-----Image Average-----Group Average 0 Glu---------1---------24----------------25---------------------30 0 Glu---------1---------26 0 Glu---------2---------34---------35 0 Glu---------2---------36 3 Glu---------1---------19 etc. The left three fields represent the input data, and the right two columns the calculations. I tried setting up relationships and using the Average function but it didn't work. I can perform sub summaries via parts or summary calculations, but just for the…

Hi There, I have a database with 1000's of children's games. I have teachers in other parts of the world classifying the games into categories. When they log in, a find is performed to bring up a specific 100 games for that teacher to categorise based on topic. The teacher categorising the Games to teach the animals (100 games) has a readout telling the teacher that s/he is on E.g. Game 4 out of 100 which is a calcualtion I made. Once they are done they check the 'Done' box in the Games::Done field. The teachers have requested that there is a readout telling them how many games they have completed and I am struggling to create a formula to acheive this. …

Okay, this is the first time I've had occasion to use the Calculation engine to format a field for display, so I'd appreciate a critique here. 1) I have a situation where I have a field that may or may not have data in it. If it does, I want the layout to display a little label ("Laboratory") and then on the next line, below it, the name of the lab (Grants::Laboratory). If it's empty, I don't want it to display anything at all. So I have Case ( IsEmpty ( Grants::Laboratory ) ; "" ; "Laboratory" & ¶ & Grants::Laboratory ) okay. this works. 2) I would like to format the name of the Laboratory so that it is distinct from the label. So at t…

I have an address field, formatted like this: Monkey Time Productions, Inc. 455 Gerald Avenue Bronx, NY 10452 I need to populate a field with the first line of the address (which I can do), plus add the city name from the third line (sometimes it's on the fourth line, but it's always on the last line), enclosed in parentheses only when my "Active" field is set to Yes. I need the result to look like this: Monkey Time Productions, Inc. (Bronx) I'm tearing my hair out trying to figure out how to combine LeftWords with GetValue to "strip" the city name out. Here's the calculation with the most success I've had thus far: Case ( Active? = "Yes" ; …

I have two dates the current date today and an expiration of a certificate.... I need a calculation to give me a flag to say Renew, or expired..... Renew if the current date is less than 30 from the expiration and if it past the date in the field to say Expired. Thanks in Advance. Randy,

Not sure where to post. I have attached file of example. On a related Quotes layout (related to Persons) I have calculation from Persons which creates an address label. I put this label everywhere, makes it easier than individual fields. Space is tight on my forms so I only want to show this single calculation. But I would like something strange, no surprise, right? If someone clicks onto this calculation in pink, depending upon where their blinker lands, I want to put real fields underneath the calc and, if they click, let them enter the real field under it. Of course the calc does not line up exactly with the fields if you look at city state and zip. …

I'm trying to calculate in years, months, and days how long it is between a date on a past record in FileMaker to the current date. I have a calculation field that I thought was working perfectly . . . until I got to 1/1/12 and then the calculation starting going backward. The calculation in the calc field is: GetAsText ( Year ( Get ( CurrentDate ) ) - Year ( SermonDate ) - If ( Get ( CurrentDate ) < Date ( Month ( SermonDate ) ; Day ( SermonDate ) ; Year ( Get ( CurrentDate ) ) ) ; 1 ; 0 ) ) & " Years, " & GetAsText ( Mod ( Month ( Get ( CurrentDate ) ) - Month ( SermonDate ) + 12 - If ( Day ( Get ( CurrentDate ) ) < Day ( SermonDate ) ; 1 ; 0 ) ; …

Hello All, I'm trying to figure out a way to calculate the number of years since the date of a musical composition. This is mainly to filter "contemporary" composition from older compositions. I would enter the date of the composition and then have a field subtract that from the current year. I was hoping that there would be a "Get (Current Year)" function, but I see none. So I've tried creating such a field with the Get (Current Date) function; then I tried to truncate this so that I would just get current year. But I can't figure out way get it to do that. I would appreciate any help you can offer. Thank you

Good morning. I am building my first database in FileMaker, and I've got most of what I want to do figured out. However, I do have one thing that I would like to have work better. I have a portal in one of my layouts that shows Contact information, which is of course pulled from the Contacts table. Right now I'm using a merge field to show the following information formatted like this: Firstname Lastname Job Title, Company However, I don't know the job titles for everyone in the Contacts list, so that field is sometimes blank. This means that the resulting merge field in the portal looks like this: Firstname Lastname , Company Since I'm a …

I am trying to create a playlist track number calculation that will assign incremented track numbers to children records in a final cues table. The children tracks are versions of the original tracks whose track numbers exist in the playlist table. Tables are as follows: playlist playlist_items (join) finalCues All track numbers are assigned in the playlist_items and the finalCues are children tracks of the playlist_items. There will be many versions of a playlist_items for instance a 90, 30, 90 ALT B, 90 ALT C, 90 ALT D, 30 ALT B, 30 ALT C and 30 ALT D. What I would like is to be able to is to take all the 90's and use the track number from the play…

I want a proper running balance, this is what i want, actually i am running a simple (Balance = Target-Target Achieved) formula which updates the values in all the workplans as the same which was given in the last record. For example The March 2012 workplan target records are showing in the January 2012, although this is not what i want. January 2012 Activity-----------Target--------Target Achieved----------Balance Rugby 10 2 8 Soccer 20 5 15 Baseball 12 3 9 Feburary 2012 A…

I have a container field where I store references to pdf files. My users know to check the store reference box everytime they store a file in the field, but it's easy to forget. Is there a way to have the "store only a reference" box checked by default? Thanks, Kevin

I have a dashboard in my school Information Management database. I have used the technique of single portal rows to display Aggregate data such as how many total classes there are during the week and a differnt portal (& table) to display how many students attend classes (Some once a week, some twice). Ok, so far so good, 36 classes, 193 class attendees. How on earth's name can I create a calculation that shows me the average no. of students per class. (5.36) because as soon as I take this summary aggregate field out of the portal it displays just a single records data? Mmmm, I'm a bit lost here. Thanks in advance for all your help.

I have a repeating number field (20 reps) which contains location names allong a route. E.g: A,B,C,D & E in the first 5 repetitions and nothing in the rest. I need to create a calculation based on each being a possible starting position, but only the following ones being possible destinations. ie: the calc field should contain: A-B A-C A-D A-E B-C B-D B-E C-D C-E D-E It should also not include any of the blank repetitions producing lines such as "A-" or "B-". And it may contain a full 20 locations (E.g: A-T) I tried something allong the lines of: GetRepetion(<Field>,1) & "-" & GetRepetion(<Field>,2) & "<paragraph…

I'm working to add a user-selected "switch" that designates whether to store an image in a container field, or only a reference. The store and display parts are working (i.e., stored images do display properly). But I thought I would see a clear indication of what was actually in the container (image or file path). Instead, a calculated field (GetAsText(Container)) shows either the image size ... or only the inserted file name (no path). Attached is a screen capture of my test results. Lamb and Blue were inserted using a right click + Insert Picture + Store Reference operation. The others were done without the Store Reference designation. Shouldn't the calc…

I have text field which can have multiple lines entered; yeah I know, don't do that (smile) but I need this one because it is not regular field but global for me. If I type two lines in a text can I make a calculation repetition show them so that the first line is in rep 1 and the second is in rep 2 and so on? I know there is limit but that is okay. It is small number of items. I search and find extend but I cannot make them work with what I need. I read probably 50 of them. Somehow it escapes me.

I need to create a summary field to total a portion of a field but still show all the records that are not part of the summary in the list view. Is this possible? Thanks in advance.

Am writing an EMR solution using FM advanced 8.5. Part of the EMR is billing section that i would like to have electronically transmitted to payers and not paper claims. In order to have that i have to be able to plug my solution to the payers EDI which uses a standard ANSI ASC X12 protocol. Are there any links or articles that can help me in approaching this issue.

Hello All This should be easy, but I can't seem to get it. I need filemaker to open a text file, based on the current record. There will be thousands of records, so I do not want to add a data source for each one. The best way I've found is using the send event script step. This will allow me to open the file based on the record. Here is the problem. This is the path: \\192.168.2.83\HerdArc\WMIC\SERVER12-PC\systeminfo.txt The "Server12-PC" changes based on record, and systeminfo.txt changes based on which script parameter is passed by the button. I can use this path as straight text in the send event, and it works perfectly, but I can not c…

I'm trying to convert 00:00:00 hh:mm:ss to decimal time. I have 1h 45min (1:45) I want a field that has 1.75 hrs in decimal time. How can I do this?

trying to find a way to auto-enter a future date upon creation example: client not only wants to track a creation date of the customer record but also wants to contact them in 3months and then again in 6 months my thought was to have auto-complete fields set 3 and 6 months out and then have a log on script remind them off a range of those date fields in case it happens to land on a weekend or holiday have a radio button for yes or no as far as have been contacted and then omit in the search any better thoughts or could someone please explain the date/calc function a little further thank you in advance for your time

Hi, I've got a clalc field as follows Empl 1::average= .07865 Empl 2::average= .8723 Empl 3::average= .5432 Empl 4::average = 1.22234 and I want to put another field "ranking" so that Empl 1::ranking=4 Empl 2::ranking=2 Empl 3::ranking=3 Empl 4::ranking=1 it's in an already sorted layout that can't be re-sorted (otherwise I'll loose the summary calcs) and it doesn't need to be displayed in order (1,2,3 etc) just show the ranking calc field, so I'm hoping some mathimatical genius might come up with a calculation ? cheers

I am writing a database to create a complex and sophisticated index to a large catalogue. I have a table of Categories - these are the actual text that appears in the printed index. These are related to a table Page Entry which has a page number and a ProductName. The ProductName is related to one or more Categories. So, the Category 'Barbecue' appears on pages 25, 32, 40, 41, 42 and 50. I have to manipulate these numbers and eventually output 'Barbecue [tab] 25, 32, 40-42, 50' I have got the numbers into my 'Category' field by 'List ( Page Instances::Page No )'. However, if the entry on page 42 is added later than the one on page 50, I end up wit…

When I modify or new-record in a related field, I want a alarm with costom dialog in server. I want users in server to see my costom dialog in the same time. like a message " there is a New Change in field " When I do this in Set script triggers (onobjectmodifiy), costum dialog is seen just by me, but I want users in server to see this costum dialog in the same time. what is the calculation about this ?

Hello all. Happy 2012 !! I am a newbie, and have been searching for a solution to my problem unsuccessfully so far. I am hoping someone can help me with my problem. I am using FM Pro 11 to create my databases to use in FM GO for Ipad. The database I have has a field called "Notes" which I use to track general information as I receive it. I have created a script that allows me to insert the current date and time into my notes field before I start inputting my notes. InsertCurrentDate[] does the trick to insert the current date, but it is not the format that I need. It inserts "08/01/2012" But I need it to insert the date in the format of " Sun 8 Jan, 2012" An exa…

Sorry to ask such a simple question: I have many DB's with summary fields with child tables, but now I am making a flat DB. It's a checkbook, and I merely need a field that gives me a sum of one field for all of the records. A check is recorded as a negative number and a deposit as a positive number. How to make calculated summary field of that field in this flat DB? THANK YOU in advance.

Hi, I'm trying to make a calculation that will remove the last three characters from a field. I have imported a bunch of picture files and I want to have a field that has the label of the file without the file type info. For example: FILENAME: A1-3.jpg (full filename) NAME: A1-3 (the name without the extension) Does anyone know how I can make this work in the name field? Is it a calculation? Thanks, Ro

Hi there, I would love some assistance with trying to get a count of type working. To give a context. I have 3 tables - Module, Lesson & Lesson Deeper. I would like to be able to show the status of my progress in the lesson deeper level, and then to be able to count on each of the other levels also. Each of the layers is effected automatically by what is triggered on the Lesson Deeper level, and has 3 responses - In Progress, Test It & Complete. The concept that I have tried is built on keeping the Status in a separate table, and then calculation in a related table, then using a summary field to count the related fields. I am not sure how …

I have a housing database which lists individual housing units (Unit) with the area (Area 1, Area 2, Area 3, etc.) the building is located and the number of families (1, 2, 3, etc.) housed in the building. I need to create a calculation that gives the total number of families that are associated with Area 1. So, if I have UNIT AREA FAMILIES 1 1 1 2 1 3 3 1 2 4 2 1 5 3 2 6 3 2 So, the total number of families in Area 1 is equal to 6. How do I calculate this? Thank you, Matt.

I've got a table with three global fields; one a text field, the other two numeric fields. The database is hosted by Filemaker Pro 11. So far, no problem. But I've noticed that a change to any of these three fields is not updated by either a) guests if the change is made on the host computer, or the host if changes are made on guest computers. I've never seen any behavior like this in Filemaker before. In every other instance, a change made in one record is updated everywhere as soon as the change is committed. I can't imagine there's a database setting that would allow for this sort of behavior. Anyone have any ideas?

Hello. Is it possible to use a calculation field to display a certain record? For example. A calculation field named "display" Would have a calculation to display the contents from record 29 from the field "product" in another table named "inventory". What wouldsuch a calculation be? Happy new year

Hi I have a problem grabbing a value in a text field containing a list. The list is containing values from a related record and is defined: TextField = List (RelatedTable::RelatedField) And is like: 0 10 50 100 etc. What I want to do is to loop through this list, grab each value and create a new related record in another table an paste each value into a field. Can anyone help me on the right track here. Best regards, Reynir.

Hi, I'm trying to use a new Vat rate (23%) for new Pricings from the 01-Jan-12 while keeping the old Vat amount for Pricing before that. I'm using Filemaker Pro 6 I have a "Customer Creation Date" field that adds the current date when a new record is created. I have an existing "Vat Amount" field that just calculates the vat value for 21% using "Cost Price" * .21 then adding "Cost Price" to "Vat Amount" to get the "Total Price" Cost Price Vat Amount Total Price I want to keep entries up to the 31-Dec-11 showing the 21% Vat Amount value and any entries from 01-Jan-12 to calculate the 23% Vat Amount I've created 2 new Vat fields, "…

As far as I can tell, there isn't a Get function that fetches the Top and Left coordinates (in pixels) for the main/parent window on the desktop; I need to know that for a specialized FMP application I'm working on--where on the desktop is FMP's window; the existing Get functions already provide the height and width of it. Is there a way to fetch that info from with FMP or is there a standalone app that FMP can "look" at to get those coordinates? TIA for your time/help.

[WARNING: Newbie at work] Does anyone know a way of expressing a numeric date field as just 'Month and Year' in text? I know it's possible to make it DISPLAY in that way, but I wonder if there's a calculation that can define a new field to turn 30.12.11 into 'December 2011'. I guess it would be something along the lines of: If characters 4 and 5 are '1' and '2', then that's December, AND if characters 7 and 8 are '11' that's 2011. Put me out of my misery. Philip

Hello everyone; I am curious as to use this function as i have seen mostly it is used to save states of tabcontrol panels. Is this the only use? Help is quite vague. thanks in advance, -ian

Hello Everyone; I am stuck right now. I am trying to design a payment solution, but really dont know how to perform a multi payment scenario which is what i need. EG> Customer pays $50 on a $199 invoice. Great so far if they only are making 1 payment. I am trying to create another payment, but it is not carrying over the balance in same table. My relationship is 1 order => many payments OrderID = orderID_fk Perhaps i should just write a script that takes in the amount and then just updates the record? only thing is , i would really like to keep track of Payment date, payment AMT and then apply to the Order # Any Ideas are …

I have two tables: 1. Groups: records 00, 10, 20, 30 .... 2. Subgroups: What I want to accomplish is to number related record in the subgroups table with the next number of it’s own group. The first related record of group 00 will be 01, next 02 The first related record of group 30 will be 31, next 32 and so on. The only calculation I am able to use is NumberGroup field + 1, and logically it works but only once , all related record have the same number. Any help? Thank you.

Ok, hope this is an easy question. I have a table called BBP and a table called AW and a table called RS and also MR. All the tables have records on them and I have created a field called records that uses a calculation Get (TotalRecordCount) and it displays the total number of records and updates when more are added. I then created another table called Title, with a relationship to all the others and put the records field from each of the other tables onto this table. So I have all the totals from each of my tables showing up on the title table and I try to create a calculation field to add up the 4 related fields (like - Sum (BBP::records; AW::records; RS::…

My apologies if this is a known issue, but a forum search did not find anything related. I am using FileMaker as a database to store information about chemical structures. These structures are stored as ChemDraw OLE objects embedded in a Container field. I do not believe this problem is exclusive to ChemDraw objects, however. In FileMaker 6, I could copy-and-paste these structures into an OLE-aware document (e.g. a Word or Excel document) and the object itself would be pasted. That's the desired behavior. Now, in FM8.5, these objects copy-and-paste as images. If I try to Paste Special, there is no option available which will paste the OLE object. I can fin…

How can I get the following calculation to place a Carriage Return at the end of LastName so it will display as Fred Arthur Smith 666 Green Street Have read lot's of post's but can't seem to get it working. Substitute (List(FirstName;MiddleName;LastName;Address);"¶";" ") Thanks

Hello everyone; i am trying to figure out the virtual list technique by BruceR, but all i see is a writeup on FMLAYOUT.com. Which to be honest with you doesnt make too much sense. questions from html page i have: = from site http://fmlayoutmode.com/wp/?p=1799 Then you need to do a replace on the index field for those records, using a serial number, starting from 1: "Ok, but are you gonna be doing this everytime or can this be automated, if so, scripting?" In Browse Mode on a layout for the VirtualList table (not the printout one we created), you need to create a whole pile of records. I created almost 20,000 records — well, okay, I created a script to do it f…

This is probably one of those simple things that I simply can't see ... The attached file has a field called cRandom. When I attempt to sort on this field, it doesn't sort properly and I cannot understand why. I think it is because it is unstored and random and it changes it's display of the number after it sorts? So sorting comes before unstored calculation refreshes? I cannot see the logic in why it does not sort properly. Ideas please? random questions.zip

hi have a calculation the gives the a image path. exmp:" imagemac:/mac/Users/danirub/Desktop/to/Cliant/11131/1693.tiff" why i can not see the image in the filed? thanks Dani

Hello i get from a date base in my office (sql) a filed that the contents of it is: example :"hello:how:are:you" how can i change to "hello/how/are/you" thank you Dani R

I have a calculation field that takes a Main Genre and a Sub Genre and combines them together in a field. Because of character limitations, I like to remove any recurring instance of words that are already present in the Main Genre field, but also any repeat of of words used in Sub Genre aswell. Example: mainGenre = Rock subGenre = Heavy Rock, Rock-Rap, Pop Rock, Rocktronica, Hybrid Rock, Hybrid Dramatic, Heavy Rock, Heavy Metal combinedGenre = Rock | Heavy Rap Pop Rocktronica, Hybrid, Hybrid Dramatic, Heavy, Heavy Metal I have been using Let([ mainGenre = metadata_genreMain & ¶; SubGenre = metadata_genreMainSub ]; Substit…

Hello I have a portal to hours spent on a project (PId in Projects, Belongs to PId in Hours) To make it easier to manage, I added a filter where a global in projects ("gMonth selected") is compared with the month in the hours table. It's easy to select all hours from december or november, but what if I want for instance all hours? Any hint deeply appreciated. Cheers

I am trying to use Get(ActiveFieldContents) to populate a further field that I am using for a relationship. I assume that this filed would update with whatever field I was active in etc. - But, noting I do seems to get it to populate. This driving me crazy. The Filemaker Help talks about the field that has 'focus' but doesn't define what this actually means. I am sure I am being very dense here - Any help much appreciated. Rob

Hello I have a situation that I would like to make a calculation to pick a random word in a sentence of varying length change it to a "_______" and paste in the field "missing word" . I don't know if this is possible because of the length of the sentence being variable . Also since this will be used for an english drill I would be looking for words longer than 4 letters. This may be too difficult. But thanks for answers Ps If the word didn't look like the best choice to restore to original sentence and try again. More Thanks

Hey Everyone; i am trying to test the following, what i am doing wrong here with my calculation TransposeFieldnames. appreciate any help. -i VLISTS_ALL.fp7.zip

Beginner here! I have a simple time entry app that has suddenly gotten really complicated for me and I'm looking for some help. In my Lines table I have the following (Employee, Time worked, Date, Bill rate) I need to calculate overtime (1.5) based on hours worked over a week instead of daily. Can this be a running total based on 3 fields? Just wondering what the calculation might look like... just looking for some hints here. Thanks!

Attached is a file i am trying to get to work. I cant seem to put the data from my table into the repeating field> can someone please take a look and let me know. thanks, -i VLIST_WEIRDONE Copy.fp7.zip

Happy holidays, everybody: I "get" that when dealing with timestamps you need to add time in seconds to denote a future time or date, e.g., to add a day to a timestamp the calc would be: Timestamp + 86400 ...since 60 (seconds) x 60 (minutes) x 24 (hours) = 1 day But, how do you add a month since they can be from 28 days to 31 days? In my typical unwieldy fashion, would I create a Custom Function requiring multiple IF/CASE statements where "If it's January add 31 days; February, 28 days (except for leap years)..." etc.? I'm sure there's a _much_ simpler way of coding the calculation, I'm just not seeing it. TIA for your help!

I have a field called Service_Company, which is populated from a drop-down value list. Occasionally, actually rarely, the original service company goes out of business and a new company is assigned. I can only recall this happening twice. I thought it would be a good idea to add a new calculated field called Original_Service_Company, which would populate with the original service company during data entry. I unchecked the "Do not store calculation results" checkbox, yet the results are not stored. Whenever the Service_Company field is changed, the calculation runs and stores the new service company in the Original_Service_Company field. Maybe I'm misundersta…

Good afternoon, First I wanted to thank the regular posters here as they have helped invaluably as I am converting our small business database created in 2.0 into a relational model. I have two tables "Transports" and "Legs" set up in a one to many relationship. Our main working view - "In Progress" shows the "Legs" active for the day that come from multiple transports. I am trying provide and indicator to remind our employees to have modifications to one leg carry over to other legs. (If the first leg is running 2 hours late we might need to address the time on the other legs manually) What I am struggling with is showing the "Leg" record number relat…

I have been populating a "Nickname" field with the first line of another field using the calculation: GetValue ( Address ; 1 ) I then use the Nickname field to populate a drop-down value list in a related database. It works perfectly. I now need to populate the Nickname field only on the condition that my field called "Active" is Yes. I tried: Case (Active = "Yes" ; GetValue ( Address ; 1 ) but it doesn't work. Your help is appreciated. Thank you. Kevin

Hello, I have attached the file I am working, which I am developing to record data on mutual funds held within multiple portfolios [groups of mutual funds] One table, securities monthly data main and the associated table occurrences has the data on the funds and various Sum calculations. Another table, portfolios aggregate values main is related to the occurrences of the table securities monthly data main to produce several calculations. It is in this table and the one table occurrence self related to it, that my problem is. I have entered only minimal records. The two records that illustrate my problem are the 'td waterhouse rsp' and 'rbc rsp' [t portfolio…

Hello, I run a talent agency and one problem I have with talent is their availability on certain dates. What I want is for an indicator (maybe a red dot or fill a field red or something) when a specified date conflicts with dates of unavailability I have for certain records. For example, john smith lets me know he is going out of town next week from 12/7 - 12/10 so I input it into the database. I have a new job that is on 12/8. When I go searching for john smith I want to see something come up that lets me know he is unavailable like a red flag. Is this possible?

This is something I've been wondering about for a while. It's obvious that there's a difference between how some FM functions work compared to others. For example, when you say ABS( some::field ), it returns the absolute value of the value of that field. However, when you use GetFieldName( some::field ), it doesn't use the value of the field, but rather the properties of the field itself. This is fantastically useful. So is there any way that I can make a custom function that acts like GetFieldName? Let's say I want to write a simple function that extracts the database name of a field that I pass in. It would be great if I could just call the function like …

I have posted this on the FMI website but figured it is serious enough that it deserves a post here as well. The short version of this is: There are two fields: "Field A", a text field, and "Let Field" a calculation field. Let Field contains the following calculation: Let ( [ ~fieldName = If (Field A = 1; 1; 0) ] ; ~fieldName ) After doing this, the name of "Field A" is changed to "Field B". The field name inside the let function DOES NOT CHANGE. Even more intriguing is, after changing the name of the Field A, if you open the Let Field and then try to close it by clicking "ok", it puts up the message that the field "Field A" cannot be…

I have five fields; Dates::TypeDay Dates::City Dates::State Venues::Name Dates::Info_TypeDay Type day holds such values as "Show Day", "Fly To", "Off Day In", etc. In the Dates::Info_Type Day field I need a merge fields formula that will by default show the Venue Name only. If the Venue Name is empty it needs to show the TypeDay, City, State. This is for printing purposes. Below is a Photoshopped example of both fields. This formula would affect the leftmost field only.

I recently upgraded my FileMaker from 8.5 to 11, and am hoping that I can simplify a few of the kludgier solutions I have. Considering that FMP is so powerful, it is strange that I can’t find a way to do what I need to be able to do more directly. More than likely, I can’t do it because I’m overlooking something obvious. Let me outline the situation. I am selling things. The core issue is that a single sales record contains the following data: Order No. A single item No. Quantity sold Type of sale (cash, credit, giveaway, for example) Date of sale etc. A CustomerOrder contains various information, displaying ItemNo and Qty from the SalesRecord. W…

I found this example below on FileMaker forums. Instead of rounding down all the time I would like it to round up or down based on the value. Any suggestions on how to do that? The following calculation formula will round any number down to the nearest quarter decimal (.00, .25, .50 or .75). For example, the number ... 123.10...would return 123.00. These examples would also follow: .27 -> .25 .52 -> .50 .86 -> .75 15.98 -> 15.75 25325.98756323 -> 25325.75 157.02 -> 157.00The integer values do not change. Here's the formula, assuming you have a field called Number: Int(Number) + Choose(Int(Mod(Number, 1) /.25), 0, .25, .50,.75)

Making a DB for a friend who repairs cars, he would like a layout to display 12 lines down and to have these fields in each line, "Part Description", "Price", "Cost" & "Profit" which would mean 12 fields for each totalling 48 (may even want more). I've searched FMForums and numerous Google searches and everytime I get the same old answer, make your DB using Customers, Invoices, Products and Line Items, this is just dandy if you sell Widgets & Dingdongs, just put them into the Products Tables and all's fine, popup and ID Number and there it is. But here were talking about 1000's of car parts. My question is, should I be creating separate fields or is t…

I have a filemaker solution that extracts website html from different sites and puts them into a field and it would be SO much easier to use the html in that field if all the spaces and carriage returns throughout the field were gone, I've tried like the trim4 custom function, but couldn't make it work. Any suggestions, I've attached a sample file below. SampleB.fp7.zip

I have a problem how to calculate some values. I have 2 tables (Orders and Shapes). Orders is in a relationship with Shapes. Copy database in the attachment. Eeach order covers items of one shape, specify the amount of items ordered and receives status (NEW or OLD). I'd like to know: 1. Orders in total. (xSummaryStatus) 2. OLD orders. (xSummaryStatusOLD) 3. NEW orders. (xSummaryStatusNEW) --- 4. How many items of each shape has been ordered? (TotalAmount) 5. Number of items of each shape ordered (orders with NEW status) (TotalAmountNEW) 6. Number of items of each shape ordered (orders with OLD status) (TotalAmountOLD) Sums.z…

Hi All, Completely lost on this one. I am trying to set up a calc for interest charges for the remainder of a current month. The data that is available is the "start date" and the "Interest Per Day Charge". Need the formula to count the remaining days for the given month, then multiply by the per day cost to return a value of interest due. Example: Start Date - 11/22/2011 Per Day Charge - 25.00 Number of days - (the result would be the number of days remaining in the calendar month, including the start date) so in this example the result would be 9 based on 30 days in Nov) Obviously the calc would need to adjust automatically based on the numbe…

Have what is probably a simple problem for you folks, but not for me. Building a database to record multiple data entries we manually record each time we run a new job through our chemical etching process. There are currently 17 fields per record, but for now need help keeping a running total of metal dissolved in bath (Total Metal, below). The total has to reset itself to zero each time we start a new bath. Here's a sample of what it might look like isolated to just the pertinent records: (Sorry for the underscores, but the forum software doesn't like spaces) Bath #__Job #__Weight-B/4__Weight-After__Diff'nce___Total Metal 1______1_______40_________32…

Hi everyone, I'm trying to create a field which flags whether a record has been modified today, and is modifiable by the user in case they don't want to include it in the next export (and I will set my metadata export script to flag all exported records to "No"). The auto-enter calculation I've added to the field works, but (rather obviously) it updates automatically to "Yes" as soon as the user tries to click "No". This is the calculation: If ( PatternCount ( Record Modified ; Get (CurrentDate) ) ; "Yes" ; "No" ) I guess I could try sticking this flag in a related table so it doesn't update when the record upates ... but is that the simplest solu…

I'm updating an existing database to add some functionality and unfortunately, I'm stuck dealing with a lot of repeating fields. I know they can be a pain to work with and should be avoided but the database is already in use and there are years worth of records that I have to maintain backwards compatibility with. So, on to the question... The database is for a log (similar to a captain's log) that has fields for date, time, operator's name, log comment, comment ID, and comment category. The date is a single field (1 repetition) but all the other fields are in repetitions of 30. So a given record is viewed by the date, with up to 30 log entries for the day. I ha…

So I have a calculation that extracts website html into a field in filemaker called "Hal Google Code". I then have a calculation that I'm trying to run to extract/parse part of that code from between a beginning and end point and the end result should actually be a price, here is part of the html code in which I'm trying to extract the price in bold and underlined: <td class="condition-col"> <span class="f">New</span> </td> <td class="taxship-col"> <span class="f"> <nobr>No tax</nobr> + <nobr>Shipping: $99.00</nobr> </span> </td> <td class="total-col"> <s…

I am trying to convert Indian language input into Mandarin and for some reason, the individual characters are selected incorrectly. For instance, நன்றி should be 3 characters (the obvious separation is correct), but in FileMaker the 1st character is correct and the 2nd character is all except the last vertical line. The last vertical line, according to FileMaker is the 3rd character. Looking at the Code() for each character is 2984, 2.993e+13 and 3007. While the 1st and 3rd are within normal ranges (still not right for the 3rd, but within normal ranges), the 2nd is insane. I have tried to export the field, save it in .xls format and reimport it without any effect. E…

I'm looking for a way, in a script, where I can set a variable to equal a field containing a list of numbers (separated by carriage returns), sorted in ascending order (the field has the numbers not listed in ascending order; I want the variable to equal a list of all those values, in ascending order). Is there maybe a function for this, I'm not aware of? If not, any advice? Additional info: The reason I honestly need that is because I need to be able to know which number is higher and lower than a varying percentage of the numbers in the list. If the script can get it in order, and say I needed the highest value that was lower than 65% of the …

Hello all, Firstly, I am sorry if this question has been answered elsewhere, but I wasn't sure which search terms to use to find the answer. I am trying to develop a sales solution that will pull stock from an inventory module and create a list of equipment that the client has asked for. My question is, how do I get FM to automatically add items to a pick list based on a particular code? Let me explain... I want to type in a product code, for example, "10m steel pole". The "10m steel pole" ships with 4 x "M10 Bolts" and 2 x "fixing plates". I want the bolts and the plates to be automaticall entered into the pick list when I select the pole code. …

hi guys... how can i calculate last record id of another table. Suppose i have two tables, A and B. In B, i want to calculate the last record id of table A. Pls reply soon.....

I have a field "Detected" which is a binary response, 0 or 1. The other field is Point_Type which has 5 possible values where some indicate a bird was detected (1) and some indicate a bird was not detected (0). Both are within the same table. I have over 10,000 observations. I am trying to figure out how to get Detected to autofill 0 or 1 based on the value of Point_Type. Can some one get me started? I can work the logical coding but do not know where to start. Is this a conditional value? I'm not sure if this is a calculated value either. Thanks Cat

I usually use a FileMaker custom function called ScientificNotation to round results containing a power of 10 exponent to a certain number of decimal places. However, this can be inconvenient to use at times. I'm still on FileMaker 7 Developer, and I have access to FileMaker 8.5 Advanced. But neither of these versions has an easy way to set the number of decimal places for a result containing a power of 10. I don't know if versions 9, 10, or 11 does. Example: I would like 3.122411775e-8 (the calculation result) to be displayed as 3.1224e-8. Attempting to use the "number" layout formatting does not help at all. Of course, I can use ScientificNotation but it can…

I'm not sure if there is such an animal, but is there a calculation (or maybe custom function) that will tell you which fields are being sorted and their order (ascending/descending)? I know there's the Get (SortState) command which will tell you what the current sort state is, but beyond that...don't know. This kind of calc would be handy for fields sorted while in Form view since you can't tell at a glance--as you can in List view--the sort order of a field (or fields); you either have to press Command/Control S or click forward or back a record or two to see a trend. Thanks...and a salute to our veterans, past and present, on this Veterans Day. Thank you all…

Hi, I'm relatively new to Filemaker and I'm having a bit of an issue. Not sure if this is the right category. I've created a set of records using auto enter serial (numbered M001-M400 for example). I've created a layout that shows all records in a list. At some point i must have deleted the first record by accident. I created a new record and changed the serial number to M001. Now when i go to the list M001 is at the bottom, after M400. From what i understand this has something to do with the unique number that Filemaker applies to each record? That being said, what is my best approach to fix the problem? Can i change the FM RecordID or do i need to make a …

I'm running a script to notify via email when an employee reaches their 6 month anniversary. What is the correct 'calculation' to add 6 months to their 'hire date' field? Is there a simpler way than: If (month(hire date)<7, month(hire date)+6 &"/"& day(hire date) &"/"& year (hire date), month(hire date)-6 &"/"& day(hire date) &"/"& year (hire date)+1) I tried this and it works, but does can cause problems if the calculated month does not have 31 days (or 28 for Feb). Thanks. Bill

I've created a birthdate, Now I need to extract into number form the Month, Day and Year. Certainly there is a simple solution to this... I;ve tried Brian Dunning's as follows with no success so far... Case ( Patient_DateofBirth = "January"; 1; Patient_DateofBirth = "February"; 2; Patient_DateofBirth = "March"; 3; Patient_DateofBirth = "April"; 4; Patient_DateofBirth = "May"; 5; Patient_DateofBirth = "June"; 6; Patient_DateofBirth = "July"; 7; Patient_DateofBirth = "August"; 8; Patient_DateofBirth = "September"; 9; Patient_DateofBirth = "October"; 10; Patient_DateofBirth = "Nov"; 11; Patient_DateofBirth = "November"; 1…

Hello all, I have a portal from a related table and it has multiple iterations on a particular layout with different filters to display different records of interest. I want to create a summary field that can summarize the DISPLAYED records (not all records from the portal's source table) for each portal shown. I realize it is possible to solve this dilemma by using a single portal and using a global search field to conditionally define a filter (and thereby make it possible to easily solve this issue too. But is there another way to do it that allows me to display ALL the portals at the same time with summary fields? I want to give users the most information p…