Manipulating Related Table without Using Portal

[eYz]

Is it possible that by using a script I can manipulate data in a related table without having to create a layout and include a portal for that table? I don't see such commands available in the script editor.

[Ender]

You can use the Set Field script step to change data in a table (current or related) without having the field on the layout.

If you are acting on a related field, you may need a portal just so you can select the correct related record.

[Fenton]

You'd only need a layout if you were using steps that required the field to be present on a layout. Set Field doesn't. You would have to address the target fields as related fields (Table Occurrence), much like v.6. You say you have a "related table," so it would be more or less the same as 6.

[MoonShadow]

Hmmm, it is my understanding that a layout determines which Table Occurence the script will run against. New Record Request, Find, Sort, Import etc. all depend upon being on a layout that references the correct Table.

I try to view my layouts AS my (old vs. 6) files. I couldn't imagine not having at least one layout view of every table to act as my 'other file' (related or not).

You can add records through the portal and manipulate the data, but if you ever plan on scripting, importing, exporting, etc. you'd better have a layout available.

[Ender]

To use Set Field, you would have be on a layout that in somehow related to the field's table.

[Fenton]

I think the original poster is going to have to be more specific if they want explicit advice. He asked, "Is it possible that by using a script I can manipulate data in a related table without having to create a layout and include a portal for that table?"

I would say, "Yes, it's possible," with the qualifications mentioned. You can set a field related to the current table (he said related, so one assumes he has defined a Table Occurrence) without needing an explicit layout for that T.O. in the current file, more or less like in 6.

But, as mentioned, which is different from 6, you will be setting it with fields evaluated FROM your current T.O. layout, though you may use a different T.O. from that of the current layout; somewhat like a "filter" (which we're used to using for conditional value lists). Does this make sense?

As to other types of data manipulation (which is really too broad a term, too many possibilities), such things as importing definitely require a layout based on the relevant related T.O.. Others, such as creating new records, may or may not, depending on the method; but generally would. Find, Sort obviously would; but I didn't really think of those when he asked about data manipulation.

Basically we don't really know what he's asking about. And yes, I need to learn more about all the ins and outs of the new relational model.

[MoonShadow]

Fenton said...I think the original poster is going to have to be more specific if they want explicit advice.

Absolutely. I kept trying to figure out why eYz was asking the question, ie, what is he trying to accomplish? That's what we really need to know. And I hesitated in responding because it is quite possible to use a table without a layout, like a lookup table; however, I've been tripped up on this one (more than I'd like to admit) and I've done things like - delete records from the wrong table.

With FM7, knowing which layout is being accessed throughout a script is critical (I have to constantly remind myself to ask that question) and eYz wanted to script his related. I had trouble imagining what he would want to accomplish that might not (at some point) require direct access to the table.

eYz, keep in mind that you can keep a User on the main layout, Freeze Window, switch layouts, run a script in your related table and then switch back ... and they won't even know it. Is that what you mean by not wanting a layout? Only that you don't want your Users to experience a layout switch?

[zincb]

If you simply don't want to display a portal where your users see it, you can create an "ALL FIELDS" layout, that contains both all fields in the current file as well as a one-line portal for every relationship the current file has.

When your script fires off, freeze the window, set user abort off (so they don't hit ESC and wind up in ugly-land), go to the all fields layout, do everything, then return to original layout.

[eYz]

Sorry for the late response! I wasn't able to check the site till just now. What I really want to achieve is this:

The user enters data into one table. Upon finishing a record, he clicks a button and a few records will be automatically created/updated in a related table. This process had better be transparent to the user. (No, this cannot be done by using calculated fields.)

I'm habitually thinking of using scripts/macros. How differently do I have to think with FMP 7?

[Fenton]

This can be done transparently. But, it appears the others were correct; you need another Table and its layout. That's what's different in 7, another table, not necessarily another file.

You're using the word "table," but do you mean FileMaker Table? We've got 'em now, just like the big boys :-)

You also say "related." In 7 this really means "Table Occurrence," which work much the same, but are more powerful and flexible. You look at them in a graphical interface, with the fields involved visible by clicking on the relational link box.

[Though you don't really even need such a relational link; you can just plop the other Table on the chart as is, then reference it as if it was another file (in 6 thinking). You can get global values this way. With a corresponding layout you can do all manner of things.]

In your case you need a layout for your T.O.. Go to that layout with a script, issue a New Record command. You'll be creating a new record in that Table, not in the original. You can access global fields in any table that you have a T.O. for, to get data. If you Freeze Window before leaving the original layout, no one will see.

Now, a question: Why, if the users are entering data in regular fields in one table, is it being redundantly replicated in multiple records in another table?

Secondary question: If the data is only needed in the other table, why not use global fields for its data entry, then script population?

Or, why not go directly to the other table's layout and show the original table's fields are related fields? Of course, this is not really "transparent."

To answer these questions one needs to know what the data is, how much is in one place and how much in the other. I just don't get why the data exists in its entirety in two places.

[Fitch]

He never said he was going to replicate the data. Maybe he's creating records based on the new data but with different values.

That does bring up a good point, though -- where it was often necessary in FM6 to have duplicate data in related files, it will seldom be necessary in FM7.

[eYz]

No, the data won't be replicated. I'm working on a link between my sales and accounting modules. After a sales record is created, the sales rep has to click a button to confirm his receipt of payment. Based on the information of the sales record, some calculation will be done and a bunch of transactions will be registered into the accounting module. For example, net sales goes into the Sales Income account, tax goes into the Tax Payable account, and gross sales goes into the Undeposited Sales account if it's a cash deal. In some cases, things get even more complicated.

Anyway, I figure that the use of a hidden portal is my only option. Thanks for all the input!

[Fenton]

OK, accounting. Redundant data is necessary. But you still don't need a portal. If you create a layout for each T.O. you can flip between them, create and populate the record(s), then flip back to the original.

Portals are really only for viewing data; which you're explicitly not doing. Once you're on the layout of a T.O., you're on its records. Freeze Window will hide those layouts during a script, unless you do something that makes them show. Everything you've said above would happen in a split second.