Use Portal vs. Fields vs. Repeating (gulp!) Fields

[FMDuck]

I think a specific answer would be a lot to ask for so I'm looking more for opinion and maybe some probing questions...

The attachment shows a wall with plywood splices and for reference is laid out using spreadsheet type columns and rows. Note that the portal doesn't match the drawing. In this case the parent table, containing the Wall as the parent already includes over 2000 fields. I've spent 8 years trying to break up this table and after many attempts have given up.

Here I need to be able to:

- Calculate and create these plywood pieces based on the size of the wall (up to 3 pc. x 15 pc. or 24' high x 60' long)

- Identify the upper left corner of each piece to create a drawing using JavaScript in the web viewer. (I'm finding this challenging when using a portal of related records because of a combination of the Evaluate( ) and Let( ) functions required to render the drawings along with stored calculations that auto update.)

- Identify the minimum width of all pieces that make up the first column and the minimum width of all pieces that make up the last column.

- Assure the materials in column B (based on the drawing) total the right height. The problem here is that I can easily add the piece in the A1 space to the piece in the A2 space to determine the height of column A but am having problems clearly identifying that the piece in A1 also consumes the space of A2.

I'm hoping that someone has done something like this and I'd like to know if portals/related records, local fields or even repeating fields may provide the best solution and hear what you may have run across.

Thanks in advance for any help.

[comment]

How would one do this (excluding the drawing part) without a computer?

[FMDuck]

Typically this would be done on a piece of grid paper then converted to a material list so currently I think I need to do it backwards.

The example I gave is a small part of the larger benefit of the software, but in general, it allows someone who knows nothing about crating to design a mil. spec. container so I can't mimic the normal process. I need to build the material list then create a drawing from it.

There are only 8 different possible sizes of plywood pieces, so the current version of software has 8 sets of static fields that include length, width and QTY. The new version needs to account for each piece individually, even if most are the same size.

[comment]

My point is that if I don't know how to do this using pencil and paper, then I don't know how to do it Filemaker either. I am not even sure what's the input and what's the result in your example - let alone the process to get from one to the other.

[Ocean West]

Back in 2006 Richard Carlton demonstrated a solution at the keynote...

That outputted the values from child table into a WV dynamically drawing a sketch

http://www.ard-inc.com/manholeentry.html

http://www.ard-inc.com/trenchpipeline.html

http://www.ard-inc.com/vaultentry.html

There was also a video... http://www.ard-inc.com/download/3d_drawings.avi

you may need VLC to play on mac.

Not sure what secret sauce he is using but wonder if it's something you may confer with him on.

Edited May 3, 2010 by Guest

[bruceR]

Regarding portals vs repeats - it is possible to use both.

Using the virtual list technique, you can have a portal that points to a set of repeats. You can filter and sort the portal; and write to the repeats.

The basic idea of the virtual list technique is that you have a utility record set with a standard number field 1 through N, where N is the number of records. Generally you do not create or delete records in this table. Then you have unstored calc fields that get the Nth value of something; a global field; a global variable; a repeating field; etc.

And of course, with onObjectKeystroke script triggers, you do know that it is possible to "edit" a calc field, right?

If Text1 is a text field; and Text1Calc is a calc field; you can enter Text1Calc and type and send all the keys to Text1.

Bruce Robertson

On May 3, 2010, at 11:39 AM, Jeff Duck wrote:

I think a specific answer would be a lot to ask for so I'm looking more for opinion and maybe some probing questions...

[FMDuck]

I appreciate the feedback from all but trying to explain this is a good example of why I've spent 8 years trying to come up with the next version.

Comment - I THINK you're looking at this from a different perspective than what I'm looking for.

I'll explain differently - this covers one part of my question:

Each child record (piece of plywood) has a field that includes a JavaScript function stored in a text field with X,Y,X2,Y2 coordinates. Those coordinates are created in a Let() function in a web viewer in the parent table where the JavaScript code is used.

Since the JavaScript function is parsed into the calculation that includes the Let() function then the coordinates must be added in using an Evaluate() function. The Evaluate() function doesn't evaluate any results of the Let() function.

The Let() function is used and is where it is because of the order of the calculations and to create more fields would result in a total of about 250 new fields (this is just one of six sides of a crate and the size of each side can be dependent on the size of the four that it touches, etc.)

I don't know if this clears anything up but figuring this out would get me though one layer of the problem.

[comment]

I am still struggling with the definitions. Are the coordinates of the child record known? Or do they need to be calculated from something else? And this part I didn't understand at all:

Since the JavaScript function is parsed into the calculation that includes the Let() function then the coordinates must be added in using an Evaluate() function.

What does "parsing a function into a calculation" mean? And why MUST the coordinates be added using Evaluate() - why cannot they be, for example, declared as variables?

[FMDuck]

Creating the calculation for the coordinates of the child records is the first step that needs to be accomplished, so generally they are not known. Determining this point across the relationship(s) is part of the challenge. The coordinate of any piece of material can be dependent on a variety of up to about 600 fields in the parent table. It would be easier if the 600 fields could be normalized into a child table but that creates a different problem.

I GREATLY appreciate the attempt to answer my question (especially since you answer about 1/2 of mine here in FMForums) but the problem requires a great deal of explanation. Yesterday I spent about 2 hours trying to figure out a clear way to convey the problem and now today another 45 minutes. The broader problem can't be described in '10,000 words or less'.

Inter-dependencies and design variations create literally thousands of options as to where components need to be placed. I can move around fields and methods to where I can easily generate the results but not with the desired user interface. One option that I tried previously worked perfectly but it took FM about 5 minutes to redraw what appears to be a simple side of a crate. One issue I'm faced with is that the software takes what is usually a 5 minute process down to about 15 seconds but must also take what can be a 1 hour process and reduce it significantly. This problem is relative to the size of the crate. Although most options that I come up with will be acceptable to the user when designing large crates, it makes the time required to design a small crate unacceptable and in the end, doesn't save the user's time.

[comment]

The broader problem can't be described in '10,000 words or less'.

That's possible, but I don't see how I can provide a useful reply without something more tangible.

I can easily generate the results but not with the desired user interface.

This seems to be the crucial point here, perhaps? I tend to break this into two stages: first, the process itself (which appears to be solved?) and only then the user interface, in two parts: (a) how does the user enter the data, and (: how are the results displayed back to the user. As much as possible, the solutions of these two should have no impact on the process derived in the first stage. Note also that the user interface, on both parts, can be designed without knowing anything about the nature of the process itself.