John Caballero

Dan, and other Big Spuds - What if you put a button that linked to an Exit Record/Request script on the layout? Yes, I'm sure you would soon have extremely irate users , but wouldn't that free up the record? I've never tried it, but if my current client doesn't pay up...

Hmmm...I saw a post recently (by ESpringer, I think) which said you can't use the create related records feature if the right hand side of the relationship is a calculation.

Hmmmm... What's really needed here is a User preference file of some sort. In my previous post, the value that is set in the single-record file won't be lost the way a global is in a multi-user setting, but it still won't act as a global value the way it does in a single-user setting because whatever value is set most recently would be the one the next user saw. Each user would need a record in the file.

Yes. The problem is that globals in a multi-user environment aren't truly global. They're global to each user's machine and only for that user's session. As you've observed, only the host machine's global values seem to work. Create another, single record file, and instead of a global field, use a regular field. Create a constant-to-constant relationship to this file from all the files that need the value.

Hi there, You might want have a look in the Sample Files forum here.

Did you specify the amount the serial number is supposed to increment by? Also, keep in mind serial numbers are assigned when the record is created. If you already have records in your db, and this is a new field (or newly-modified field), there won't be any values in the field. If there are already records in your db, you can use Replace to populate the field with serial numbers.

Dan's suggestion is right on the money. A movie will typically have many actors, and an actor will typically have many movies. You need the 3rd table to show combinations of actors and movies. Rather than use the actor's name as the join field from Actors to ActorJobs, create an ActorID field in your Actors db and use that ID as the join field. So in the ActorJobs db, you'd have an ActorID field and a JobNbr field. You'd create a relationship in Actors that links to ActorJob based on the ActorID field and a relationship in Jobs that links to ActorJob based on the JobNbr field. Let's say that in your Actors db, Harrison Ford's ActorID is 100. Let's say in your Jobs db, Star Wars has JobNbr 999, The Hunt For Red October is 1051, Witness is 7234 and Indiana Jones & The Last Crusade is 44. Your ActorJobs db will contain lots of records, but somewhere in there you're going to have a series of records that look like this: ActorID_____JobNbr 100_________999 100_________1051 100_________7234 100_________44 (Ignore the underscore character...that's just for spacing.) Conversely, you'll have a series of records where the ActorID is different, but the JobNbr is the same. For example, JobNbr 999 (Star Wars) will have the ActorID for Harrison Ford, along with ActorIDs for Mark Hammil, Carrie Fisher, etc...so using the above example, the ActorID would change with each record while the JobNbr would be the same. In your Actor db you'll have a portal that will show all the movies the currently viewed actor has been in. In your Jobs db, you'll have a portal that shows all the actors who have been in that movie. Both databases will get that information from the ActorJobs db. What you need to keep in mind is that whenever you create a new entry in your ActorJob db, you'll need to specify content for both ID fields (ActorID and & JobNbr). As Dan said, there are a few ways that can be done.

Hi Chris, Just to add to ESpringer's exellent portal tutorial, you can have fields auto-enter information based on the value you select in a drop-down menu, even in a portal.

OK. So in some other file (let's call it Hours.fp5) you have records that show hours worked per day (in an "nHours" field) and the date ("dDate") they were worked. You'll want to create the follwing text calculation (call it cMonthYear): Month(dDate) & " " & Year(dDate). In the MonthSum.fp5 file you'll have an auto-enter creation date field ("dMonth1st"). You'll also have a cMonthYear text calculation: Case( Month(dMonth1st)=1, 12 & " " & Year(dMonth1st)-1, Month(dMonth1st)-1 & " " & Year(dMonth1st)) You'll create a relationship from MonthSum.fp5 to Hours.fp5 that is linked by the cMonthYear calculations (Hours_cMonthYear_to_cMonthYear). To show the sum the hours worked in the previous month, create a calculated number field ("cHoursSum"): Sum(Hours_cMonthYear_to_cMonthYear::nHours) At the beginning of the month a script will be executed (how depends on you) that creates a new record in MonthSum.fp5. Keep in mind that if someone goes in and changes the hours worked for a given month, the sum will change as well. If you need to capture that sum before any changes happen, you could create a standard number field and use the Set Field scritp step to set the value of cHoursSum as soon as the record is created.

OK...probably better to go with a seperate table. My experience with CDML is extremely limited - I'm not sure if you can show a summary type of report via CDML. OK, next question - will you be summarizing total hours worked per month per user? I'm still shooting pretty blind here because I don't know what the Access db (even just this piece) does. I'm not expecting a super-detailed descritpion. An overview would be fine.

Sounds like you calculation is formatted as a number. Is that the case?

What does the Access db do? Another possibilty that might work for you is just to have a summary report that you'd run at the start of each month.

Ah. You could create a separate table (MonthSum.fp5) with a number field. You would then create a script that creates a new record in MonthSum.fp5 at the beginning of the month and sets the value of the field in the new record with the total hours worked for the previous month. Or you could use a calculated result based on a relationship. There are several ways to go about this, but since I don't know your database structure, it's a bit hard to recommend a way to go. Lee - any ideas? Also, if you were in an Access environment and you were adding a new column to your table each month, wouldn't your table eventually have a lot of columns? Designing queries would become a real nightmare eventally!

Just any old field? There isn't a way (to my knowledge) to automatically create fields in FileMaker. Now data & records, on the other hand, is an entirely different story. What would this new field be used for?

Actually I learned that from Queue recently. I'd come up with an overly-complicated Case statement, but he showed me the way. Just like Peter Frampton.

There is, in fact. If you're not in a portal, by default FileMaker can only see the 1st record in a related file.

Yes there is (thanks Queue)! Assuming your mileage field is called Mileage. Create a calculated text field, call it cMileage. Right("000000" & Mileage,6)

OK. How is the information being transferred? Via a relationship?

It's a bit hard to troubleshoot since I can't see your script. Were you careful to make sure your Go To Record/Request [first] is outside (before) your loop? If it's inside the loop, you'll never get past the first record.

I'm not sure why you need to duplicate your information in Order.fp5 into Man5.fp5, but here goes. Create a LineItems.fp5 database. The data in this table will be shared between your Order.fp5 & Man5.fp5 tables. In each of your tables, in addition to whatever fields you need, create these fields: cConstant, nID, gID. cConstant is a calculated field that returns the number 1 (in other words when in the specify calculation box, simply enter the number 1). nID is an auto-enter serial number. gID is a global text field. In Order.fp5 create an nMan5ID number field. Create a constant-to-constant relationship from Order to Man5. Create a constant-to-constant relationship from LineItems to Order and from LineItems to Man5. (A constant-to-constant relationship is a relationship that is linked by the cConstant fields.) Create a relationship from Order to LineItems that has nID (in Order.fp5) on the left side of the relationship and nOrderID (in LineItems.fp5) on the right side. I specify both the name of the related file and the matching keys in my relationship name, so I would name the relationship: LineItems_nID_to_nOrderID. This way if the relationship ever breaks, I can quickly redefine it because I know how it links. Create a relationship from Man5 to LineItems that is linked by nID in Man5.fp5 to nMan5ID in LineItems.fp5 (LineItems_nID_to_nMan5ID). Create a relationship from Order to Man5 that has nMan5ID (in Order.fp5) on the left and nID (in Man5ID) on the right. (Man5_nMan5ID_to_nID). OK, so in Order.fp5 you'll have a layout that has on it a portal to LineItems.fp5 which is based on the LineItems_nID_to_nOrderID relationship. You'll have a button called New Line Item (or something like that). As an overview, here's what will happen when you click the button: You'll set the nID field value of the current record in Order.fp5 into the gID field in Order.fp5. If there isn't a corresponding record in Man5.fp5 (you'll need to check for that), you'll create a new record in Man5.fp5 and set the nID field value of the newly-created record into the gID field in Man5.fp5 you'll also set the nMan5ID field value in Order.fp5 with the gID field value in Man5.fp5 via the constant-to-constant relationship that exists between Order.fp5 and Man5.fp5. If there is already a corresponding record, you won't create a new record, but you will set the nID field value of the corresponding record in Man5.fp5 into the gID field in Man5.fp5. You'll create a new record in LineItems and set the nOrderID field value of the newly-created record in LineItems.fp5 with the value in field gID in Order.fp5 and set the nMan5ID field value of the newly-created record in LineItems.fp5 with the value in field gID in Man5.fp5. Both those values (nOrderID and nMan5ID) are set via their respective constant-to-constant relationships. You then enter the relevant information via the portal in Order.fp5. Is that enough to get the juices flowing? Again, why do you need a seperate file for Manufacturing Orders?

That's a really good idea, Lee. That would make debugging much easier than trying to decipher 4 quotes marks.

Use the Go To Related Record [show] script step and print from the related file.

How is the data that you entered in File A getting transferred to the field in File B?

You could use PatternCount(yourField, "

No to my knowledge, but you can script an exit script step to occur based on conditions you specify.