Scripting with two Tables

[mhemans]

I have a db with two tables. I import into one table from an excel spreadsheet, with multiple rows of data. That works just fine, but want to create a new record in the second table and copy specific fields to it. The 2nd table also has portal for the records in table 1. I can get the first record created in table 2 but does not create the remaining rows/records from table 1.

Help please!

[bcooney]

Would you please describe what you're trying to achieve in more detail? Copying data btw tables is a red flag. Perhaps if you tell us the table names, etc., we could offer a better approach.

[mhemans]

Where's the red flag? All that I am trying to do is make a script. I have a script that allows me to import data into my db and have the field's pre-mapped and now I want to expand on it and copy a couple of fields into a second table. I only need this for items that I import. Right now I am manually entering into table 2. If I make a revision, I do it my duplicating the record in table 1 and editing the data that is changing

The two tables have a relationship based on a number. The first table acts as a log of all items regardless of number and that number can be in there more than once. The second only has the number in there once and shows the history from table 1 based on the number.

[bcooney]

"I can get the first record created in table 2 but does not create the remaining rows/records from table 1."

Are you saying that you can create the record in Table2 and cannot see any related table1 records in the portal? How are Table2 and Table1 related? How do the Table1 recs get the Table2 key field?

How do you handle the case when you don't need to create a Table2 record, because it already exists?

Are you looping thru the table1 recs that were not yet processed?

Real data examples would be helpful.

Edited March 5, 2009 by Guest

[mhemans]

Currently I am creating the record in table 2 manually, type in the the fields that contain relationships and the remaining fields populate themselves via the relationship. I should also mention that table 2 is the main layout that I am viewing and am trying to work my way from table 1 because table 2 give me a comprehensive look at the data.

Define "foreign key".

I have tried the set field and it does not work as part of my import script. But creating a script that contains copies and pastes between the two tables does, but it only gets the first row of data from the spreadsheet. I want all the rows that are on the spreadsheet not only import into table 1 (they do import into table 1 just fine), but table 2 as well w/o doing two separate tasks.

When the data exists in table 2 and I want to add to table 1 I have a script/button in the portal of table 2 to create a new entry in table one.

I am not sure how to answer the last question.

[bcooney]

What is the relationship btw Table1 and Table2? What data are you looking at...please use real data in your description. This discussion is going nowhere until you really describe what your doing.

"...type in the the fields that contain relationships" Huh?

"table 2 as well w/o doing two separate tasks"

Why do you need the same data in two tables?

"Define "foreign key"" : Please adjust your Skill level!

[comment]

Maybe you could just script importing the records from the first table into the second, right after the original import.

I say "maybe", because I too am perplexed by your description. A straight import would just duplicate the information between tables, which seems entirely redundant. If you only need some fields, why not simply use those and ignore the rest?

[bailey14]

I have a similar type of issue. How do I approach this? I don't see a clear answer.

Smile!, Be Happy!

[comment]

There can be no clear answer without a clear question. :

[bailey14]

What's not clear about the question?

[comment]

Earlier, I said was perplexed by mhemans' description. Then you say you have a similar issue. So what does that mean?

In general, I think you will get better answers when the context (what you have) and the purpose (what you need and why do you need it) is clear to those who provide the answers.

[bailey14]

My issue goes something like this:

I have customer orders that are given to me with an excel spreadsheet. I track them using a filemaker database by importing them in, so that it keeps me from having to do extra typing. I would to track the data in a separate database by customer name so that I can see a customer order history as a summary. So I thought that I could use the response to the question that mhemans posted to apply to my process.

It means that I have a similar issue to what mhemans posted. If you don’t have anything positive to add, then don’t respond.

[mhemans]

What are you perplexed about? Maybe I can elaborate on something.

[comment]

Take your pick:

Would you please describe what you're trying to achieve

Perhaps if you tell us the table names, etc.

What is the relationship btw Table1 and Table2?

What data are you looking at...

please use real data in your description.

Why do you need the same data in two tables?

If you only need some fields, why not simply use those and ignore the rest?

[bcooney]

c, let them help each other, obviously we're just annoying them with our questions! Where's LaRetta? She usually comes in about now with a fresh approach, lol.

[mhemans]

Well, I have an item # that is in both tables. I currently have a relationship set-up using that # between table 1 and 2. Table 1 has the item # and all of its components/parts. Any time the item is revised a new record is created in table 1. I want to use table 2 to show the item # at the top and use a portal to give me a summary of table 1 below. Since new parts are given to me in an excel spreadsheet and I import them into the db via table 1. I can even get the first item/row into table 2 from the import. If the spreadsheet has more than one row of data, it will import fine into table 1, but stops after 1 row in table 2. I should add that my import occurs with a script to keep mapping of the fields in the proper order. I also thought that I could tack something on to the script that will allow me to add the extra records into table 2. If the item # is revised I have a script that duplicates a copy of the matching item # via the portal and I change any data that I need.

In the end, I only want to look at table 2 and see a history of that item # via the portal. The only hang-up I have is that I need to manually add the new record in table 2 and add the item # from spreadsheets with rows below line 1.

Hope that helps.

[bailey14]

I thought that this was a forum, not a user bashing.

[bcooney]

Maybe what you really need to do is stay with one table, table1, and create a sub-summary by item#.

[mhemans]

What is preventing me from doing what I am thinking? Just curious.

[bcooney]

Um, I believe you said, "If you don’t have anything positive to add, then don’t respond."

We're asking for details so we can help--for free!

[comment]

With your permission, I'd like to call table 1 'ItemHistory', table 2 'Items', and the field that uniquely identifies the items in both tables 'ItemID'.

I would suggest that in the Items table you set validation of the ItemID field as Unique, Validate always. Actually, that's the only field you need in this table.

In the ItemHistory table, make sure you have a SerialID field defined to auto-enter a serial number. Define the relationship so that records from ItemHistory are sorted by SerialID, descending.

Now you can simply import the data from the spreadsheet into ItemHistory, and immediately after that import from ItemHistory into Items (you only need to import ItemID into ItemID). Because of the validation, only newly added items will be imported.

Place fields from ItemHistory directly on a layout of Items - they will display data from the last update of the item (that's in addition to the portal that lets you view previous data).

[bcooney]

OK, from your description I'm guessing that your items consist of many parts. You'd like to see a record for Item and below it a portal of its parts. This is a classic 1 to many relationship.

You're only receiving the children records (parts). You need to create a Parent (Item) record. You cannot import into two tables at once.

I would import the parts and then loop thru them creating Parent Item records as needed.

The loop would look like:

import new parts to part list

start at part list viewing new part records

Loop

//If the relationship from this child part to its parent Item does not exist

If ( is empty (item_Part::ItemID) )

//capture itemID into a variable

Set Variable $itemID to Part::ItemID

go to an item layout

create new record

set Item::itemID to $itemID

//go back to the part list

Go to Layout PartsList

next record, exit after last

End loop

Now, how are you handling parts that drop?

A subsummary report of just one table, Parts, by Item# will also give you a listing of Items and their component parts.

[mhemans]

thanks to all. I will give it a try and let you know.

[LaRetta]

I thought that this was a forum, not a user bashing.

Asking for further detail, so we can give clear answers, is not bashing. If you want answers with no information then I suggest you try here .

[Vaughan]

I'm still chuckling about Barbara's "adjust your Skill Rating" comment.

And I'm impressed that nobody suggested repeating fields as a solution. :