records

[ausbob]

can anyone help. How do I create a number of records in one layout based on the number of records found in another layout.

EG..Layout 1 looks up layout 2 field X - Field X is related to 3 records in Layout 3.

Layout 1 creates 3 records based on Layout 3.

I hope this makes some sort of sense

[LaRetta]

Sorry, no, it doesn't. Records belong to table occurrences - not layouts. Are there any relationships? What tables are those layouts based upon?

And please use real names and not abstract. We need to know what you are doing and abstract tells us nothing meaningful. So what is the purpose of your solution and what is the purpose of why you want to duplicate some of those records? :wink2:

[ausbob]

OK

We have Tables(sorry not Layout) Account, Stock and Item number.

A Stock product has up to three item numbers.

The account has to automatically create up to 3 records based on the 1,2 or 3 item numbers the stock product has. (It may have 1,2 or 3 item numbers).

I am sorry for my description. I am an optometrist but have been using Filemaker for about 10 years but no actual training. Have only recently started using Filemaker 8.5 after 5 year hiatus

[LaRetta]

No problem.

So let's translate:

[color:blue]EG..Layout 1 looks up layout 2 field X - Field X is related to 3 records in Layout 3.

Layout 1 creates 3 records based on Layout 3.

Account, Stock and Item number.

A Stock product has up to three item numbers.

The account has to automatically create up to 3 records based on the 1,2 or 3 item numbers the stock product has. (It may have 1,2 or 3 item numbers).

So ... You have an Account record (layout 1 looks up layout 2 field x). Layout 1 is based upon Account table? Layout 2 is Stock? So an Account is related to Stock that they manufacture? And you want to perform a find for a certain stock item and then duplicate it's Items (and the stock item may have 1-3 items)?

I'm still lost. Do you want to create a new stock item and duplicate a 'similar' stock item (with it's associated items)? We still need a bit more because if we create new records of the items, which stock item will they attach to?

[ausbob]

unfortunately I have to go, I will sit down and repost when I can explain this better. Thank you for your help.

[ausbob]

I'll try again, What you have written in blue is actually correct.

I dont need to create any new records other then in the accounts table. The stock and item numbers remain as is linked by a STOCKID. The accounts table is where the totals are calculated based on the three item numbers and their cost.

[LaRetta]

What you have written in blue is actually correct.

The portion in blue is correct because I was indicating what you said word for word.

You say Layout 1 creates 3 records based on Layout 3 but then say you dont need to create any new records other then in the accounts table. I cannot see how Accounts and Stock are related and WHY you are creating records in Accounts anyway. Which table is layout 1? Which table is layout 3? Who is on first? I've asked questions about which tables are which layouts and you haven't answered my questions and I remain as blind as before.

I believe you have an incorrect structure but it might be because I can't envision the PURPOSE of this at all. Are you attempting to store the static totals of the items in the Account table? Maybe someone else can help you, sorry, or maybe you can post your file for us to look at. But, even seeing your file, I think we'd need to understand the purpose of what you're trying to do. I truly wish I could help you.

LaRetta

[ausbob]

I will start again.

I am an optometrist

Frames have 1 item number e.g 110

Lenses that we sell have up to three [color:red]Item numbers

e.g 212 ( lens type - Single vision, bifocal etc)

512 ( lens material - plastic, glass)

652 ( lens coatings)

I create a job order which the user puts in a stockID number for a frame FStockID, and a stockID number for lenses LStockID. Look up fields then fill the rest of the job order up linked to a stock table via the StockID.

When the patient comes to pay for the job I start a script which looks up the job code. From here it creates a new record in an accounts table. This is linked to a receipt table by the AccID (1 is to many)

From here it looks up fstockID in the job order table and transfers into a new record in the reciept table the[color:red]item number and cost of the item.

A portal in the Accounts layout simply shows the related receipt fields which may end up as

E.G

110 Frame $100.00

Each line is a record from reciept

The item number for frame (110) is easy to add as all frames have the one item number therefore I paste in the FStockID for the frame and use look up fields for item number and cost. For lenses however there are up to three item numbers and therefore up to three records need to be created in the receipt table.

The portal in the Accounts Layout should look like

110 Frame $200.00

212 Single vision lens $50.00

512 Plastic $20.00

652 hardcoat $20.00

[color:red]I have just read what I wrote and I think I have confused myself. If you can help great if not I will just reasearch more.

One thing you may be able to help with is with regards to indexing fields is there a rule of thumb on which fields should be indexed.

Thanks again

( If you have any eye questions I could probably answer that.)

[Søren Dyhr]

Paid or not paid, is just an attribute of the invoicetable, which can be seen from the jointable, enough to make a sub-summary report. Summarized will it give 2 sections of sub-summaries, in option/ordered and Sold.

My point is that accounts table is synonymous with the join table, and is filled with data already when the order is given ...honestly nothing which need to get scripted, just a boolean change to a single field in the invoice/order table.

--sd

[LaRetta]

When the patient comes to pay for the job I start a script which looks up the job code. From here it creates a new record in an accounts table.

As said, why are you creating these records again? They exist in the Job Order. Is it because the Job Order is to the people who make the glasses? And you want to create an Invoice for the Patient? Script can flag each item individually as received and paid or the entire Job Order as received and paid. If you can have many payments made on one Job Order, this might make sense to use an Account table. If this is your theory then that is fine. But you STILL won't need to again insert the Items into the Account table. You would just relate them to provide the items received/sold (using a join) and then type the check# and date paid right within the Account record. But you haven't even indicated that you have a Patients table!

Again, I question your structure. Now is the time to change it if it is incorrect. Now ... you can go off on your own and change it and AGAIN have an incorrect structure. Understanding good relational theory is why we get paid the big bucks (just like you get paid the big bucks for your expertise). Or you can post your file and let us get you straight. The choice is yours. But we cannot continue to guess and try to explain something more complex (as this is) simply through words on a post especially to someone who doesn't understand the logic and terminology. Surely that makes sense to you.

LaRetta

[Søren Dyhr]

Account, Stock and Item number

Just by you naming of the tables is something not quite as it should, it's very difficult to circumvent a structure like this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000717

...it's fledged only just, to make the wings bear the bird. Usually is the use of different tables more elaborate, of course dependent on the actual deployment. Laretta and I could be wrong but the transaction model isn't what you're after but instead something like this:

http://www.jonathanstark.com/downloads/Inventory.fp7.zip

--sd

[ausbob]

Thanks again, I have attached a copy of what I have got so far. (Laugh if you must but I enjoy trying) If this is to complicated feel free to ignore as I don't want to take up all your time. I am sure my structure is incorrect, I dont pretend to be a programmer. The advantage of writing my own software is I can alter things on the spot when my staff require it.

At this point the item numbers are within the stock table but in different fields however I was thinking that I would need to link the stock to a seperate table for the item numbers.

Optom.zip

[Søren Dyhr]

There is absolutely nothing to laug about here, it's a serious problem, you seem to have ignored that the RG isn't a ERD, no-one perhaps not even you can find his/her way in this web of relations, thrown in arbitrarily. There is likely - as you more or less admitted, to be both a data structure as well as the obvious presentation design issue the image reveals!

It might come as quite a revalation, but the graphing can actually be organized? If you wan't us to look at your stuff should you perhaps even subscripe to one of the more recognized ones; the Anchor Bouy!

Watch this video:

http://www.filemakermagazine.com/videos/solution-design-models-how-the-graph-presents-your-data.html

I can alter things on the spot when my staff require it

Marvelous, If you then prentend me as part of your staff - wouldn't you then do me the honor of untangling the graph into a Anchor Bouy'ish one, and perhaps while at it, give Harris paper here a closer scrutinization:

http://www.digfm.org/ref/FM7_key_concepts.pdf

--sd

[ausbob]

Looking at that jpeg certainly puts things in perspective. I have read the article and will attempt to tidy things up.This may take awhile. (dont hold your breath).

At least you could always use this as an example of what not to do.

Thank You

[Søren Dyhr]

Could you under private message, let me know when it's ready?

--sd

[ausbob]

I certainly will