Make a temporary list and then save it as a permanent list

[A_Berlin]

Hello, I'm a new member of the site and I've been delighted to see how many great tips and samples there are here for Filemaker users, so first things first -- thanks to all of the members who have taken out time to make these contributions and have made this place such a stellar resource for learners like myself.

I'm making a database right now and I've hit the wall trying to figure out a way to implement one of the desired features, so I thought I'd run it by you all:

There is a layout which is a detailed view of an individual record, consisting of the fields in the primary table of the database, and in that detailed view I have also put a portal which displays a self-relationship, as a way of allowing the user to see the detailed view and a mini-list-view on the same layout.

Which records appear in the portal is determined by a binary switch in the primary table, i.e. if a certain field has a value of 0, the record isn't seen in the mini-list, and if it has a value of 1, it is (the 1/0 field is the relational key in the self-relationship). This lets me use a button to add or remove things from the mini-list.

What I would like to do is have the option to take everything that is in the mini-list, and save it as a permanent list in another part of the database. So, if the records in the main table are a series of different grocery items, and the user has been adding the ones that they need to buy at the store to the mini-list as they browse through them, they can "save" the shopping list for later perusal or re-use and give it a unique name (i.e. "shopping list for Thanksgiving dinner").

What I find tricky about it is that one foodstuff can be added to more than one shopping list, so deciding which field to make into a relational key that will allow the selected items to be displayed as records in another table eludes me. I can think of two (really) kludgy ways of solving the problem; one is to copy the contents of the fields of the records in the mini-list into a new table that is just about displaying the shopping lists (but this results in duplicated info, which is just bad), and one is to append all of the unique names of the lists that a single foodstuff is a member of to a field in the main record with some kind of delimiter to separate them, and then generate the shopping lists via a search inside this field for the unique name (it'll work OK, but it reduces the amount of useful things that the user can do with the list after it's been created IMO, such as giving it a stored sorting order that can be changed).

I feel like there is a good, relational way to do this that I'm missing. Can anyone share some wisdom about the right approach for this problem? Thanks very much in advance for any assistance.

[Søren Dyhr]

one is to copy the contents of the fields of the records in the mini-list into a new table that is just about displaying the shopping lists (but this results in duplicated info, which is just bad)

I'm glad you're aware of it yourself!

What I find tricky about it is that one foodstuff can be added to more than one shopping list

This is pure and simple many to many, and very much to our luck can such an issue be made without the jointable, by making a return delimited list as key, so the way to populate what's in your binary switched portal, is to bolster the keyfield with keys to destnation beneath the switch - the it requires the key values of the recieving records not collide with 0 and 1, it could be done by pre- or postfixing or just ensure that the values are far away from this boolean "spectrum"....

--sd

[Inky Phil]

Hello there and welcome to the forum.

I haven't a complete answer for you, merely a suggestion.

I think you are on the right lines with your second cludge but with a slight variation.

Presuming that each foodstuff has a unique ID what you might do is store the unique id for each of the items in the portal into one field in what is known as a multi line key.

This one field can be stored in a seperate table 'Shopping lists' which only has two fields - the multi line key and another field to identify which event this shopping list is relevant to.

This would allow you to treat shopping lists as a seperate item for sorting, viewing, reporting, updating etc without duplication of the data.

Pseudo Script to set multi line key would be:

Got to portal row (first)

Set field(yourmultilinekey);Foodstuff ID

Loop

Go to portal row next(exit after last)

Set field(yourmultilinekey);yourmultilinekey& :paragraph:&Foodstuff ID

endloop

This will give you a delimited field that can be used in a relationship to call up all those foodstuff's who's ID's it contains

HTH

Phil

[A_Berlin]

Hi Søren and Inky Phil,

Thanks very much for your responses -- apologies for taking so long to stop in and say thanks but there is some odd combination of a java applet and ad code on the fmforums.com site that crashes my browser (Camino) 100% of the time I visit it, so I've been fighting some negative reinforcement!

Anyway, both suggestions were great and really helped to shake out the cobwebs. I ended up following Søren's advice with the return-delimited list and one-to-many relationship. That worked very nicely, but it brought me to my next (trickier) question, which is:

What if I have an associated piece of info for each item in the list, for example a number which represents it's sorting order in the list (yes, we are coming to the edge of usefulness for my shopping list analogy... : ).

So, each item that is in a list also has a number associated with it for that list only, but the item may also be in another list and will have another number in that case. How can I get 1) all of the items stored under the shopping list name "foo" (this part already works) and 2) also get their numbers for that list only?

Is this a case for a jointable? This is new territory for me in terms of complexity but I also find it really interesting -- thanks again for taking the time to help me!

[A_Berlin]

Hiya,

OK, now after spending some time trying out different approaches and failing, I'm starting to wonder if what I'm trying to do is possible at all.

Here's the part that I can't quite make work again:

Records in the main database are foods, which can be added to a mini-list (a self-related portal view that is in the food detail view). The food items are keyed to the mini-list portal by using a binary switch (0 means not on the list, 1 means on the list).

When the user is satisfied with the mini-list, they can press a button and turn it into a permanent list and are given the opportunity to name the list. As per Søren's advice, this is another table connected to the main table by a one-to-many relationship (the food is the one, the lists are the many), keyed to the name the user enters for the list.

But the items on the mini-list also have a "list number" which can be changed, and which dictates the sorting order of the foods on the mini-list, and I want to preserve that number when I save the mini-list as a permanent list.

Is this possible? How do I get the number out of the mini-list and into the permanent list? Sorry if I'm just being thick here, but this seems somehow much harder than indexing the list names in the main table, because it is a secondary piece of information that is related to the item and the list (i.e. 'beets appears on the list "supermarket" and "farmer's market", and it's number 4 on "supermarket" and number 7 on "farmer's market"').

Can anyone help me understand this a little better?

Thanks very much.