Portal through a many to many relationship

[Drew Sanderson]

I am having trouble with data not coming through a many to many relationship to a portal. If this possible to do?

Here is the scenerio:

Lets say there are 10 items for sale are on consignment to a dealer.

Information on the consignment is in one table and is assigned a line item.

5 of those items are bought by the dealer and invoiced leaving 5 still on consignment.

A few days later the dealer buys the remaining 5 and another invoice is created.

In the end I have:

1 line item

2 Join records

2 Invoices.

My setup:

There is a table (the join table) which keeps track of how many items from the line item went to an invoice.

There will be many line items.

There will be many invoices.

The join serves to connect these.

The portal on the invoice is intended to show information from the line items.

The join table consists of:

Primary Key - Not used for anything

Foreign Key Invoice which is the Primary Key from the invoice

Foreign Key Line Item which is the Primary Key from the line item.

Quantity Field - This is keeps track of how many of the items will be taken off consignment and added to the invoice.

I have all relationships with an =

I am allowing for creation of data on both sides of the relationships.

Any insights would be great!

Thanks,

Drew

Edited October 29, 2006 by Guest

[John Mark Osborne]

It's not really clear what your question is so I am going to refer you to an invoicing example you pick apart. Maybe this will help:

http://www.filemakerpros.com/joinprint.zip

[Drew Sanderson]

John Mark,

Thank you for your reply. I guess what I have written so far is plausible. What I think I failed to mention / make clear was my thoughts at this point to why it does not work is because some of my data is coming from several relationships away. Is there a limit of how far away related data can be / come from to show up in a portal?

I really appreciate your time!

Thank you,

Drew

[Drew Sanderson]

I think I may have found my problem. I created what I would describe as a relationship debugger that works similar to and with the script debugger. With the script debugger you keep stepping through your script until it breaks down. I decided to step though my relationships till they broke down.

I created a script called help.

I start at the begin point of the relatioship and executed the script with script degugger turned on.

The script works by using the Go to related record action. Mine stoped after two relationships when it still had about 3-4 more to go to get to the end. I got two forms of feedback. The layout showing when done running this is on the layout just before where the break down in the relationship occurs. Secondly, by watching the script debugger dialog I can see / confirm exactly where the problem occured.

In my case, I think another join table may solve the problem but I will have to test it out after I get home from work. The relationship strings across about 6 occurances at this point so that is entirely possible.

I will report back after I get a chance to implent another join table and rework my scripts to incorporate the change.

Thanks for the ear everyone. If anything that I have assumed here does not in fact work please let me know.

Thanks again,

Drew

[Drew Sanderson]

I have narrowed down the problem to what I think is a due to a circular relationship.

I need a way to connect the three different line item tables to the one invoice.

Right now I have to create an invoice table occurance in order to get them linked over to the invoice. This is a problem because I can either display information from the DP and DS line items or the the J line items but not all three on the same invoice. After I have this figured out how to get the three onto one invoice I will be adding one more type of line item.

The set up on the invoice is that I have three portals. One for each product type which are from the three line item table.

Here is my question.... How do I get all three to relate to one invoice table with this (what I think is) a circular relationship?

I have tried many combinations and reworking of relationships.

I think the problem is that they all relate to the memo ( which is a consigment type of invoice ) and this creates the circular relationship.

Thanks for any input!

Frustrated... uhh I mean Drew_S

[John Mark Osborne]

It's impossible to create a circular reference in FileMaker 7 or later. I looked at your relationship diagram and all you have to do is relate the Invoice TO to the three line item TOs. However, your relationship diagram is difficult to understand. In a typical invoicing solution, you have Invoices related to line items and line items related to Products. I see a Quantity to Invoice TO between Invoices and Line Items and then your other line items related through a Memo table. I've read through the thread again and I don't understand what you are trying to accomplish. I fear this may be too complex to handle through message boards and may require you to talk to someone over the phone.

BTW, you might want to consider storing all the line items values in a single table and differentiating them by a category field.

[Drew Sanderson]

I originally had the three line items directly related to the invoice as you suggested. This worked perfect. Recently it was decided (not by me and contrary to original design) that some items (those on J_memo_line_items) may be sold in quantity unlike those in the other two (DP and DS) that are unique. It was this request that brought about the Quantity to Invoice Table and my current problem.

Since filemaker prevents circular references the following happens. When I try to relate DP line items to the invoice a second table occurrence ( Invoice 2 ) is created. I can relate DS line items to this second table occurrence. The problem with this is that in layout setup for invoice I have to choose either Invoice or Invoice 2 as the table the layout it based on. Due to this it will display those items on the J table and not the ones from DS and DP (memo line items) or visa versa if I base the layout on Invoice 2. What I ( think I )need is a way to relate all three to just the Invoice table and get rid of the other table occurrences ( Invoice 2). I however cannot figure out a way to do this without have the problem of multiple table occurrences preventing the relationship needed.

The reason for the Quantity to Invoice Table was that quantity from the line items assigned to the memo may be invoiced onto multiple invoices. Here is a scenario to better explain:

The dealer calls and says I would like to try and sell 20 of the same product. When this happens:

A new memo is created with all pertinent dealer information assigned.

A new J_Memo_Line_Item record is created to record which product went to which memo and in what quantity.

A week later after receiving the goods, the dealer calls and says that he has sold 5 of the 20. At this point the following is created:

A new invoice record is created.

A new Quantity to Invoice record is created to relate the one line item to the invoice.

Three days later the dealer calls and says that he has sold five more. The following now happens:

A new invoice record is created.

A second Quantity to Invoice record is created relating the same memo line item to the new invoice.

Two weeks later the dealer decides to return the remaining items that did not sell. At this point it is entered to the line item that 10 have been returned and 10 have been invoiced.

In the end there is:

On Product record

One Line Item record

One Memo record

Two Quantity to Invoice records

Two Invoice records

I hope this makes my situation a little more clear. When I first started with this problem I had no idea why it was happening. At least now I know where and why the relationship is not working, now I just have to figure out the how to fix it.

Thank you for all your help.

Sincerely,

Drew

Edited November 2, 2006 by Guest

[Drew Sanderson]

I figured out how to make the relationship work.

Since all three line items relate to the memo correctly and each item must go to a memo before to an invoice I just joined from the memo to the invoice with the quantity to invoice join table in the middle. It seems to be working great. All I have to do now is rework a few scripts and the solution should work. If not... I am sure I will be back

I think taking the time to write out the problem in a manner that would (hopefully) make sense on the forum, along with the process of synthesizing my thoughts really helped to bring out the solution to my problem.

John Mark, your comments really helped me to confirm some of my thought and get me thinking in the right direction. I really appreciate it! Also, I would like to publicly thank you for all the help you have given me to get this far. You may be wondering what I mean. I purchased the three DVD set you put out with VTC. They are likely the best instructional DVDs I have ever used. Without them I would still be hobbling along on many concepts. You cover so much material that it did not all sink in at first but the more I work with filemaker, the more valuable they become as I understand more and more of some of the more complex topics. That said though... you also laid out great foundational building blocks. It is this foundation that I believe helped to prevent me from making many design problems. This problem I just had was likely the first one I have had and it was a fairly complex one at that. Anyway, thanks for the help with this post and for the DVDs. BTW... I am looking forward to the VTC training on Filemaker Server. I hope to see it soon.

Thanks again,

Drew

Edited November 2, 2006 by Guest

[John Mark Osborne]

Wow! Thanks for your kind words. Now, if that testimonial doesn't sell more videos, I don't know what will :P