automatic create/duplicate new record

[funkybluelion]

Hello everybody,

i am working with filemaker pro 11 and wanted to ask your opinion to how to resolve a problem i'm stuck with.

"sorry for the bad english but it isn'tmy native language"

So, i have a first table A

It contains 7 fields:

Field 1: Productnr

Field 2: Productfamily

Field 3: Quantity

Field 4: Price/unit

Field 5: deleverynote nr

Field 6: Total Price = calc of field 3 x 4

Field 7: Invoicenr

and a second table B with two fields

Field 1: Productfamily

Field 2: Extra cost

Relation between two tables is field 2 (table A ) & field 1 (table B ) productfamily

What i want to get as a result is:

when the field productfamily in table a matches the field productfamily in table B

it has to make a new record in table a with the same values of the original record execpt for field 2 "productfamily" has to be empty & for field 4 (price/unit), there it has to fill in the value of field 2 from table B

Also when the quantity in field 3 table a is for example 2 it has to do the same as above but creating 2 new records where the qta for each record is set to 1

Is this possible to do with a script in filemaker or is an other approach better ?

i've attached a picture which ake the problem more clear

Thank You

[comment]

Why not add the extra to the existing line item in table A?

[funkybluelion]

first of all thank you for your response

indeed i can do that, that's the easy way.

i'll will try to explain why i want two new records:

Table A has a lot more fields than explained in my example, like the orderstatus, delivery date, etc but i think that doesn't matter.

i order products at work in an online ordersystem.

From that ordersystem i can extract a csv file with contains all the data of the orders

This csv file is 'converted' in excel with a macro which delete the coloms i don't need and changes some other things.

When done i update the table A with the data from the excel sheet, new orders become new records, existing records will be updated (orderstatus invoicenr etc)

when i come at work, i update my database, and i can print out a list of the products that i receive this day (sorting delivery date & nr gives me a nice list

of the products our supplier send me last night sorted by collinr)

Then i can mark these items as received, manco, etc

When after a while the products that we have ordered, are being 'invoiced' to us, they get updated (csv => excel => filemaker) and they get an invoicenr

After a few days, whe receive this invoice on paper , and the i can filter this invoicenr in my database & see if everyting is being invoiced correctly,

discounts, promotional discounts, qta etc. Very easy thing to do with filemaker

This isn't a problem for most of the products.

But there are some products (productfamily) that are revised parts and the old piece has to be returned.

so thats what table B is about. This table contains the productfamilys with the extra cost for the 'carcas' of the revised product.

It comes also on our suppliers invoice to us and he credits this "extra cost" back to us when we have returned the old part to our supplier.

This extra cost doesn't show up in the online orderprogram and thus also not in my database

So my invoices in my database doens't match the real invoice

I could use your solution, this is easy to do i know, i put an calc field in table a, which lookup the productfamily and gives the price, and counts this to the total of the selected invoicenr.

but i want to create a new table c where i can see the status of the revised product

so the extra records will be linked with table C ( which will be also updated

trough an csv file generated by our suppliers orderprogram )

to see if they are returned or not and if a credit was made by our supplier

Note: The csv file for table A and for table C don't have an unique match, that would be easy, but it isn't the case

and ok i know i would best add a id field for the records in all tables.

I've tried to make it more clear in the attached file

I hope you understand now why i want this

[comment]

I hope you understand now why i want this

Not really, I am afraid. It is very difficult to follow your explanation, esp. with cryptic names like 'table A' and 'table B'. I still think that if a certain product carries an extra cost, that is an attribute of the product - and should be reflected in the product's record. But as I said, I may be missing the point here.

In any case, your approach may be much more complicated that you think. It's not only a question of creating N child records (N being the quantity ordered, IIUC). That would be relatively easy. But if the quantity changes, then some records need to be deleted, or more added. Similarly if the product's family is changed, etc. Lot of moving parts in this clock, and plenty of opportunities for it to break.

[funkybluelion]

yes,

sorry it's difficult to explain,

but maybe i saw the light

If I put in table A (ORDERS FOLLOW UP ) an autocalc field so that i have a unique number for each record

and in table B ( REVISION PARTS COST & Follow UP) when received the part i put in this number

table C (COST OF REVISION PARTS) linked with table B with the field pfamily to get in table C the cost

Then it has to be possible i think to create a new table with the records from A & C, perform a search

on the invoicenumber an then is should have the solution

but for that i have to see how i can get records from 2 different tables in one or perform search in 2 tables at once

think the added pic makes more clear

if not i'll see if i can make a real example database to upload here with some real data

now i'm going to take a warm bath and relax a bit !

thank you for your time spended on my topic and till later

grtz from belgium

[comment]

I tend to look at things from a data modeling point-of-view. I see your "table A" has fields for Invoicenr, Productnr, Quantity and Price - that would make it a LineItems table in my eyes.

I cannot see what entity your "table C" represents, i.e. what is a record in this table? If you have ordered 5 units of Product X, the only good reason to create 5 records in another table is when each unit has something specific to record (for example a serial number).