John Caballero

You'll need to have a unique record ID for every record in the related file, a global in the main file, and a relationship from the main file that's based on the global and the related file's record ID. Your button will set the global with the record ID of the related record which corresponds to the portal row that you clicked, then you'll have a Go To Related Record (show) script step that isolates the record that is based on the global-to-rec ID relationship, then you'll call the import script the 3rd file that imports the isolated record from the related record.

Yes...I was presuming that your field name might actually be called FileName.

Hi MD, If you'd set your Quote ID into a global, you could've used a constant-to-constant relationship to grab it from the Quote file and use the Set Field script step to set it into the ID field in the related file as your looped through the records. That's probably how I would've done it if I'd set up the files, but it sounded like you already had a certain architecture in place that you wanted to go with.

I Per-Otto, If your calclulation is indexed... Create a global text field. Create a self-join relationship that has the global text field on the left and the calculation on the right. Create a number calculation that looks like: Count(self-join relationship::filename) When you create a new record, insert the candidate filename into the global - if the filename already exists, your Count calculation will show a positive value and you can act accordingly.

Hi MD, Glad it worked out for you. Ugo - I hear what you're saying and almost suggested doing what you suggested. This just seemed to fit better what MD was asking to do.

Hi Marco, It sounds like perhaps when the user clicks Cancel, they're taken to a layout that is a duplicate of the primary layout, and on this layout the button they click doesn't have the correct script linked to it. Check the layout name before your user clicks the button for the 1st time and then check the layout name after the user clicks the Cancel button.

How about going to the related parts_list_items to isolate the set you want to duplicate and then importing those records into quotes_line_items and then setting the newly-imported records with the ID from your Quotes file to link it up?

I'd stick with what you have.

Yeah, but having multiple layouts that mirror each other creates a lot of extra work in the long run - if you make a change to one layout you have to make sure you reflect that change in its mirror. Eddy's suggestion requires only one layout and is a pretty common and accepted practice for maing things either show or hide on a layout based on some change in a value.

Create a calculation in the related file that looks like this: Case(checkbox=whatever value you use,0,field you want to sum) Then from your main file, create a calculation: Sum(relationship::calculation)

OK, in both files create a calculated number field (call it cConstant) that returns a value of 1 (when you go to Define Fields and select calculation, enter the number 1 in the forumla window and make sure the field is set to be a number). From your 2nd file (the Child file) define a relationship to the 1st file (the Parent file) that is based on the cConstant field in both files. This is called a constant-to-constant relationship. In both files create a auto-enter serial number field(this gives each record its own ID), called nID In the Child file create a number field, nParentID. Create a realtionship from Child to Parent that links nParentID (in the Child file) to nID (in the Parent file). OK, so instead of using the Name and Address fields in the Child file, you're going to create cacluated fields that get their value from the Parent file. In the Child file, create a calculated text field, called cName. When you do, you'll see at the top of the dialogue box a drop-down list which allows you to pick the data source for your fields - select the relationship that you just created to the Parent file and locate and select the Name field. Create a calculated text field for Address and do the same (obviously selecting the Address field from the Parent file). This means that any change you make to the Name or Adress fields in the Parent file will automatically be reflected in the Child file - no scripting required. To setup new records: In the Parent file create a global text field, called gID. Create the following script: New Record/Request Set Field (gID, nID) Perform External Script (you're going to access a script in the Child file which you will create shortly) Exit Record/Request Halt In the Child file create the following script (this is the script that you'll call from the script in the Parent file): New Record/Request Set Field (nParentID, constant-to-constant relationship::gID) Exit Record/Request

Hi there, It it's important that your values be seperated by a comma, create a calculated text field in each of your files that looks like this: Substitute(jobnumberfield, ",","

More specifics would be helpful. Which record(s) in the 2nd file needs to be updated? How do those records relate to one another?

Thanks for providing that DISTINCTion, Marz!

You can check for the mode (find, browse, layout, preview) in your script. Basically you want the script to execute when not in Find mode, otherwise allow entry to the field. I don't remember which mode is Find (but no doubt, someone else does and will speak up soon enough), but put this in your script: If Status(CurrentMode)<>whatever Find mode is Your script steps Else Go To Field (which ever one you clicked on) Now it may be the case that you have one button for the entire row, if you do, you'll need to change that to several buttons (depending on how many fields you want to allow your user to search).

Hi there, There isn't an equivalent for Distinct in FileMaker. There are some ways that that can be simulated, but they are somewhat involved. Maybe some of the big potatoes here can come up with a work-around. For those of you who don't know, Distinct in a SQL query returns one each of duplicate values. You can easily find duplicates in FM, but it will return all the duplicates whereas Distinct returns one of each duplicate.

I've encountered the same problem - (in case Lee asks why I was using table view, it's because I wanted to ). I finally gave up.

Thanks Fenton. Creating the partial key isn't something I'd considered. I've encountered the problem of company and people records being in the same file along with the problem of store fronts, but because there were only about 1000 records in that db, I had my client manually go through and eliminate duplicates (better to pay a staff member to spend the time on that project than a consultant), but this db contains about 60,000 records, so asking for that isn't reasonable. This will be hourly work, and because of that my client is looking for as quick a fix as possible.

Hi Mike, OK, so what you want is a relationship from your main table to your "towns" table based on each table's "town" field. The other relevant fields in your main table get their data from a lookup based on that relationship. You set that up in the Options part of Define Fields for each field you want to auto-enter into. When a user enters a town name (and spelling will obviously be critical here) and then exists the town field, the relationship is "activated" and the lookup occurs. You might consider making a drop-down menu for your users to select from instead of having the user type in the town name.

Hi Mike, Many states have towns with the same name. How do you forsee FileMaker knowing which county, state, and country to pick? A better solution would be to enter the zip code, which no two towns share, and have that be the lookup key. You'll need a zip code database that contains all the cities, states, towns, counties that correspond to each zip. Have a look here: http://www.zipwise.com/

Hi Tarheelfan, Rather than think of data as actually being a part of the portal, it's more helpful to realize that a portal is really just a window onto another file that let's you see that file's data. The question then simply becomes, "can I export data from FileMaker?" and the answer is "absolutely". They key is to export only the data that the portal displays, but you can isolate that data with the Go To Related Records (show) script step. Create that script step and when specified, select the relationship that the portal is based on. When you execute the script, it will take you to the file that contains the records that the portal is displaying, and from there you can export whatever data you need.

Hey folks, I'm looking at working with a new client. They've got a contacts db that contains company and people info. They've had a problem with staff inadvertently hitting CMD D instead of CMD F and creating duplicate records and then not deleting the duplicate, so over time, the duplicate record starts to look different from the original because sometimes the original may get updated and sometimes the duplicate may get updated. Add to that nightmare the fact that some companies have multiple locations/store fronts, but of course there is no ID that associates multiple store fronts to a single owner. Store fronts can look very much like a duplicated record that has been modified from the original, but is in fact, not a duplicate. My issues are: what's a good direction to start thinking with respect to finding duplicate records and determining which one should be kept, and how to merge the data from the duplicate(s) into each original record while at the same time not accidentially merging a store/front into the "master company" record? Right now I'm not looking for specifics, just more of some ideas to get the juices flowing.

It sounds like you don't have the master password. A developer can deny access to various database features based on passwords. I'm guessing your password doesn't allow full access.

Hi Don, Here's a slightly more involved solution, but given that you have "many types of mediums", it might be better in the long run: Create an additional table (Layouts.fp5)that has two fields: Item_Medium & Layout_Name. My assumption is that for each type of medium, you want to have a seperate layout and that you've already created those layouts in your main table. For each type of media you deal with, you'll have a corresponding record in Layouts.fp5 along with the layout name that each medium should be associated with. Create a relationship from your main table to Layouts.fp5 that is linked by the Item_Medium fields in both tables. When you click on a button, the script looks through the relationship (and again, the relationship is based on the value in the Item_Medium field) to determine which layout to go to. Your script looks something like this: Go To Layout (relationship::Layout_Name) This gets away from having lots of If/Then/Else script steps and allows for things to be a bit more dynamic. If for some reason you need to change up layouts, you won't need to go digging through a bunch of If statements - you'll just change the assignment in your Layouts.fp5 table.

Hi Charly, Have you created a portal on your layout yet? If not, create a portal (which is based on your relationship to your notes file). Because you've got your relationship set up to auto-create related records, your portal will always display a blank row at the bottom. When you click in a field in the portal on that blank row and start entering data, a new record will be created in the notes file that is associated with the people record you're currently viewing.