Filtering data from one table to another - how?

[Mark Welch]

I am importing a set of data into one table, and then running a script to "transmogrify" the data to be stored in a different table. My problem is that it seems that the ONLY way to actually do this is to create a separate temporary variable for every field in the source record, because once I change the Layout to store data into the new record, the source table is no longer in reference. Even though I've 'graphed' a relationship between the two tables using a unique index field, when I try to reference the "source table" while the layout for the "new table" is in scope, I get error messages.

Am I missing something simple here? I'm actually starting fresh after a botched attempt in which I did manually create a unique temporary variable for every single field in the source database, but this absolutely felt like a kludge and clearly led to many typographical errors since I had to manually re-type long field names many times.

What I'm doing:

- Go To Layout (SourceTable)

- LOOP - for each record in source table

+ get the index_id tag

+ Go to Layout (DestinationTable)

+ New Record

+ set the index_id tag for this new record

But I think that it is at this point, or the next attempt to access or store any data, that I am triggering an error message ("This operation could not be completed because the target is not part of a related table.") I don't actually know what script step is triggering the error message. Nor do I know whether "target" means that the problem is in the destination table, or perhaps "target" might mean an attempted reference to the source table.

I'm concerned because I'm just working on the first of several processing steps, and I dread the idea that I'll need to do this same kludge for every single script.

Of course, I'm getting very bizarre error messages which have no context. I guess I need to pay $300 more to upgrade to the "advanced" version which they claim will actually show me where an error is coming from in a script.

(Alas, it would have been nice if FileMaker provided a TRIAL VERSION of its software; but in fact, the trial did not work, and I was told that it was a "known problem" which affected many customers, and there was no workaround that would allow me actually try out the software, and therefore my only option to try out the software was to purchase it for full price, choosing without any context whether to pay $300 for the standard version or $500 for the advanced version -- and now, of course, I'll be spending a total of $600 to get access to the $500 advanced version -- which might or might not meet my needs.)

One other thing: maybe somebody could suggest some other tool that has similar features to FileMaker, but which allows such basic functionality as cutting and pasting scripts? Now that I'm trying to re-start from scratch, I'm finding it extremely annoying that I must use a complex set of mouse-and-keyboard sequences to re-create each line of script code.

Edited December 22, 2008 by Guest

[comment]

It's hard to say what's wrong without seeing the exact script (and the relationships graph). For example, you say:

+ New Record

+ set the index_id tag for this new record

but you don't show us the actual step. It's quite possible you are trying to set a field in the wrong table occurrence by mistake. Or it could be something else further down the script - of which we know nothing about.

A simple way to debug a script without having the Advanced version is to use Pause Script and Show Custom Dialog at critical points.

BTW, I wonder why don't you simply import the records from Source into Destination (assuming you do need the interim Source table).

[bruceR]

I am importing a set of data into one table, and then running a script to "transmogrify" the data to be stored in a different table. My problem is that it seems that the ONLY way to actually do this is to create a separate temporary variable for every field in the source record, because once I change the Layout to store data into the new record, the source table is no longer in reference. Even though I've 'graphed' a relationship between the two tables using a unique index field, when I try to reference the "source table" while the layout for the "new table" is in scope, I get error messages.

Am I missing something simple here? I'm actually starting fresh after a botched attempt in which I did manually create a unique temporary variable for every single field in the source database, but this absolutely felt like a kludge and clearly led to many typographical errors since I had to manually re-type long field names many times.

What I'm doing:

- Go To Layout (SourceTable)

- LOOP - for each record in source table

+ get the index_id tag

+ Go to Layout (DestinationTable)

+ New Record

+ set the index_id tag for this new record

But I think that it is at this point, or the next attempt to access or store any data, that I am triggering an error message ("This operation could not be completed because the target is not part of a related table.") I don't actually know what script step is triggering the error message. Nor do I know whether "target" means that the problem is in the destination table, or perhaps "target" might mean an attempted reference to the source table.

I'm concerned because I'm just working on the first of several processing steps, and I dread the idea that I'll need to do this same kludge for every single script.

Of course, I'm getting very bizarre error messages which have no context. I guess I need to pay $300 more to upgrade to the "advanced" version which they claim will actually show me where an error is coming from in a script.

(Alas, it would have been nice if FileMaker provided a TRIAL VERSION of its software; but in fact, the trial did not work, and I was told that it was a "known problem" which affected many customers, and there was no workaround that would allow me actually try out the software, and therefore my only option to try out the software was to purchase it for full price, choosing without any context whether to pay $300 for the standard version or $500 for the advanced version -- and now, of course, I'll be spending a total of $600 to get access to the $500 advanced version -- which might or might not meet my needs.)

One other thing: maybe somebody could suggest some other tool that has similar features to FileMaker, but which allows such basic functionality as cutting and pasting scripts? Now that I'm trying to re-start from scratch, I'm finding it extremely annoying that I must use a complex set of mouse-and-keyboard sequences to re-create each line of script code.

Yes, you are missing something here.

You're making things out to be much more difficult than they are.

That's not surprising when you're starting out; but it also helps to recognize that you have something to learn instead of complaining that the tool doesn't work right.

1. You do NOT need FileMaker Advanced to copy and paste scripts or script steps.

2. The normal way to do what you are doing is to first of all, be sure that all records have a unique identifier field. Then in your "new" table add a global field gSelectedRecID and add a relationship that links to the RecordID field of your original data table. Then your script would go something like this:

Set field[ Newtable::gSelectedRecID; RecordID]

Commit Record

Go to Layout [ myNewLayout ]

Set field [ Contacts::FirstName; Selected_Record::FirstName]

Set field [ Contacts::LastName; Selected_Record::LastName]

Set field [ Contacts::City; Selected_Record::City ]

... etc

Commit Record

If the RecordID field in the new table can be used for the link to the old table then you won't even need the global or extra relation.

It might also be helpful to explain what you're really doing. What are these two tables and why do you need - or think you need - to do this whole operation?

Edited December 23, 2008 by Guest

[bruceR]

One other thing: maybe somebody could suggest some other tool that has similar features to FileMaker, but which allows such basic functionality as cutting and pasting scripts?

No matter what tool you get, you're going to have to learn how to use it including reading the documentation and asking experienced users. This may reveal that the tool already has the feature which you claim is missing.

[Mark Welch]

"comment" wrote:

> It's hard to say what's wrong without seeing the exact script (and the relationships graph). * * * you don't show us the actual step. It's quite possible you are trying to set a field in the wrong table occurrence by mistake. Or it could be something else further down the script - of which we know nothing about. <

Thanks for your thoughtful reply. I absolutely agree that's it's "hard" (really, impossible) to diagnose problems without seeing the actual code. Unfortunately, FileMaker doesn't seem to provide any way for me to show you the script; there is no way to cut and paste it. Given the large number of potential typos caused by the requirement that everything be manually typed oer and over, it's also frustrating that the script editor doesn't show any kind of formatting (e.g. showing variables in a particular color or font). It's very, very frustrating for me.

> A simple way to debug a script without having the Advanced version is to use Pause Script and Show Custom Dialog at critical points. <

Yes, I've tried this, but it requires its own series of debugging steps to get this right, and it still requires extensive trial-and-error to track down the problem. I spent three hours today just tracking down a problem which turned out to be a typo in one reference to a particular variable (I had typed "$book_title" instead of the actual variable name which was "$_book_title"). For the current problem, I suspect it could easily take dozens of hours of "hit and miss" with this technique.

> BTW, I wonder why don't you simply import the records from Source into Destination (assuming you do need the interim Source table). <

As I said, I am "transmogrifying" the data. For example, some values in some fields need to be split into two (or more) separate fields (one specific issue is separating the "articles" in product titles: "The Holy Bible" or "Holy Bible, The" must be converted into two fields: Article ("The") and Title ("Holy Bible"). Some fields have extraneous data in them (e.g. an item titled "Pablo Picasso - The Flute Player" needs to be split into separate fields for Artist (Pablo Picasso), Article ("The"), and Title ("Flute Player"). In the absence of any better technique, I am using "brute force," simply iterating through the records and checking each applicable field before copying valid data or transforming invalid data into valid data.

I've created two separate "exception tables" (one which lists 'articles' ("The ", ", The", "A ", ", A", "Las ", ", Las", etc.) that might appear at the beginning or end of a field (including a number of typo variations without a space after the comma), and the other to deal with a much longer list of less-frequent changes which must be processed (like the Picasso example).

Edited December 23, 2008 by Guest

[Mark Welch]

Bruce: Thanks for your reply. I don't see any way to cut and paste scripts. (I know that I can cut and paste one "script step" from the current script into another section of the current script -- but that's one line of code at a time, and only within the current script, and of course this technique is extremely vulnerable to sequencing errors if any single line is pasted out of sequence.)

I'm afraid your longer explanation is whistling through the large gaps in my brain -- I don't understand some of the concepts, such as what a "global field" is or how I could refer to something called "Selected Record" (with or without a leading "g") using a reference that looks like a table name?

I'm continuing to work through the documentation -- since FileMaker doesn't provide printed manuals, and scatters its documentation across a number of separate PDF documents and help files, I've tried to fill in the gaps in what I'm learning using (so far) two other books, reading some sections many times ("Special Edition Using FileMaker 9" and "FileMaker 9 Developer Reference").

Clearly, I made a huge mistake when I accepted the claims of some people that FileMaker is an excellent "rapid application development tool." The people at FileMaker were very clear that this is NOT their view, and they told me that the work I wish to do will require just as much effort using FileMaker as using any other platform (such as PHP/MySQL).

Edited December 23, 2008 by Guest

[comment]

FileMaker doesn't seem to provide any way for me to show you the script; there is no way to cut and paste it.

See:

http://fmforums.com/forum/showpost.php?post/288599/

I spent three hours today just tracking down a problem which turned out to be a typo

Such are the joys of debugging. It doesn't get much easier with Advanced: there's no magic in there. Basically, all it does is let you see the script while it runs step-by-step.

For example, some values in some fields need to be split into two (or more) separate fields

And after that's done, you can import the result into the final destination.

[comment]

FileMaker *is* an excellent RAD tool, IMHO. But it does have a learning curve. Still, I believe that once you have a rudimentary grasp of the basics, you can make things that actually work much quicker than in a "real" programming environment.

[bruceR]

Bruce: Thanks for your reply. I don't see any way to cut and paste scripts.

Select script(s)

Select menu "Copy"

Same for script steps.

[bruceR]

I'm continuing to work through the documentation

Did you start with the tutorial that is included in your install folder for the Filemaker application?

[bruceR]

"comment" wrote:

> It's hard to say what's wrong without seeing the exact script (and the relationships graph). * * * you don't show us the actual step. It's quite possible you are trying to set a field in the wrong table occurrence by mistake. Or it could be something else further down the script - of which we know nothing about. <

Thanks for your thoughtful reply. I absolutely agree that's it's "hard" (really, impossible) to diagnose problems without seeing the actual code.

You don't need to wait for anybody.

Post your example file; whatever you're working on right now.

See the "Manage Files" link right here in the reply editor.

[Mark Welch]

First, Bruce: I did try copying the script. And yes, of course, I can copy and paste an entire script within FileMaker. That doesn't provide me with anything I can post here -- I just have another copy of my script locked within the rigid structure of FileMaker.

Or were you suggesting that I just do a screen-capture of each viewable page of the script, and post the screen shots here? This format truncates parameters and options.

I do realize that I could post the entire FileMaker file, but when I considered doing so earlier, even after I stripped out all unrelated data from tables not accessed by this particular script, the file was 6MB in size (4MB compacted) and still contains some proprietary data that I can't post publicly. I guess I could "Save Copy As Clone" but then there's no data at all to see how the script works. Hmm.

Second, to "comment" -- I did try printing to PDF (earlier today, and also in my prior attempt last week). Unfortunately, when I cut-and-paste from the PDF file, there is some garbling of the script; some characters are lost, and some strings are duplicated, and line breaks appear where they did not exist in the PDF or source script. I guess I could just post the PDF file, but before I do that I'd need to look and see if the PDF file really is visibly identical to a printed copy of the script, and in turn whether a printed copy of the script presents the complete information (including parameters etc.)

BUT the good news is that after continuing with the "trial-and-error" insertion of the "Show Custom Dialog" script step (which I'd started earlier this morning), I finally discovered that the problem was something I hadn't considered: I had called another script from my main script ("Perform Script"), and the "subroutine" script included a Go To Layout script step -- leaving me in the wrong Layout after the return from the subroutine, thus throwing the "target" error message when the next step tried to save data from the ScriptResult into the no-longer-current table. (I chose to use a subroutine because the same sequence will apply to several situations -- now I just need to remember that any time I call this script, I must add a "Change Layout" script step after the "Perform Script" step. I suppose I should check to see if there is a way, at the start of the subroutine, to store the current Layout name, and then re-select that layout before exiting the subroutine.)

I really can't imagine any reason why FileMaker doesn't identify the position in the script where the error occurs. I would have saved several hours if I could have seen that the error was occurring on the next "Set Field" event after the "Perform Script" step. (Are there really any other scripting languages that don't provide for a line number when an error is thrown?)

Again, thanks for all the suggestions and assistance. I know that I've been quite grumpy, but I really do appreciate your time, effort, and expertise.

Edited December 23, 2008 by Guest
Clarifications.

[LaRetta]

FileMaker doesn't seem to provide any way for me to show you the script; there is no way to cut and paste it.

Select script(s)

Select menu "Copy"

Bruce, it seems that Mark is asking how to copy an entire script to present HERE and not how to copy a script to paste back into FileMaker (ie, duplicate a script). If your suggestion works for Macs (and it may), it unfortunately doesn't work for Windows.

Mark, you can print to a text-only printer or print to PDF and then open the PDF and copy (Edit > Select All) the script from there; and there are probably other ways. But I agree that an easier method should be available to copy the text of scripts for pasting into Developer documentation and so forth. If you don't have PDF, you can download free CutePDFPrinter (or any number of other freebie PDF makers) or you can install text-only printer (should come on your Windows disk). You would then print to file with it.

BTW, neither does copying script-steps work. Once copied, a CTRL-V (paste) into a text file produces nilch.

LaRetta

Edited December 23, 2008 by Guest
Added a BTW

[bruceR]

Bruce, it seems that Mark is asking how to copy an entire script to present HERE and not how to copy a script to paste back into FileMaker (ie, duplicate a script). If your suggestion works for Macs (and it may), it unfortunately doesn't work for Windows.

Mark, you can print to a text-only printer or print to PDF and then open the PDF and copy (Edit > Select All) the script from there; and there are probably other ways. But I agree that an easier method should be available to copy the text of scripts for pasting into Developer documentation and so forth. If you don't have PDF, you can download free CutePDFPrinter (or any number of other freebie PDF makers) or you can install text-only printer (should come on your Windows disk). You would then print to file with it.

BTW, neither does copying script-steps work. Once copied, a CTRL-V (paste) into a text file produces nilch.

LaRetta

I realized that later. Mark was talking about how he needed FileMaker Advanced to copy/paste scripts, and that was the misleading part. As you mentioned, it won't help get scripts posted here.

Best bet here would be to post his whole file - which would help deal with relationship issues also. Next best, print scripts to PDF/text as you describe. But a screenshot would also work, at least we could see the script content.

[bruceR]

I do realize that I could post the entire FileMaker file, but when I considered doing so earlier, even after I stripped out all unrelated data from tables not accessed by this particular script, the file was 6MB in size (4MB compacted) and still contains some proprietary data that I can't post publicly. I guess I could "Save Copy As Clone" but then there's no data at all to see how the script works. Hmm.

I really think you would benefit from posting a clone, cleaned up as necessary. There is still a LOT we can tell about relations, field definitions, scripts, etc. You also know how to zip a file, right?

As noted in my recent message I did not realize you were trying to paste scripts HERE. But now I understand.

[bruceR]

I really can't imagine any reason why FileMaker doesn't identify the position in the script where the error occurs. I would have saved several hours if I could have seen that the error was occurring on the next "Set Field" event after the "Perform Script" step. (Are there really any other scripting languages that don't provide for a line number when an error is thrown?)

That's what FileMaker Advanced is for. Somebody was less than helpful in guiding you originally it seems like. If you're a user, buy FileMaker Pro. If you're doing any development at all, then get FileMaker Advanced.

If you're developing solutions that's really the best way to go. It's only money. Get it sooner rather than later, you'll save valuable time. I wish they had a crossgrade upgrade path but I don't think they do.

Edited December 23, 2008 by Guest