Calculation Engine (Define Fields)

In my database I have Project (parent) and Shot (child) tables. The child ID is Shot_ID which is has an auto-enter serial with unique validation. As I prepare for its launch I have been deleting all the records and testing the script that adds the child records. The script uses a portal on the parent table. The weird thing is that while the first record (Shot_ID="SHOT00001") gets added just fine, the second record (Shot_ID = "SHOT00002") triggers a validation error saying the Shot_ID is not unique. But I have debugged the script and can clearly see the value is different! All subsequent shots add fine. If I overirde the warning I get the kind of strange behaviou…

I attached a samplefile. I want a field to update based on the last portal entry. I have created a relationship to isolate the last portal record. If I create a calculated field it works fine. However, I would rather a regular field that autoenters with the same calculation. The reason is simple. The field will usually but not always be based on the last portal and the user may need to override the calculation. Please take a look. test.fp7.zip

Hello all, I'm a bit of a novice at building databases from scratch. Although I have took the available courses in my town, I still struggle with calculations and scripts. Right now, I'm working on a contact database and I want to avoid having the same contact entered more than once. I'm guessing it is a calculation set in the define field. What I would like is for a warning signal to come up when a new record is created and a combination of first name and last name is entered that matches a contact that is already in. I would really appreciate some help in figuring out how to go about this. Thank you so much!

Hi all,1st of all thanks for all help...here is my problem: I have 2 fields A & B A is contained Employees ID number (xxxx) B is container field for photo. In my employee's database all employee photo store under EmployeeID number locate in folder " D:Emp_Photoxxxx.jpg " Is there a way that i can auto data entry into field B a complete each path, file name for each record such as (D:emp_photoxxxx.jpg), so that when I click on field B it automatically load the photo. Many thanks...

I would like to have users click on either an icon or a button to display a pdf or Word doc file in FM7 (Windows). I have the pdf filename in a field. I made a button of a field containing a URL and it successfully starts up my browser and goes to the proper website. I can manually insert the pdf file into a container field in a record. An icon is created and it starts adobe reader and displays the file. But I don't want to manually insert each pdf file. I tried to import all of the pdf files from a folder using import/folder, but I get an error message - "No files of the specified type were found in the specified folder." Does anyone have an…

Hi all,1st of all thanks for all help...here is my problem: I have 2 fields A & B A is contained Employees ID number (xxxx) B is container field for photo. In my employee's database all employee photo store under EmployeeID number locate in folder " D:Emp_Photoxxxx.jpg " Is there a way that i can auto data entry into field B a complete each path, file name for each record such as (D:emp_photoxxxx.jpg), so that when I click on field B it automatically load the photo. Many thanks...

I kind of feel like this is something I should know, but is there a way to prevent a user modifying a field based on the value of another field? For instance, say I have two fields, "name" and "nameLock" and I only want to be able to modify "name" if "nameLock" is empty.

Hello all, I have been searching the archives for answers -- and have found information, but some is a bit old, and I wonder if there might be a newer solution. What I need to do is fairly simple, and common: Calculate the # of working days only (excluding weekends) between two dates. There are two scenarios: date1 -> date2, and date1 -> current date. Could anyone advise of a solution or point to an existing topic thread? many thanks in advance. julia

Hi all, I have been creating a Membership Database. I have members with existing numbers - starting at Number 1. Any new members, I wish to start off at 80000. However, what is happening is: I add a new member - it gives the correct new number of 80000 onwards, but when I amend details (such as an address) on an existing member the database then gives this amended record the new 80000 + number instead of keeping its original one. Could anyone please have a look at the attached file, and suggest where I'm going wrong. (I have uploaded the file to my server - the URL below is a direct link to download). http://www.box.net/public/stati…

I'm stumped on this one, thought I'd try the forums. I have a data base related to another data base. For the relationship I have defined, I get three related records for each record in my main data base. If I create a portal I can see all three matches for each record. I want to parse each of these values into 3 separate fields in the main data base. Using a calculated value for my fields I can pull in the first value using: GetRepetition ( relatedDB::Value ; 1 ) however, GetRepetition ( relatedDB::Value ; 2 ) doesn't work Last(relatedDB::Value) will pull in the last value I need to parse Value in my related DB to Value1, Value2, Value3 in my main …

Hi, I am in the process of modifying the stock Business Productivity Solution from Filemaker. This was made using USA date and system settings. Is there a simple way I can convert all of the databases in this solution to UK format? or do I have to change each date field manually? I have FM Pro 8.5 Advanced, if this makes any difference. Thanks in advance!

Hey All, I'm reposting this in what I think is now the proper forum. Thanks in advance for your help. I've modified the Business Productivity Kit. Everything has been going smoothly, but it has become apparent that I need some more foundational information. The User Guide gives very little information on Functions, Operator and what any of those pesky little symbols mean. I found the filemaker.com pdf file on functions, but putting it all together is another thing, eh? Anybody know where I could find a quick low cost source for info on the symbols and the rest? I guess that stuff is too basic for this site or maybe I'm just not searching in the right place. …

Is there any way to sort an address by the street name . . . in otherwords ignore the number in the address field?

We use many portals (columnar, horizontal, filtered, conditional, dynamic) – techniques picked up from Forums in the past. I have been asked to provide scanning option (in addition to these other methods). User wants to quick-scan portal rows by (various) alpha start characters, view quickly then scan again. They want to see the names right above and right below where they stop – no filtering; or filter then scan. I can do it now by running through each row of the portal under a Freeze Window. But I should be able to jump to row by calculation and I can’t get the logic of it. I can get the position of the letter requested (from a value list), example: A – Acme…

I have a file that I use to manage purchase orders. I want to have a field that calculates the total quantity of a certain product. ie on an order that has 3 different items I need a total quantity count on only the items that have the item# 13063. I'm sure this is easily done, but I am not very conversant with calculations unless they are very simple. Any help I could get would be greatly appreciated.

Hello, I'm trying without success to do the following... I have a portal with a Artist table as context. I have related another ArtLabourItems table in another file to the Artist table. Then I have a ArtLabourType dropdown field in the portal that defines an automatic value list from the ArtLabourType field in the ArtLabourItems table. This works fine. There is also a Price field in the portal that is defined as a calulation. When a ArtLabourType is selected from the dropdown I want the Price field to lookup its value via a calulation from the ArtLabourItems table. Basically I just want to select a value in the dropdown and then have other portal …

See attached file for an example of what the situation is. We have a job list for our company that keeps track of orders for customers and orders for testing equipment comming in. We have a field called "Total Sold" This is a sum of the Child database job items. Problem is, the sum is not stored, it is auto calculated on demand. The Thing is, I want to know how i can set up "Total Sold" To calculate only the quantity of the companies who are NOT "Inventory Test" Please help someone, our numbers are way out of wack. (We have items that we just got in, and we tested 300 of them , and only sold 5 so far, but our total sold is 305, it should only…

I have a situation where I have a calc: GetNthRecord (Filed 1; Get (RecordNumber) - 1) The problen is the Get (RecordNumber) is pulling the record number as the info was imported. If I create a seperate field that only has Get (Record Number) and I "Do not store calculated results" it puts the correct record number of the found set. Is there a way to have the Get (RecordNumber) inside the formulas recognize the current records in the found set? If not how do I can I pull that record number in and have it look up the previous record? Thanks for any help...

So (thanks to InkyPhil), I've got this calculation working on my FORENAME field: [color:black]Case(not IsEmpty(forename);forename;"(forename)") Great! But I'd also like FM to change the colour of the text if the value is ever "(forename)". Something like this might work: [color:black]Case( forename = "(forename)"; TextColor ( forename ; RGB ( 255 ; 0 ; 0 ))) Can anyone tell me how I get both of these calculations working on the same field? I think it's expecting a boolean operator between them, but the & operator doesn't seem to work. (The colour doesn't change).

I have a database that sums spending by brand. The database holds spending for an entire year and I want the user to be able to choose a date range to pull desired spending. I want to bring that sum into another database (I want to use a field to bring it in). I am having trouble getting that number into the desired database. I only want the sum of those records that are in the found set not the sum for the total records. When I use a calc field referencing the SUM field it brings in the total spend not the spend for the found set. Any thoughts on how to make this work? Thanks for any help...

I am putting together a clac that will be exported to an html file. Is there a function that allows me to enter text and ignore if it includes quotation marks rather than thinking that is the end/start of text?

I’m able to go (not even) half way and need some help with this one. I need to parse out text out of 1 text field and put the data in several fields. The basic text looks like this: ‘Variable length of text' - Definition 1 Meaning: Variable length of text Example: Variable length of text. Notes: - Variable length of text - Variable length of text 'Variable length of text' - Definition 2 Meaning: Variable length of text Example: Variable length of text. Notes: - Variable length of text - Variable length of text Where there will be always at least 1 definition, sometimes 2, sometimes 3..... Final goal is to have definitio…

When exporting a pdf file from a container field is there a way to name the file with the contents of a text field in the same record - then add the .pdf extension? Many thanks for any help.

Hi, Seriously Stuck! Basically I'm helping a friend out by producing a nice little database for his guitar shop. The Guitar Shop gives each item in the shop a separate serial number, at the moment they give these out manually but i wish to change that using filemaker. In short i was wondering if any one new how to allocate an "auto enter" serial number to different categories. e.g. For all Electric Guitars the serial has the letters GE in-front of it. This makes the serial read GE1234. now the problem I'm having is allowing that to raise by 1 and then also allowing another category e.g. AC to raise by one independently from GE but keeping them in the same f…

Good morning! Can someone help me with the following problem? I'd like to create a database in FM7 storing graphical information on a given scheme. Specifically, I would provide a graphical scheme of the coronary arteries in the database. The user should then drag and drop (or draw) one of several symbols onto that scheme denoting the degree of stenosis of these arteries. The symbols would look like circles filled 0%, 25%, 50%, 75% or 100%. Is it possible to create such a database in FM7? Thanks for your ideas! So long, luke

Hello, I have a portal to keep track of all recent information for the contact. I have this information displayed in the contact information, everytime I enter new information I would like it to be entered in the contact information page and it currently is not doing that. I would like to know if it is possible to have the field display the mose recent information entered in the portal and where I can go to find out more about this, I have 8.5 advanced. Thank you so much!

I am new to FM and I just want to add a hypertext link to a text field so that my users can view a web page from my browser. I can do it with buttons, but when I make a new record the button get carried over and I don't want the same links on the next record. I want the person entering in the data to put their own link to the web. I have been scratching my head for 2 days now. Seems like an easy thing to do, so I don't quite get it!

Here is my situation. I've got various files which contain a value which is the date and time in seconds since 1st Jan 1904. Some files contain this as a negative value, others as a positive value. I have an Excel calc that handles both: IF(A1<0,DATE(50,1,1)+((2843274496+A1)/86400),(A1/86400)) You simply replace the A1 references to the cell containing the value, and it'll work out if it's neg or pos and then perform the correct calculation on it. What I'm looking for is a similar thing for Filemaker Pro, as I want to import the files into filemaker rather than use Excel. From the limited amount I know, I *think* I would need to define three f…

Currently I have a calculated container field which shows a button icon if the status of a request is "open" and is set to nothing if the request status is in any other condition. Im not really happy with the way the button looks... is there a different way to go about this situation possibly without using a container?

Hello, i newbie when it come to calc. I have DB of Student which i track Attendance, how many they miss, how many hours they need to make up, hours made up already. I need to figure way to find out how many student need make up. let's say i have 24 students, out of those 24 how many need make up? i can tell how many hours they miss, and how many hours they need. i was thinking of using how many hours they need to figure out who need make up. here is example james smith, Total hours miss: 21 Total hours made up: 7 Total hours Need:14 base on the hours need, how can i set up a calculation that will count those student who need make up. i…

Hello! I know how to get Filemaker to insert a default value into a field. I'd like it to restore the default value if a user enters a different value and later deletes it: i.e., if the value of a field is ever null, then FM should immediately set it to the default value (maybe "unknown"). Can anyone help me out?

Storing a file reference in a container field is an easy way to get to the content of a file, FM just opens the app the referenced file was created with. Every thing works fine as long the referenced files reside in your own machine or the files are gathered from a remote machine in the LAN by the Creator of the reference, however trying to get a file referenced by another user is not possible. What I am doing wrong? How can I get the content of a file referenced in a container field by other users in the LAN? Sure you can help. Salujdos, Javier

I have 4 number fields. Let say field 1 I fill in with a value = 1 field 2,3 and 4 will show 0. If I fill in value 1 into field 2, field 1,3,4 will show 0. If I fill in 1 into field 3, the rest of the field will show 0. How do I do this? dblentry.zip

I’m having trouble writing a calculation and could use some help. I’ve got a value list with eight items in it; Design, Photoshop, Editorial, Writing, Web Design, Programming, Photo-shoot and Meeting. This way, when filling out the time card associated with each project, you can select the appropriate activity. I have a sum field for the sum of all activities (total time) but can’t figure out how to get the sums of each activity (Total Design, Total Photoshop, etc.) to display in its own sum field. Thanks

To use a date expression in an exported PDF filename I would like to convert a date field (i.e.; 10/16/2005) into a text expression (i.e.; 051016) so that the PDF file will sort properly in a directory list. Is there a way to do this with a text calculation? Any help on this is greatly appreciated. ~Dennis