Looking for script to copy records from one table to another

[Derek_C]

Looking for a script to copy number of records from one table to another table based on a find condition.

[Lee Smith]

Hi Derek, and Welcome to the Forum.

Copying Records will creates redundant records, and usually isn't the way you would want to go. In most cases, the need for this can be accomplished in other ways. In this case, you may just need to establish a relationship to the other File.

So before we get into the script, why not explain why you want to duplicate the Records. In other words, what is the end results that you want to accomplish.

Lee

Edited September 6, 2008 by Guest

[Derek_C]

Thanks for responding Lee.

I have a price list database (300 items). I have been deleting items that have been discontinued. Recently, I have had a number of items that we needed to bring back.

So, I started to export the discontinued items to excel before deleting. Great except that I have a container field for item pictures. Dont want to loose the pictures.

Thought setup copy structure of price list database and copy discontinued items to it when discontinued. Thus no loss of any data.

P.s. Have not been able to grasp idea of relationships yet. Still using multiple single table concept.

[Lee Smith]

Hi Derek,

Be sure you are using copies of your files, you don't want to lose data by making a mistake in your Originals.

I think I understand why you want to copy some of the records, but instead of copying, why not just export the data as a FileMaker File.

I don't see the need for a relationship yet, so lets things that you mentioned.

If I understand you correctly, you have a file that you are happy with, I'll call it File A, and a file where the data resides, File B, that contains data you want in File A?

You can do this using the Import Records Feature under the File Menu. To start with, isolate the records you want in File A, by doing a find in File B. I'm not sure if this is all records, or just a few, but once you have what you wont in File B as a Found Set, then go to File A, and Import the Records.

Some of the other things you mentioned, might be accomplished with some of the tools in Advance, such as Import Tables, Copy Tables, Copy Fields, etc.

Oh yeah, did I say you should use copies of your files. This is an important point, be sure you are doing this on copies.

Lee

[comment]

I'd suggest marking products as discontinued, and leaving them in place.

[Lee Smith]

I read this as he has already moved them, and is looking to move them back.

[sdl]

I'm going to resurrect an old thread because I'm in the same boat and my head is spinning ... coding for way too many hours.

Simple Scenario:

I need to plan over 200 events across the country in several months

I have a table of EVENTS which contains all possible events

However some events aren't feasible as they conflict.

That said, I don't want to delete them completely.

Yes, I could mark them as inactive and I have that but they still show up on my calendar, event list, etc.

Yes, I could struggle to find complex trigger scripts to filter out the inactives but I'm not that smart

So I was hoping I could just create a new table called EVENTS_ALL and whenever I create a record in EVENT, it creates the exact same record in EVENTS_ALL. And if I delete a record in EVENT I still have a record of it in EVENTS_ALL.

I could create a massive Cut/Copy script for each field but I know that's not the answer

I could create a button that I could press AFTER I create the EVENT to export the record over to EVENTS_ALL but what if I forget to. And I can't see a script trigger that works that way.

uggg.... head spinning. Help appreciated.

[comment]

So I was hoping I could just create a new table called EVENTS_ALL and whenever I create a record in EVENT, it creates the exact same record in EVENTS_ALL. And if I delete a record in EVENT I still have a record of it in EVENTS_ALL.

I believe you have just defined "redundancy".

No "complex trigger scripts to filter out the inactives" are required - all you need is to define your relationships to include only active events. For example, instead of EventID, you could use a calculation field =

Case ( not Inactive ; EventID )

as your matchfield. This will be much easier than trying to keep two tables (partially) synchronized.

[sdl]

Hi "Comment"

Thanks for the quick reply. I'm still confused and almost embarrassed to post on the forum for fear of sounding like a total doofus.

My list of Events is shown in two places. One is on a layout in just the body section. The other is in a portal.

The portal has a SelfJoin relationship where z_g_SelfJoin and z_g_SelfJoin are equal. So that way I get all events listed in the portal.

I'm not familiar enough with the not logical function. So I'm confused on how to set up the relationship.

In terms of the regular layout I guess I could use an Onlayout script trigger to trigger a find and sort to eliminate the inactives. But not sure on the self-join.

I am sorry I'm not being quick on the uptake.

[comment]

If you have a list layout of the Events table, then yes - you'd better use a script trigger there.

As for the self-join, I'd suggest you define two fields:

• Inactive - Number (use 1 for inactive records, 0 or empty for active);

• cTrue - Calculation, unstored (result is Number) = 1

A self-join relationhip:

Events::cTrue ≠ Events 2::Inactive

will show all and only active events.

Other variations on the theme are possible, e.g. using a global field to switch the related set between active and inactive (though this would require the Inactive field to toggle between 1 and 0).

almost embarrassed to post on the forum for fear of sounding like a total doofus.

The purpose of the forum is to learn. IMHO, the foolish thing is to prefer ignorance.

[sdl]

Thanks so much for taking time to grab me by the hand Comment. Your self-join worked perfectly and now I can see the whole redundancy path that I was starting to head down unnecessarily.

It did invoke a new question from me though ...

when is it appropriate to use a Number(with calculated value) versus using a Calculation (with number result)? I learned the hard way on a previous issue that one of my relationships couldn't use a Calc(number) but worked perfectly with Number(calc). Just curious.

Thanks again to you and all others who take time to help mentor

[comment]

when is it appropriate to use a Number(with calculated value) versus using a Calculation (with number result)?

It's hard to give a comprehensive rule to cover all possible cases. I'd say use a Calculation field for calculations, unless you have a good reason not to. Users overriding the calculated value, or vice versa (e.g. formatting user entry) are good reasons.

one of my relationships couldn't use a Calc(number) but worked perfectly with Number(calc).

It sounds like the calculation could not be stored. I am not sure of the exact circumstances, but using an auto-entered calculation is rarely a solution to such problem - since being stored it will not recalculate when related/global fields being referenced are modified.

[sdl]

I hope I'm not overstaying my welcome here but another question that is driving me crazy but likely something simple ...

Here are some photos to start:

Here's my layout.

The layout as you see,is showing EVENTS which is a parent table. It has two key parts. The top part is showing Event details straight from the table. The lower part is the portal that I discussed earlier.

Here is the relationship. cTrue is a calculation = 9 and if my Event_Priority = 9 then it's equivalent to inactive.

Here's the RG in case that helps:

So what is the issue:

Well all I'm trying to do is click on the portal list and have that record populate in the top half. But regardless of the various RelatedRecord scripts that I try to create I get an error of "target is not part of a related table".

I'm guessing I"m missing something obvious but after a couple hours of trying different things (ie changing the layout (get records from) and changing the script step to try different relationships I'm stumped.

Any advice is most appreciated.

Wow Comment I sure wasn't expecting an answer on a weekend. You rock sir (or Ma'am)

[comment]

I believe this should work (using terms from my example above;

Go to Related Record [From table:"Events 2"; Using layout; <Current layout>]

Make sure the "From table" is the same TO as the one being shown in the portal.

[sdl]

no luck Comment .. unless I misunderstood something. Here is the photo

I think I've got the portal and TO exactly the same and this is what I reference in the script ... you can see both in the photo.

Did I misunderstand?

[comment]

I don't know. Does this work for you?

GTRRNav.zip

[sdl]

I don't know. Does this work for you?

It is working. I've got to step away and I'll come back ... but the one thing I can't understand is how a number field (Status) with no calculation is coming back in the portal as "True" or "False". Normally if I put a number field in the portal it comes out a number field. I can't for the life of me figure out why it's coming out as a logical text result. What would I do if I wanted to show the numeric value of Status?

[comment]

Format > Number… > Format as Boolean

[sdl]

Format > Number… > Format as Boolean

Dooh! of course. I knew it was some type of Boolean but I forgot about looking at the formatting rather than the field definition.

I also figured out where my error was. It wasn't in the portal setup per se. It was in the setup of the button. I had a graphic in the portal and I was using that as a button. I didn't put the graphic in a container and therefore it wasn't really a part of the record. It was just a graphic.

Comment, thank you so much for your help on this! Very nice of you to take the time to make that down and dirty file. That made all the difference!

Cheers

sdl

Edited February 13, 2011 by sdl

[Vaughan]

I knew it was some type of Boolean...

Things are either boolean, or not... unless you don't care.

(This is an attempt at humour.)

[sdl]

Things are either boolean, or not... unless you don't care.

(This is an attempt at humour.)

lol ... TRUE <--- get it ... true :)

[bruceR]

There are 10 kinds of people; those who understand binary; and those who don't.