Calculation Engine (Define Fields)

I have a need to have a unique identifier created based on a calculation. A user needs to select a zone, which has a two-digit abbreviation. My calculation will take the zone abbv. and append a three-digit number and the first three letters of the name of the current month. The three-digit number (currently) comes from a serial number field, which increments by 1. E.G. User picks "Zone A", which has abbv. "ZA". This becomes ZA008SEP. However, at the start of each month, I need to reset my serial number counter to 001 so that next month I can have an id like ZA008OCT. What's the best way to accomplish this, preferably automatically? Cheers, Robert

Hi all, I'm designing a database to track the progress of projects and I need a field in which I can enter notes about what stage a project is in. I've been reading about portals but I think that's overkill for what I need but I could be wrong though. What I'd love is a filed that I can just enter lines of text in. I tried to make fields and just repeat them but the data is just duplicated when it's entered. Thanks for any advice on how to tackle this.

Do people keep a separate table for their global fields or store them in which ever table seems most relevant? Thanks, Linda

Hope someone can help me. I have got a number of fields with students pictures in them spanning kindergarten to grade 6. I am hoping to be able to display a current photo of the child regardless of what grade they are in. Teachers can navigate through a child's records from K to Yr 6. I want to be able to show only their current photo based on their current scholastic year. Hope that makes sense

There should be a color tint from a container calc behind the rows, not text. A picture is worth a 1000 words, and I'm betting the client needs to install Quicktime? Is that a must for Windows? Get results: Platform "-2" Version "5.2" I believe he just upgraded to XP and the latest service pack.

So I have a database that has lookup fields that populate when data is entered into an ID field. The problem is that this ID field is entered through CWP, and it seems CWP doesn't tell FileMaker that the field has actually changed, so the lookups never get populated. If you enter the ID numbers into the fields in FileMaker, rather than the web, the lookups work without a problem. To solve this, I tried to change the ID field to an auto enter field in which I used the Replace() function, basically substituting the original text for the original text, thinking this would "touch" the field. No dice. Any ideas? Thanks for your help!

Hello I'm using filemaker with ASP for our website - using it to list training events. One of the things for each training event is an automatic link to a booking form. We do this by having a New Record script that puts the following in a field: Download, fill in and return this form / Large Print Booking Form But because we need it to be a piece of text, we have to put quote marks around it in the calculation field so it becomes "" Unfortunately the quote marks around the href link confuses the calculation and it doesn't work. Can anyone think of a solution? Thanks

Once again, I genuflect myself to the FM gods and I'll gladly place a copy of MS-Access on the database alter as a sacrifice to you. The attached db is a work in progress, so its navigation between screens is a little rocky. I'm focusing on the LT (long-term) report section for now and will model the other report functions after it. If you go to the Long-Term_Costs_Report_Switchboard layout, you can see that a user can click on a button to select the desired category report. The first one, Asset Capital Loss (by Date) works fine. The problem is the scripting to create a report: there are two report types: one for xDate (or by Date), and the other xReport (by Rep…

Can global variables be used to provide an auto-entered text string during a record create? I've tried every combination I can think of to specify the Auto-Enter of a predetermined value ($$CurrentText). Simply placing $$CurrentText in the Data option indeed causes "$$CurrentText" to be placed in the field, but not the contents of the variable. Placing it in a calculation doesn't work either, nor with any combination of "replace existing". If I make the calculation read: $$CurrentText & "TEST" the word TEST shows up, so the evaluation is taking place. BTW, Data Viewer displays the proper text string in the variable before and after the record create. Is …

I'm working on a project that includes having TIFF images come in via XML. The image data is encoded in Base64, which I can decede easily enough with the Troi URL plug-in. However, I end up with the raw image data rather than an actual picture. How can I go about changing all of the garbage-looking text into a nice picture for use in a container field?

Hello, I have a Membership database with a field that i would like to automate. I have fields: Factory Start, Contribution, and Amount. I wish to have the Amount field populated by automatically working out the total Contributions made since the Start Date. eg: Member started on 01/01/80 - paying £1.50 per week, with today being the 05/09/06 = £xx paid in Contributions. The contribution amounts are: Adult £1.50 per WEEK Juvenile £1.11 per WEEK Retired £2.50 per MONTH Student £10.00 per YEAR Unemployed £2.50 per MONTH If anyone could please help me do this, i'd be very grateful. I have attached the database. (Use Members…

Hi, I am trying to round up time to the closest 15 mins. Fields: Service Start Time Service Stop Time Billable Time = (Service End Time - Service Start Time)/60 I want Billable time to round up to the closest 15 mins. In other words I want billable time of 61 mins to equal 75 mins. I want 5 mins to equal 15 mins, etc... Thanks! dmg

I am using John Osborne's technique for not displaying an icon in the last portal row if there is no record there (using a global container field and an auto-calculation container field). This works fine. However, I am using it in a database which already has a number of records. The container fields work fine for newly entered records. But the previously entered records do not display the icon on their portal rows. Is there any way the container can be updated for previously entered records on the portal rows? Many thanks, Margita

Hello, I have a formula that checks to see if a related field is empty, and if it is, returns a text error message. If the field is not empty, it performs a calculation and returns a number result. I would like the result to be no more than 2 decimal places long. That is easy to set if the field is designated as a number field, but then I do not get the error message. If the field is designated as a text field, everything works, but the number has too many digits on the right of the decimal point. Does anyone know of a workaround for this? Here is my formula, if that helps. If(IsEmpty(InvBarcode) ; "" ; If(IsEmpty(LiquorCatalog::FullWeight) or …

Hi all, I am making a contact database. In it, each person can be a member of certain groups. For instance, Joe Blow is a member of Activities and Newsletter. There are twelve such groups and I have made a checkbox field that contains all of them. When I run a report on group membership, Joe Blow will show up in Activites but not Newsletter. How can I run a group membership report where people in multiple groups will show up in those groups on the report? The report I want to see would look something like... Activites Joe Blow Newsletter Joe Blow I hope all this makes sense, I am a bit of a newbie. Thanks, Matt

What I would like to accomplish is summarize how many contacts are related to a specific CompanyID when I am looking at the Contact record. Ideally, I'd like to have a field sitting next to my CompanyID field that simply states XX number. I guess a similars summary but without the portal. Can it be done and if so how? I tried all the variations when setting up a summary field without getting the desired result. Thanks for the help. Al

this seems like it should be simple but has had me stumped. i'm trying to get the numeric difference, in days or months, of the difference between two dates. one of these date fields will always be later than the other. when i set it up as "datelater - dateearlier" and output to number, then i get fairly accurate results if the difference is a year or two. but i'm doing a longterm medical study and the differences are often 15 to 25 years and filemaker is returning numbers that are usually way off (though, even in this range, they are sometimes almost accurate). what's going on and how do i fix this? andrew

I'm trying to create a database for some sound effects and I'm running into problems with the network aspect. I've got some sound effects on a network share and I've got my container fiedls with a reference to those files and everything works on my machine but if I open the database on another machine on the network it doesn't play the files...the link is broken. How can I view the link or change the link of a container field? is it even possible to do this? I can't store the files in the database for a couple reasons but one of which is it would be huge. thanks for the help!

I have a question that is beyond my FM knowledge. I have a database whose function is esoteric though similar in structure to more normal ones. It consists of samples of charcoal recovered from prehistoric arhcaeological sites. Each sample has numerous types of charcoal (wood, nut shell, nut meat, etc.). There is a table for the samples with information about them, a data table with counts and weights of the charcoal types (related to samples with a sample ID code), and a table of types related to the data with a type code. It is easy to get a total of the charcoal for each sample in a live display in a layout using a calculation field that sums the amount or weight…

I'm having the same trouble now that I did 30 years ago: trying to get a date. :S If you'll open the attached file, go to the Scheduled Checks layout, then record 5, you'll see the problem in the Year-Months-Days columns. (They all use the date in the yellowed Due Date column for their calculations.) Days that are past due are in red (which is what I want). However... Here's the problem: for the 11.16.06 due date, it's really only 2 months, 17 days away. However, as coded into the calculation, FM is saying, "No you don't have 2 months + 17 days left, but 3 months -14 days left." *heavy sigh* You can't argue with logic like that...but I'd rather have the 2 +…

Hello, I need help with a calculation, I need the calculation to display the total of records in table B that match the project ID of the related table A. I tried to use this in table B and it didn’t work: Get(TotalRecordCount) = ProjectID I appreciate any help.

I have a calculation that tracks changes made to certain fields. I want to have a delay built into the calc that allows the person creating the record 5 minutes to complete the first version of the record. The calc see's any edit to these fields as changes, even the very first entry. So I would like to have an If statement that says: If ("Time In" + 5 minutes > Current Time; execute calc; otherwise leave the revision list blank). Any ideas?

Hello all, I have a DB which stores some order line for a sale order. Every record has a code that is the key for a relationship with my supplier database. All the suppliers has a number that represents the shipment days (i place an order to that supplier and - i.e. - he ship the goods within 3 days). Here comes the troubles. I must calculate the arrival date excluding all the saturdays and sundays because they aren't working days! I hope I explained the problem well. Any idea or suggestion to help me?

Hi, is there a way to store individual emails in container fields?. I have seen commercial Document Management software that can do it. The solution I am working on, can handle any stored file (thanks to FM of course, not my talent!), except emails. Saludos, Javier

I have a field in my DB that contains the shipdate for a shipment. Is there a way to set up a popup reminder, let's say two workdays before the actual shipdate? If the actual shipdate is on a thursday, the popup will show itself on a tuesday. If the shipdate is on a tuesday, the popup will show on friday. If the shipdate is on monday, the popup will show on thursday. Thanks for all help! Chris

I have a large database of thousands of books and have now been given hundreds of image files (that I've been able to relate to my book database by creating a separate "image path" table which stores my book ID number and the file path where the related image is stored. The images, however, have been named by the supplier so they do not instantly relate to my database) and I want to somehow, by using the book ID relationship, store the path references in my new container field in the book database so that I can view the image. As I'm relatively new to FP8 and container fields I am hoping someone may be able to help. I hope so!

I'm not sure where to post this question, so I thought I would try here. I just started to get my head around the new web stuff in FM8.5. I think it is pretty cool how you can have a map show up based on an address in a field. I would like to take this a click further. Google Map has this feature where you can ask for directions. A subset of that feature tells you how many miles you will have to travel to get where you want to go. [color:red]Is there a way to extract JUST the number of miles you will need to travel? I would like to post just this data, without the corresponding map picture. If anybody can aim me towards a plug in or custom funct…

Hello. I would like how to filter a portal list on each keystroke. Let's say I'm searching for a name in a portal list. I Type A; then Anna, Adam, Adolf comes up. When I type the next letter D (ad), then only Adam and Adolf comes up. How can I make this work?? Thank you for the effort! Chris

I have been using FMP8 for just a couple of weeks now and love it already. Never got on with Access (far too complex) and previously I was a committed Q&A User (Great Symantec product, long since discontinued, and only a flat form database not relational). Mine is a typical newbie question but I have searched F1 help and online here without success, in part, because I don't know what the thing I want is called. Here is the problem. I have a database with two tables, Patients (which is a list of my patients and their details) and PatRecs (which is a list of their sessions with me and data pertinent to each session). When I add a new session record in PatRecs,…

how can i set the validation of the calculation field? i need to have in a calculation field just unique values

I'm new here and very happy to be developing on Filemaker again.. so.... I have video editing software I'm using to export logged clip information. As part of that info, I can export a field with the file path info that describes where a video quicktime is stored on my array. I can import that into Filemaker no problem. I want to set up a container field on my layout that automatically displays the quicktime based on that file path info. Can this be done? The filepath is only the absolute path, not the one relative to filemaker. Since I have over 1000 records per project, this has to happen whenever someone selects a file. Thanks in advance for any help you migh…

Hello. Today was my first experience using FM 8.5 Advanced...or any version of FM. I am trying to set-up a project management database. To do this, I had hoped to modify one of the starter solutions, but this has proven to be more difficult than I imagined. My first challenge is to create a project number field that updates when jobs are created. I have discovered the serial function, but have a problem w/it. If I create two records (e.g. 0001 & 0002) than delete the second record (0002), the next record I create will be numbered 0003. I would like to make a numbering system that is smart enough to base the new number off of the last existing number. For e…

Hi, it's been a while! Miss me? Nahhhhh, I didn't think so. Here's the problem: I have a field (CurrentDate, which is formulated as[Get(CurrentDate)]) that reflects today's date (08.24.2006). There's another field, DueDate (a Date field), that has a due date of 08.30.2006. With a calculation of DueDate-CurrentDate, a black "6" will appear in the DaysToGo field. Conversely, if I put 08.10.2006 in the DueDate Field, the result is "-14" since I passed the due date by 14 days. Cool. Now, what I want to happen is that if there are "x" days left before the due date, the DaysToGo field will reflect the number of those days in black; if time slips away so the DueDate h…

I have a PO# in this format 560800000. "5" stays the same for all PO, "6" is the year, "08" is the month, the five 0s are auto-enter serial number. I defined the PO# as "5 & Mod (Year(Get(CurrentDate)); 2000) & Month Function & PO Suffix". The month func. is if month < 10, add 0 in front, else use the two digit month. PO suffix is just a serial# (00000), auto increment by 1. I'm working on duplicate record script, so that all the info from the PO will be copied, but the PO#. I want to auto reset the PO# to the next value. I use duplicate record/request script step, but the PO# won't be updated. Tried set next serial value, but I can't really do that be…

I'd like my shared solution to remember the value of a particular global, even after the files are closed and re-opened. The value is a moving target--it constantly gets updated. Is it possible in FM7 or 8? Or, any suggested workarounds? Thanks much.