creating a selection list using 2 portals

[gaz]

You know when you are exporting something from Filemaker, you have a list of fields on the left and an empty list on the right. You can then select fields to export by adding them to the right.

Well I want to have two lists that behaves in a very similar, not selecting fields to export, but in this case picking questions from a question database to go into line items of a quiz database.

The following picture is just something I found on the internet that is similar to what I want.

The three tables (databases) are:-

[color:green]questions table

(full of questions, each with unique I.D. number and each categorised into different categories)

[color:blue]quiz table

Each record in this table represents a different customised quiz

This is where the dual portal lists resides) The left portal displays questions indexed by their category via a drop down list that references the categories field in the Question table.

e.g. You set this drop down category selector to 'trees' and the list on the left shows only the questions (from the table [color:green]Questions), that have been categorised under 'trees'.

As a button (titled 'use') next to each question is clicked on the left, a script copies the ID number of that question across to the [color:red]Quiz_line_items portal where it gets added as a new entry.

[color:red]Quiz_line_items table

As the questions get copied across, the ID of the question from the [color:green]questions table gets added. A calculation field relates this value back to the questions table and fills a field in this table with a copy of that question (for the purposes of displaying in the portal in [color:blue]Quiz table).

Have I lost anyone yet

I've got the copying across bit working OK, but what I want to happen is when items from the left list are selected, they not only appear in the right list, but get hidden in the left list. (so there is no risk of duplicating a question). Also if they are ever removed from the selection list, they would reappear in the list on the left.

I can't think of a clever way of doing this with relationships etc..., I wonder if anyone here can help or has ever done anything similar.

Many Thanks

Gary.

[dreamingmind]

what if the master question list on the left didn't just show questions in a category, but instead showed questions in a category that ≠ question ids recorded in the set of question assigned to a quiz?

then, if you assigned a question to a quiz as you do, its id would then be in the quiz/question set and it would disappear on the left...unless you deleted it in the quiz list, then, violá! reappears on the left.

Clear?

Don

[gaz]

Thanks Dreamingmind,

I think that may be the answer, the problem is I'm viewing two separate database tables from a third, so I'm not sure how I would go about doing a multiple relationship between the three.

i.e. Quizes links to both Questions and Quiz_line_items

For the category drop down to work, I guess Quizes needs to link with the category field in "Questions". How can I filter this relationship further to then not show Question IDs that already appear in the table "Quiz line items" ?

I know you can have multiple criteria for a relationship between two tables, but how can I then make the same relationship look at a different table to restrict the results further (to question IDs that haven't already been chosen ?)

I'm sure the answer must be staring straight at me :(

[Fenton]

I didn't read all of the above (lazy), but I believe what you need is a return-separated list of the QuestionIDs that have been chosen, and you need it in Quizes. You can get this in FileMaker 8.5+ using an calculation field, using the List() function, and the relationship from Quizes to the Quiz line items.

_cQuestions_exist = List ( QuizToQuizLineItems::QuestionID )

Use that for the "≠" criteria in a relationship from Quiz to Questions.

[dreamingmind]

Fenton's suggestion of a value list seems good. In case you never realized it, linking to a field that has a value list in it will link to records that match any of the elements in the list.

So, you could link to records that match none of the elements in the list.

So:

You're in the quiz-master record,

it sees the questions assigned to it,

it has a field that gets set to the list of id's of the assigned questions,

and this field links to the master list of questions through a not=equal relationship (and your filter for question categories)

Don

[Fenton]

I am guilty of not reading the original post carefully. gaz was talking about a portal for the choices. Which is much easier. There is NO Value List anywhere. Just the calculation field, used in the relationship. I removed the offending sentence.

[gaz]

Dreamingmind, Fenton,

Thank you very much, both of you, for taking the time to read through my post and the help you've provided.

I've actually simplified the database to a single table now because the client wants this fairly quickly and I discovered another problem with the set up I had (I wanted to export the records from the second portal to an XML file to load into Flash. I successfully did that when the question table did the export direct, but the XML seemed to jumble up when exporting via a relationship).

I'm sure there's ways around all of this, but unfortunately I'm stuck for time now, so I'm having to keep it a bit more simple.

But I do appreciate both of your help and I'll use what you say to try to figure this selection list out in the future. The bit about relating to any values in a list (or in my case not relating to anything in the list) I didn't know about, so I'll look into that a bit more that'll be useful. Once again Many Thanks. :B

[Fenton]

When you export related data via XML, it is somewhat different from what you'd expect. Say you have a portal, with 2 fields. Because they are actually BOTH in the SAME parent record, they CANNOT be exported as 2 separate records. There is no export that would do it that way, as it makes no relational sense; it could not be seen as belonging to the parent record. If there are 3 rows in the portal is more like repetitions. Each field is exported by itself in a COL element (FMPXMLRESULT grammar).

Field 1 would be:

something in field 1, row 1

something in field 1, row 2

data in field 1, row 3

Field 2:

something in field 2, row 1

something in field 2, row 2

data in field 2, row 3

This makes sense relationally. This is something I wrote, in another post I believe, but I saved it for myself:

            

That will get the repetitive data in the multiple DATA elements for one related field. The trouble is that you often want to get more than one related field at once. A method I've used is to decide which is the "determining" field; ie., which field will have data; much like counting related records. Then get that field, and the other related fields. If you cannot be sure that one of the fields has data, then you can export an ID which you know does. You can use it in the . You don't have to output the value.



You have to go back "up" 2 levels (), then down again, to get the other related fields. Because the next field in the FileMaker portal is going to be in the next COL, but at the same position as the data you're getting. Notice I'm using a variable to get the position of the DATA element being currently processed, then using that to say which DATA to get.



Below I'm getting the phone type, then the number, of multiple related records:

(P.S. You can also use multiple for this. It's a little more abstract, so I don't use it as much; but perhaps I should.)

[gaz]

Thanks,

That should be useful. I spent so much time writing a custom parser in Flash for the XML that Filemaker created that I don't feel I can adapt it for a different format at the moment.

(it uses FMPDSORESULT, which I found easier to deal with at the moment). It formatted like this:-

<?xml version="1.0" encoding="UTF-8" ?>





0

quiz.fp7





	1

	What is 1+2 ?

	1

	3

	3.14159

	None of the above

	2

	





	2

	This is another question

	one answer

	another answer

	third answer

	fourth answer

	2

If you ever need to deal with Flash and Actionscript, this is the code I came up with to parse each record and the data within it.



The function parseFile basically uses two 'For' loops to work through each record node and then the fields within each node storing the data in the array data_content[n][c]



n=record node (parent)

c=child data node



The 'trace' command outputs the data to the screen as it gets converted.

//Filemaker XML Parser => Multi Dimensional Array



// Enter Filemaker XML filename here

xml_file_name="fmdata.xml";



// Data will be stored in a multi dimensional array called 'data_content'



data_content = new Array();



// The XML document will be loaded and known as my_new_xml



my_new_xml = new XML(); // Declare the name

my_new_xml.ignoreWhite = true;// Ignore white space on the document

my_new_xml.load(xml_file_name); // open document function returns 1 if OK

my_new_xml.onLoad = function(sucess) {

	if (sucess) {

		parseFile(my_new_xml);// pass document to my function 'parseFile'

	}

};



function parseFile(xmlDoc_xml) {



	// Outer loop to identify all the question nodes

	for (var n = 2; n



		// Inner loop to identify values of nodes

		data_content[n-2] = new Array(7);

		for (var c = 0; c



			data_content[n-2][c] = (xmlDoc_xml.firstChild.childNodes[n].childNodes[c].firstChild.nodeValue);

			trace("data_content [ "+Number(n-2)+","+Number©+" ] = "+data_content[n-2][c]);

		}

	}

}

You never know :B

[gaz]

I've played around with lists and used your suggestion of the list() calculation (which I had never used before). and the menu now works fine. :

I had to put a refresh window command on both the 'add' and 'remove' scripts before it would update, the data jumps from one portal to another. Fantastic thanks :B