Add Multiple Records

[Lashex]

I am trying to figure out how to perform the following straightforward task. I'll use pseudo-logic to describe my question.

loop until count > Max(FooTO::ID)

   Add New Record to BarTO

   SetField BarTO::Date of new BarTO record to Current Date

   count++

end loop

BarTO::ID is a "looked up" relational value (I think of it as a foreign key) from FooTO:ID.

Any and all help will be appreciated.

p.s. I have to say... I'm a newbie to Filemaker 7 but so far it is the most non-intuitive (sometimes even counter-intuitive) data and coding environment I've ever worked in. I have experience with C, C++, JavaScript, Java, J2EE, SQL and AppleScript if that helps with any mental transformation suggestions on how to thing in the FM7 world.

[Søren Dyhr]

Where do you get Max(FooTO::ID) from, it seems to me you need to create exactly the same number of records in both TO's But whats the purpose??? Wouldn't it be easier to make a scripted replace in a dedicated field just one table. You could also Import betweek tables, with an autoenter field set "Current Date"

All in all does it look like a syncing job, which points in direction of a less convenient relational structure.

--sd

[aaa]

I've ever worked in. I have experience with C, C++, JavaScript, Java, J2EE, SQL and AppleScript if that helps with any mental transformation suggestions on how to thing in the FM7 world.

If you have so experience you can easy solve your question byself.

[Fenton]

First, aaa, it is inappropriate and rude to tell someone to solve their own question. It was entirely appropriate for Lashex to state his experience (though it may trigger "programming envy" in we mere FileMaker developers -). His example is common to other languages. Besides, we may need his/her programming advice someday :-/

Basically, we don't know exactly what you're doing, so we can't tell you if there's a better "FileMaker" way to do it. But we can tell you a way to write what you've got in FileMaker terms. FileMaker is more "procedural" than most other languages I think. There is often more than 1 way to do something; though often only 1 (or 2, or 3 :-) "best" way(s).

When you're going to loop, you want the criteria for stopping the loop to be something quick, because it happens at each iteration. Getting the Max() of another table's field value is one of the slowest operations. So, if you're going to use that function, only do it once. And, since Max() of an existing auto-enter serial number should be the same as the last record's serial,* it is much faster to just go to that table, go to the last record, and set the serial into a global field** (_gMaxCount below; in either table, but probably in the one that's running the loop). Then you've got a value that can be checked quickly.

Or get it via a Constant, 1=1, or a Cartesian join [X] relationship, using the Last() function (in the Aggregate section). Or sort the relationship descending, and get it via the plain relationship.

The FileMaker command Loop starts the loop. To count a loop, you can either create a global number field, initialize before the loop, then increment by 1; by adding 1 to itself, just like an AppleScript "repeat" (except you must create the field first). Ex.:

Set Field [ _gCounter, _gCounter + 1]

Exit Loop If [ _gCounter = _gMaxCount ]

Or, if you can ignore the current found set, just count the new records. Create a 0 found set before beginning:

Show All Records

Show Omitted Only

Then you don't have to increment explicitly, and it would be faster; but either method is fast. Your test would be:

Exit Loop If [ Get (FoundCount) = _gMaxCount ]

However, we don't know whether it's an auto-enter serial, whether records can be deleted, or whether you should just import (as S

[Lashex]

Thanks Fenton... I appreciate the positive thoughts.

To your point I only included my other experience so someone might be able to help me translate my frutration using a language (or concepts) someone might share with me. Now, the question of "what am I actually trying to do?" is of course a good one for helping others know how to help me.

I don't purport to be the best arranger of Filemaker tables, but here's where I am at.

• One table "Widget" collects all non-historic information about a widget; widget name, widget source, etc.
  
• Another table "History" collects multiple historic records about widgets; modified date one, modified date two, etc...
  
• Each historic record has History::WidgetID that is an Indexed, Lookup field to the Widget::WidgetID.

Goal: Add a new corresponding history record for all Widgets

Goal: Add a new corresponding history record to each member of a found set of Widgets

Goal: Eventually show a historic report about how many times and on what dates each Widget was modified.

Since my initial post, instead of my Max(Widget:ID) reference I've added a Widget::WidgetCount field which is a Summary field that stores the maximum WidgetID field of the Widget table. Furthermore, Goal #3 is why I cannot simply replace previously existing data.

Again, thanks for any and all help.

[Fenton]

I think you're over complicating it. You don't usually "lookup" an ID, in any database system. You create the ID somehow, then possibly use it to look up other fields. And you only need 1 field for the dates, with a record per each. I've made a simple example using a portal with "Allow creation of related records."

Widgets.zip

[Lashex]

As the "Indexed, Lookup" aspect of the ID is simply something I have done while learning FileMaker I appreciate the feedback.

I'll review your attached example and see if I can do what I need to do from it. Thanks.

[Lashex]

OK after looking at your example I do see how you're using the Portal capability. I've actually played with doing that method earlier. The portal approach would work if I had the screen real estate. Unfortunately my desire is to simply press a button to "Add History Item", the button then fires a script which adds a Historic Date record in the History table for the current Widget.

Also I would then like to have a button "Add History Item to All" that fires a script which would add a Historic Date record to all existing Widgets.

I've attached an example of what I am trying to do. I have the two buttons:

-- "Add Fertilize" which equals "Add History Item"

-- "Fertilize All" which equals "Add History Item to All"

...neither of which either work, or work the way I want them to. As before, I continue to appreciate the help.

Widget-Test.fp7.zip

[Søren Dyhr]

I've made a template for you, an import to the other table with a set global as the source for an autoenter, seems the obvious solution. But I've desided to cut down on my use of globals ...as a challenge. Why because calc's depending on a global is putting a strain on the wan/lan connection, because all other variables reside on server, but the global reside on the users workstation. And in order to prevent too severe package "chatting"...

Beyond that might I be forgetting something???

--sd

mainlist.zip

[Lashex]

OK here's my final functioning DB example.

This behaves the way I originally desired. I had to use a global to pass the date to each record versus having each history record default to the creation date. If you have a suggestion on how I might avoid the global I would appreciate hearing it; in any language/environment I try to avoid globals.

Thanks for all the help folks.

Widget-Test.fp7.zip

[Søren Dyhr]

how I might avoid the global

Thats easy done....

Freeze Window

If [ IsEmpty ( Get ( ScriptParameter ) ) ]

Go to Record/Request/Page[ First ]

Show Custom Dialog [ Title: "Add Fertilization"; Buttons: "OK", "Cancel";

Input #1: FertHistoryTwoTO::FertilizeDate, "FertilizationDate:";

Input #2: FertHistoryTwoTO::FertilizerType, "Fertilizer Type:" ]

Commit Records/Requests[ No dialog ]

Perform Script [ "Fertilize All"; Parameter:

Last ( FertHistoryTO::FertilizerType ) & "

[Søren Dyhr]

Now after some thinking have it occured to me that this is much simpler, given that you have to make a

special designed layout.... where the two fields occure in same textcolour as the background or such:

Import Records [ Source: "WidgetsTO"; Target: "FertHistoryTO"; 

Method: Add; Character Set: "Mac Roman"; Field Mapping:

"WidgetsTO" import to PortalContent::BelongsTo ][ No dialog ]

Go to Layout [ "Hist. Fertilizer" (FertHistoryTO) ]

Show Custom Dialog [ Title: "Add Fertilization"; Buttons: "OK", "Cancel";

Input #1: FertHistoryTwoTO::FertilizeDate, "FertilizationDate:";

Input #2: FertHistoryTwoTO::FertilizerType, "Fertilizer Type:" ]

Replace Field Contents [ PortalContent::Value1; Current contents ][ No dialog ]

Replace Field Contents [ PortalContent::Value2; Current contents ][ No dialog ]

Go to Layout [ "test" (test) ][code]

What I forgot the "Curent content" feature, but as I mentioned are you bound to get the found set the

import creates, by turning to a layout in that table, otherwise will all the tables records not just the

found set recieve the entered values!

--sd

[Lashex]

Trying the more complex script that is avoiding a gglobal only seems to create records as follows:

WidgetID  Date         Type

1          mm/dd/yyyy          12-34-56

2                                     12-34-56

3                                     12-34-56

...notice that only the first record actually has the entered date captured. I'll play around a bit more to see if I missed anything in your example.

[Søren Dyhr]

No it's me who's too careless with the typecasts, the parameters to go with the recursive calls should instead be:

Last ( FertHistoryTO::FertilizerType ) & "

Widget-Test2.zip