Conditional Value List for a Portal

[natursalus]

Hello,

I have two tables:

Process

Document

Process table fields:

__pkProcessID

ProcessName

ProcessStepName

Document table fields:

__pkDocument ID

DocumentName

...

What I want to achieve is to have a portal on the Document layout where to put the processes and processes steps where a document is needed.

For instance, Document 1 could be needed at:

Process 1 step 1

Process 1 step 5

Process 3 step 4

Process 10 step, 2

Process 10 step 5

...

I surfed the web in search of help and I got an example that describes how to setp up conditional value lists both for fields and for portals.

No issue on the fields part. However, for the portals you get the same portal field values for all the records what doesn't make any sense.

Since I couldn't upload the file in the post, the link to that example file is: http://www.digitalfusion.co.nz/weetbicks/conditional-value-lists-pt-2--building-for-use-in-portals/25/

Any help on what is wrong with this file or any suggestions as to how to setup a conditional value list for a portal is much appreciated.

[comment]

I don't think that example fits your situation. Can it be assumed that, using your example, "Process 1 step 5" needs "Document 1" and no other document?

[natursalus]

Hello Comment,

I don't think that example fits your situation.

Okay, if so please guide me.

Can it be assumed that, using your example, "Process 1 step 5" needs "Document 1" and no other document?

No

Let me elaborate a little more.

A Process = a task

Process Step = a step in a task

In an Organization there are several processes. For example: Purchasing, Trainig, Premises Cleaning, Raw Materials Reception, Raw Materials Storage, etc

Each process can be divided in several steps.

The Documents describe who does, what, when and how.

Some Documents are process specific and are associated to just one Process.

Some Documents are general and are used in many different processes in many steps.

So all sort of combinations are possible in terms of the documents that are associated to a particular process as well as to the different process steps.

I think that I need a very flexible setup that would allow me to assign any document or documents to any proceses and processes steps.

Back to my example, each Document record can be associated to many different processes and also to different steps of any process.

Don't know if it makes sense.

Thanks

[comment]

What you describe is a many-to-many relationship between processes (or steps) and documents: one document can have many steps, and one step can have many documents. This requires an additional table joining the two, with fields for:

• DocumentID

• ProcessID

See a basic example here:

http://www.fmforums.com/forum/showpost.php?post/246136/

Your situation is further complicated by the process/step hierarchy. Perhaps it would be best to restructure your "Process" table to:

• ProcessID (auto-entered serial number)

• ParentProcessID (fill out when the record is a step)

• Name

This would allow you to join a document to any record in the table - regardless of its rank - using a single join table.

[natursalus]

What you describe is a many-to-many relationship between processes (or steps) and documents: one document can have many steps, and one step can have many documents. This requires an additional table joining the two, with fields for:

• DocumentID

• ProcessID

You are absolutely right, I missed this important point

Your situation is further complicated by the process/step hierarchy. Perhaps it would be best to restructure your "Process" table to:

• ProcessID (auto-entered serial number)

• ParentProcessID (fill out when the record is a step)

• Name

Just to make sure that I got it right.

The structure of my Process table is:

__pkProcessID (auto-entered serial number)

ParentProcessID

ProcessName

ProcessStepName

ParentProcessID (fill out when the record is a step)

The ParentProcessID is the number that I must enter to identify the step of any given process?

This would allow you to join a document to any record in the table - regardless of its rank - using a single join table.

So in the Document layout I put a Portal to the ProcessDocument joint table with two fields:

ProcessDocument::ProcessName

Process::ProcessStepName

To choose the Process I use the Process value list, where I associate the __pkProcessID to the ProcessName

How do I do to create a conditional value list from which to choose the Process Step?

Thanks for your help

[comment]

Just to make sure that I got it right.

The structure of my Process table is:

__pkProcessID (auto-entered serial number)

ParentProcessID

ProcessName

ProcessStepName

No. There is only one name. It is either a process name or a step name, depending on the type of record. The type of record is given by the ParentProcessID being empty or not.

The ParentProcessID is the number that I must enter to identify the step of any given process?

No. The ParentProcessID is a number that identifies the process of which the current step is a child of. Perhaps this example might help:

ID	Name	  ParentID	

1	America

2	Europe

3	Asia

4	Canada	  1

5	USA	  1

6	France	  2

7	China	  3

The parent name can be seen through a self-join relationship matching ID to ParentID. Note that this is not exactly a simple undertaking.