Script to move data between tables

[T.J.]

I'm a newbie when it comes to scripting and I'm hoping someone can help me out.

I have the following records in a table

Product - Count

Rock1 - 1

Rock1 - 1

Rock1 - 1

Rock2 - 1

Rock2 - 1

Rock3 - 1

Id like to build a script that takes this information and moves it to a new table and summarizes by the product.

Product - Count

Rock1 - 3

Rock2 - 2

Rock3 - 1

I am aware that you can get this result by using calculations or self-join tables but am interested in learning more

about scripts and doing this with a script.

I'm attaching a file with the tables and includes my attempt at the script. I think I need a if/else within the script

to do what I want but am having no success.

My script returns

Product - Count

Rock3 - 6

Any help with this is appreciated.

move_data.zip

[bcooney]

You need a sub-summary report. Run the Report script in this modified version.

move_data2.fp7.zip

[T.J.]

Thanks for your response. I'd still like to know how to do what I'm trying to do using a script.

[bcooney]

I know you're looking for a chance to learn how to script. This really isn't it. You shouldn't copy/move data around in a database, as a rule.*

*There are exceptions, such as copying a price/cost into an invoice line item.

[Søren Dyhr]

You shouldn't copy/move data around in a database, as a rule.

Never the less is it a rule which should be repeated a gazillion times - here in this forum!

--sd

[T.J.]

I've read that statement many many times rumaging around in this forum. I'm trusting your expertise and will restate my question. I have to believe that I'm probably missing a aha moment with relationships between tables to accomplish what I want.

What I'm after is a way to compare data on a layout I've created.

The detail of the data resides in these fields.

Warehouse Gender Customer Product Count

In my layout I show the Customer and Product Number.

Under that I'd like portals to show the summary of that product in the current record by warehouse

Then under that a portal under that to show the summary of the product by gender.

I've played around with self join tables, getsummary calculations etc and just I am not getting what I'm after.

Hope you guys will take a stab and this can be accomplished some other way than moving data around. Thanks

move_data_or_is_it_a_relationship.zip

[comment]

This could be done by a summary report again, or by relationships. But for relationships, you need tables - namely, in your example, a table of Warehouses and a table of Genders.

Oops: and Products, of course.

Edited March 21, 2009 by Guest

[T.J.]

So to view this on a layout and not a report how would I create the tables from the detail. Would this be a self-join table?

[comment]

No, it would be a structured solution with dedicated tables for:

• Products (each product has ONE unique record here)

• Warehouses (each warehouse...)

• Genders (ditto)

and a table of ProductCounts, with fields for:

• ID (auto-enter serial number)

• ProductID (to link back to the Products table)

• WarehouseID (to link to Warehouses)

• Gender (to link to Genders)

• Count (the actual amount)

and perhaps a field to identify the count, in case you do periodical inventory counts and want to keep them (which would mean yet another table where a new record would be created each time a count is made).

Once you have that in place, you can select the product you wish to view by placing its ProductID into a global field in the Warehouses table. Then each warehouse record can sum the product counts through a relationship based on matching ProductID and WarehouseID, and you can view the results in a portal to Warehouses.

[T.J.]

So for each of the tables you describe. How do I populate them with (one unique record) without having to type that into these table.

[comment]

Well, maybe you could set a field's validation to 'Unique, Validate always', then import. I am not quite sure what situation you are describing - shouldn't you already have a list of products somewhere?

Note: I am bothered by the lack of ID's in your sample file. You should take care to have a serial ID in each table, and use these exclusively as matchfields for relationships.

[T.J.]

It seems to me if I were able to create a field in my detail table which was a concatenation of the warehouse and product fields (whse_prod) and create a new table called WhseProdSum with with two fields one being (whse_prod)and the other being the totals for the(whse_prod). Then set a relationship between the field whse_prod in the two tables I'd have what I need. Of course right now I can do this by exporting the information to excel and summarizing by whse_prod and then importing it back into FMP but that requires many steps and work and I just thought a script would be able to accomplish the same thing without moving the data out of the database.

So how do I get from this in the detail table

whse - prod - count

LA - Rock1 - 1

LA - Rock1 - 1

NY - Rock1 - 1

NY - Rock2 - 1

To this in the WhseProdSum table

whse - prod - total

LA - Rock1 - 2

NY - Rock1 - 1

NY - Rock2 - 1

[comment]

Yes, that is certainly possible - but then why wouldn't you do all this in Excel, from start to finish? There is no advantage to using a database for this, and a solution built this way will not be able to do much beyond this single task.

However, if you wish, you can do it this way: find the records you wish to summarize, sort them by the concatenation field, and export them grouped by the same field. The fields you need to export are product, warehouse, and sum_count (by the concatenating field). Then import the result to your summary table - it will be already summarized.