Relationships with Repeating Fields

[FM_SouthAfrica]

Yes I know, I chose that heading specifically because I knew it would get moans and groans from the community! I have a problem in that in my STOCK table we often have items (computers) as a single line item including a unique id number as well as the individual machines actual serial number. We often sell multiple computers (eg 3) to a single customer which is then allocated to a single Invoice (there we have the [color:purple]Invoice & [color:purple]Invoice Table combination). Therefore I need to connect these MULTIPLE records from [color:purple]Stock to a SINGLE record in the [color:purple]Invoice Table.

I include files so that it can be better understood (modified version of sample solution from d.wieland of www.appointment10.com).

What I am missing is

One

You will notice as you move stock back and forth from [color:purple]Stock to [color:purple]Invoices Table the avStock_KEY field is populate but the UniqueId field will not place the repeating data at the top. Why do I care? I include this UniqueId filed in the [color:purple]Invoices Table PORTAL in [color:purple]Invoices so that you can visually SEE the number of items allocated to a single row.

(we don't allocate more than 3 items when serial numbers are involved, but we often have many [color:purple][stock] to one [color:purple][invoices Table] allocations.)

Two

I want to be able to compare the total number of units taken from [color:purple]Stock and allocated/related to a SINGLE [color:purple]Invoice Table item.

(therefore by number of items I can always verify the total items allocated to the [color:purple]Invoice vs Total related items in [color:purple]Stock)

Three

I want to be able to 'allocate' stock to the [color:purple]Invoice (really meaning the [color:purple]Invoice Table) via two portals in [color:purple]Invoice.

(one reflecting the [color:purple]Invoice Table Items, one reflecting the Stock items)

I have this working when DIRECTLY in the [color:purple]Invoices Table but can not seem to get it to work from [color:purple]Invoices . . .

[FM_SouthAfrica]

p.s. here are the sample files . . .

InvoicesStock.zip

[mr_vodka]

Have you considered using a portal to display the line items grouped together by a tag?

You have a bunch of product items that are grouped together as what your system considers a line correct? If so, you should be able to use a self join based on the groupID to display all the items for that group.

A portal showing the group info and stats against your inventory levels could be done for each group as well as the overall invoice.

It seems as though you are going through a lot of extra trouble to get your result.

Perhaps this thread can help.

http://www.fmforums.com/forum/showtopic.php?tid/157638

[FM_SouthAfrica]

Yes. I have done just such a thing in my Cash Book, e.g. there is a single parent item for the month end credit card payment, then this is broken down to children being the individual card transactions, then these can be further broken down if items in a transaction are for different purposes e.g. stationery, cleaning etc.

The reason why I'm trying to avoid it here is because sometime stock gets misallocated to the wrong invoice and has to be unallocated or returned to stock. Let me walk you through the process . . .

There are [color:red]THREE stages to allocating stock to an Invoice

[a] A sales person [color:red]RESERVES stock in the [color:purple]Stock. Once he is certain it is a done sale he moves his reserved stock to [color:red]BOOKING. This then requires a person in stock to pick the respective items and pack them together with a picking slip from [color:purple]Stock. They then [color:red]ALLOCATE the stock to the respective invoice by inserting the invoice number on each respective stock record.

This last step is where the problem comes in . . . Firstly most stock that is received is usually [color:green]split into individual line items. As described earlier if we order 3 computers the [color:purple]Order will have just one row indicating a quantity of 3 items. When we complete the [color:purple]Goods Received Invoice it will also only show one line item with a quantity of three 3 items. However when we post this single row across to Stock the stock system will ask if you want to [color:green]split the quantity of three items into seperate individual rows.

This [color:green]split is done so that we have the freedom to allocate a serial number to each item and can further allocate these 3 individual items to three separate invoices if we want to.

Now it does happen that we may end up selling all 3 computers to one customer and therefore all 3 are grouped or allocated to one invoice. Because the [color:red]ALLOCATE process is a simple one of adding the invoice number to each item in stock that is required, there is already a relationship between the [color:purple]Invoice and the stock items. This means I can have a portal from my [color:purple]Invoice to my [color:purple]Invoice Table and my [color:purple]Stock. From this I can visually see or compare the allocated stock vs the invoice's actual line items.

So this many to one relationship and the ability to undo a stock allocation is the reason why I don't want to split records again.

Finally whilst [color:blue]Fenton's example is useful http://www.fmforums.com/forum/showpost.php?post/157908/ I have to have the records in the [color:purple]Invoices Table be seperate from the [color:purple]Stock because sometimes not all the line items in the [color:purple]Invoices Table have related or matching records in [color:purple]Stock. By example we sometimes have to enter additional comments in the Invoice (meaning [color:purple]Invoice Table) like the details of the customer when the Invoice is a finance deal and the invoice is made out to the bank with the customers address details included in the invoice (meaning [color:purple]Invoice Table)

You will notice the diagram above has been amended to give a better idea of what I want to achieve.

[Søren Dyhr]

When mixing sets of items within items in single units, is there a better method:

http://jonathanstark.com/recursive_data_structures.php

http://www.jonathanstark.com/downloads.php#inventory_example

--sd

[FM_SouthAfrica]

lol, Holy Crap . . . thanks Søren. Lots to digest there, need to get to work 08h05, appreciate the input. Will report back later.