Hi all,

Still working on a conversion here..

We have a layout in Table A, which has three portals, B, C and D.

B displays records that are children of A.

There is a button on portal B which will change a global on A, so that
children of C and D will be displayed in their respective portals.

So basically C and D are children of B, which in turn is a child of
A...

The problem is that the button no longer works correctly, and any
attempt to Commit Records when new records have been created in B, C &
D causes a validation error (which checks that a parent record for C &
D exist).

Any ideas? Let me know if this isn't clear... its not the most
intutitve structure.

Thanks
Andy

Andy,

It is confusing. I think you need to provide more information on how the
tables are related, and how the global field is involved. How does changing
a global in A affect which records are related in C and D? Portals for C and
D would show records from C and D, not their children.

Can you supply more detailed information about the relevant tables, which
fields are related in each, and how/why the global field is involved?

Bill

"Andy" <ajohnstone (AT) capcitypress (DOT) com> wrote

As Bill says, it's difficult to see the whole picture here. But it is
likely relevant to point out one change from 7 to 8 (so certainly from 6
to 8) that effects portals. When you are inside a portal -- a portal
row is selected or the cursor is inside a portal field -- the context of
any script steps is not from the parent record, but from the portal itself.

So if one of the portal rows is active, your script that sets the
A::Global should happen correctly because the relationship from B to A
is valid. But if your script is attempting to write to C or D from
inside the portal, I could see where this might fail.

If this is not the issue however, here are other suggestions that may
(or may not) help refresh the C and D portals:
- Add a Commit Record step after setting the A::Global field.
- After adding new records in C or D, try setting A::Global to itself
and then Commit Record.
- Try the Refresh Window step in addition to or instead of the Commit
Record steps above. Be sure to use the [Flush the Cache] option.




Andy wrote:

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Pro Solutions Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance

Bill,

I agree, its confusing. Ah, the joy of legacy systems..

Basically, we have a single job record (we are a printing company),
which can have many signature records. In turn, each side of a
signature (basically a piece of paper) can many ink colors.

So we have a table structure like so:

Job
----
ID (Serial Number)
SigID (Global)

Sig
----
ID (Serial Number)
JobId

SigColor
------------
ID (Serial Number)
SigId (Number)
SideId (Number, and its always 1 or 2)

The layout exists on the Job file, portal B displays all Sigs for the
job, portal C displays all colors for the selected sig on side 1, and
portal D displays all colors for the selected sig on side 2.

Each record displayed in Portal C has a button, which, when clicked,
updates the SigId in Job.

There are two relationships in Job, both to the SigColor table. One
defines a relationship where Job.SigId + "1" = SigColor.SigId + "1" and
the second is Job.SigId + "2" = SigColor.SigId + "2".

The effect is that as the SigId in Job is updated (by clicking the
button on Portal B), the records displayed in portals C & D changes.

Hopefully that is more clear..

Andy

Howard,

Refresh window loses the record i just entered into Portal B.

There is an odd thing; if i use Commit Records with Skip Validation, i
end up with a blank row, the record i was entering, followed by another
blank row. I expected the blank row at the end, as that's how you
enter new rows into the portal,but the blank row BEFORE is odd..

Andy

Corrections...

Each record displayed in Portal <b>B</b> has a button, that updates the
SigId in job.

Also, there is another relationship between job and sig..should be
obvious, but just want to be clear.

Thanks
Andy

Andy,

I'm not sure why SigID would be appropriate for use as a global. Global
fields are used when the same value is to be used for ALL records in a
table. It seems to me that SigID should vary depending on the Job.
Additionally, there should be no need for a button to "update" any other
table, when the database is structured correctly. Let me explain:

It seems to me you have the following major entities:

Colors
Layouts
Signatures
Jobs

There are multiple colors across multiple layouts and jobs and customers. So
Colors would have fields like this:

Colors
======
ColorID
ColorName

There can be multiple layouts (2, 4, 8) per signature, so Layouts and
Signatures looks like this:

Layouts
=======
LayoutID
LayoutName

Signatures
=======
SignatureID
SignatureName

And finally there are the Jobs:

Jobs
====
JobID
JobName

"But wait!" you say -- "How do I know what Colors go with which Layouts and
which Jobs?"

The answer is a "join" table, which links them all together:

JoinColorsLayouts
==========
ColorID
LayoutID

JoinLayoutsSignatures
==========
LayoutID
SignatureID

JoinSignaturesJobs
==========
SignatureID
JobID

These tables literally just have two fields... and one record for each
unique combination of the two.

These "JoinLayoutsSignatures" table for example just tracks which job goes
with which layout... and by virtue of the all the other join tables, you can
very easily tell which colors are required by which jobs. When using the
database, you are in the business of adding and deleting records to these
intermediary tables, not using scripts to force-update fields in across
tables. Using join tables, a change in one related table "cascades" through
all the others without any special effort.

Even if you could get them working as you expected, the updates via button
are just a recipe for disaster. Inevitably a table is not updated correctly
and you have big problems to track down later.

Bill



"Andy" <ajohnstone (AT) capcitypress (DOT) com> wrote

I think Howard was on the right track with diagnosing your problem. In
FileMaker 6 a "Set Field" would automatically commit the record but you
still had to "Exit Record Request" sometimes to be sure that the
relationship had a chance to refresh before you set other values using
the relationship.

In FM7 a "Set Field" does not commit the record, from the point of view
of a relationship it doesn't even seem to update at all. Try changing
your script attached to button in portal B to the following;
- set field "Temp global Number" to Get(PortalRowNumber)
- set field "Job::SigID" to "ID"
- commit record request
- go to field (some field in portal B)
- go to portal row by calculation "Temp global Number"

You should not need to refresh window or flush cached results.

Thanks for all the replies,

FP and Howard,

I think the problem the commit record is running into is that the
Colors for some reason are being told to save before their parent sig
record. I've tried those solutions, and none have solved the problem.

Bill,

Yes, the SigId on Job was not global to begin with, i changed it trying
to fix the problem, but since changed it back.

I think the schema we have accurately fits; a signature always has two
sides, it can have one and only one layout (what we call a signature
definition, which is another table..) and can belong to one and only
one job. Your suggested schema states that one job can have many
signatures, but one signature may also belong to many jobs. Also, a
signature may only have one definition (layout); otherwise when we cut
the pages, side 1 pages will be cut to proper size, but side 2 pages
will likely be cut in the middle of the page.

I also am just supposed to get the upgrade working with as little
effort as possible because, at least in this part of the business, we
will be building a .Net / Sql Server solution that will replace most of
the Filemaker application.

I do appreciate your suggestions and thank you for all the help so far.

Andy

Irrespective of the number of layouts/sides, the point of my post was that
manual clicking to update other files should never be necessary.

I am very curious why you are moving to .NET/SQL Server for this also.

Bill

"Andy" <ajohnstone (AT) capcitypress (DOT) com> wrote