Conditional Value List - How to get it to work?

[Adri Oosterwijk]

Hi all,

I have a three table situation.

1. Tablename "Begraafplaatsen"

2. Tablename "Graven"

3. Tablename "Reserveringen"

In "Begraafplaatsen" is a pKey field begraafplaatsen::id. In "Graven" is a fKey field id_begraafplaatsen. This fKey field exist in "Reserveringen as well. There is a one-to-many relationship between begraafplaatsen::id and graven::id_begraafplaatsen to show only the relatedvalues of a specific record in "begraafplaatsen". This works fine.

In Table "Graven" Is a field "Rij naam". The values in this field exist more than once because for every "Rij naam" more then one position is available.

The sum of the positions is stored in a number field called TELLING_GRAVEN.

In table "Reserveringen" a is field "AANTAL_GEBRUIKT". In this field is the sum of taken positions stored for every "RIj naam".

What I want to accomplish is:

Only show the values of "Rij naam" if

begraafplaatsen::id = graven::id_begraafplaatsen

and

reserveringen::AANTAL_GEBRUIKT < graven::TELLING_GRAVEN

Setting up my relationships this way it shows the wrong data.

I've tried it in several ways but I can't get it to work. Where did I go wrong?

BTW The data is in separate file (using external data source).

Any help is much, very much, appreciated.

HTH

Adri

[comment]

I followed you (I think) until here:

Reservations >- Cemeteries -< Graves

and lost you here:

In Table "Graven" Is a field "Rij naam". The values in this field exist more than once because for every "Rij naam" more then one position is available.

The sum of the positions is stored in a number field called TELLING_GRAVEN.

In table "Reserveringen" a is field "AANTAL_GEBRUIKT". In this field is the sum of taken positions stored for every "RIj naam".

What is "Rij naam" ( file name?) and what is "position"? Is there another table where "Rij naam" is unique? How can a sum of something be stored? We can't solve your problem without understanding what are you trying to accomplish and in what context.

[Adri Oosterwijk]

Hi Comment,

Thank you for helping me out.

I will try to explain.

Every cementary have areas, every area have sections and avery section have rows. "Rij naam" is a fieldname and is a aggregation of a areacode, sectioncode, and row code.

In the table "Rij naam" ( row name ) is a textfield filled wit the formula Section & " " & area &"-"& Row ( translated ). A position is a grave. In every row ( Rij naam ) there are one or more graves available. So the field "TELLING_GRAVEN" is the sum of the graves available for a given row. There is no table where "Rij naam" is unique. the sum of the graves ( positions) available wil be calculated by a script when defining the graves (positions). I want to deal with it using a script containing a find and loop statement. I didn't figured that out as well, at this point I'm enetering the values of TELLING_GRAVEN and AANTAL_GEBRUIKT manually.

The result must be: when I'm entering a new reservation I only want to see the available rows in the valuelist.

I hioe it this will make it more clear.

HTH

Adri

[comment]

the sum of the graves ( positions) available wil be calculated by a script when defining the graves (positions).

I don't think that's a good approach. The number of graves in a row is an attribute of the row - not of a grave. I think you should have a table of Rows (as well as Areas and Sections). Then the number of positions in a row can be calculated by counting the related records in the Graves table - and I presume you have some way of telling which positions are taken and which are occupied. Perhaps you also have prior reservations for the row - I'm afraid I lost you again at some point.

You will probably encounter a problem when defining a conditional value list based on availability, because it depends on unstored calculations. There are workarounds for that, or you could use another selection method. But you should organize your data properly before you get to that.

[Adri Oosterwijk]

Oops, I have to rework my db. Ok, as that is what it takes. When I make the choice to do it the way I did (one table for all separte pieces of data to create a gravenumber) I thought it was the right way. I was expecting some difficulties but I was sure to overcome them. Sadly, this is not the case.

I'm gonna rework this part of my solution and I hope that it will work.

I was aming to get a result of -> Count all positions in a row ( a row is unique for a cementary by relationship and ) and substract (is this the right term?) the number of reservations and graves taken by funerals as well to get the amount of graves available in this given row. When the user makes a reservation for a customer the solution only shows those rows when there are available graves. (available graves: Row says: 10 in total, taken by reservations: 4, taken by funerals: 3, = 3, available.

The difference between GRAVEN_TELLING and AANTAL_GEBRUIKT tells me the quantity of available positions (graves) in a given row. The relationship, however, I created was not working the way I expected.

Regarding your reply this will not work the way I expected.

I will split up the graves table in a sections, area and rows tabel. the graves table will only show the numbers (1, 2, 3, 4 etc) for a given row and the complete gravenumber.

I hope to get it solved this way.

Thanks foor your help so far.

Kind regards,

Adri

[comment]

I will split up the graves table in a sections, area and rows tabel.

No, that's not what I meant. All your graves should remain in one table. But you should also have a table for all your rows (and another one for all your sections, etc.) Then you can do the calculation you have described - but you will do it in the Rows table.

[Adri Oosterwijk]

Forgive my poor English, thats what I meant. I think.

The values in the graves table are the complete Numbers or codes for the graves. A complete gravenumber is a concatenation of a section, an area, a row and a number.

The Row number will be calculated (section & area & rownumber) in the rows table and the complete gravenumber (complete rownumber & gravenumber) in the graves table.

Example:

Section = SL

Area = 1

Row = 1

Rownumber = Sl 1-1

Gravenumber = 1, 2, 3

Complete gravenumer is:

Sl 1-1-1

Sl 1-1-2

Sl 1-1-3

Do we have the same in mind or didn't I understand you at all?

HTH

Adri

[comment]

The naming scheme is of secondary importance: every grave should have a unique GraveID that doesn't depend on anything else (an auto entered serial number in the Graves table). The same thing applies to rows, sections, areas and cemeteries. You should use only these IDs for relationships.

This way you can re-assign a section to another area and have all its rows and their graves follow. Or change your entire naming scheme without breaking any of your relationship. The actual name or code of any element can be anything you want, as long as it is unique within its parent domain; the IDs must be unique solution-wide.

[Adri Oosterwijk]

Understood. I will rework it.

I'm sure that I'm gonna solve it using your input.

Many thanks and I let you know.

Reagards,

adri