Value list - strange behaviour

[frinholp]

Hi all

My relationships are shown in the graph attached.

When I created a new tax in the ArtworkTax table, my drop-down list that is using a value list determind by field was not updating. I was using value from field (TaxID), using also second field (TaxName) and showing only values from second field (TaxName).

I hadn't left any TaxName fields blank. I swapped the value list to only use the 1st field (TaxID), now the drop-down list shows all records in ArtworkTax table. I changed the value list to show also second field; all records now show in the drop-down list. If I switch back to show only second field, only 1 record is shown even though I have three tax records in the table. BTW I'm using all values and not a range.

Anyone got any ideas on what is going on?

Thanks in advance

Lee

[Søren Dyhr]

Beyond you somewhat individual take on how the relations should go and why, do you need to explain why you haven't chosen to abide to:

http://fmforums.com/forum/showpost.php?post/316371/

What I see of your graph, are the autoenters only sparsingly used ... since there are "birds-feet" in both ends of each the graph-connection-ends. By this can't I by the eye establish which end is the "many" end and which is the "one" end ... perhaps the inner mechanincs are in the same kind of muddle?

--sd

[frinholp]

The id's i am using as my primary keys are auto-generated using the serials by calculation. Is this not acceptable?

Lee

[Søren Dyhr]

If this should be true, would your graph have "bars" and not "bird-feets" in the one end of the relationships!

--sd

[frinholp]

Thanks sd

I have now seen the error of my ways. My primary and foreign keys are all muddled.

Maybe I should move this thread to relationships rather than value lists.

I have been trying to use Keys/Id's that mean something in the 'real world' rather than just the inner workings of the database.

I am now going to just use a simple auto-generated serials as the primary keys that mean nothing to me apart from defining a relationship.

The problem I have now is that for auditing purposes I would like a code, say 'Artwork Code AWC00001, AW00002' etc. that is unique to each to each artwork. This code would mean something to me in the 'real world' and I would like this code to be sequential with no gaps. This is because I am going to create a database where records cannot be deleted, only archived once they have been created.

Would you say that the best way to go about this is to hold the last code used as a global variable, say $$lastArtworkCode, and then increment this for the next created record of the same type?

Thanks again

Lee

[Søren Dyhr]

It's not absolutely clear which kind of audit trailing you use, here is an example of a single field making it happen:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000600

--sd

[frinholp]

Thanks again sd

That has saved me a lot of searching at a later date. A brilliant solution to logging all changes. Superb!

What I'm trying to solve now is that every ArtworkID (not used as a key) eg ARW000001 to be created on record commit. This will be sequential with no gaps i.e if I have ten Artworks, they will have ID's ARW000001 through to ARW000010.

The problem I have been having is that when using the auto-generated serial numbers on record request the serial has been used. If I then click on a Cancel button where I have attached a script to delete the record, the calculated ArtworkID using the serial as part of the calculation is no longer continuous i.e if I have commited a record using serial no 6, then request a record, 7 has been allocated. When the script to cancel creating a new record has been triggered the serial count is still at 7 where I would like to reset it to 6.

I got around this by using the next serial value function and attached this to the cancel script, but I don't think this is the most secure way of solving this problem.

I am thinking of using a global variable to hold the last used ArtworkID that is incremented when a newly created record is comitted. This value will be auto-entered in the ArtworkID when a newly created record is committed.

Do you think this is the best way to go about this?

Thankyou for your time

Lee

[Søren Dyhr]

There is an old template called serialize category:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000121

But the issue is here that live reports as introduced by fm10 have made this ready for a departure to Oblivion.

http://www.kevinfrank.com/download/2009/report-in-browse-mode.zip

Finally could a train of similar be established via:

http://fmhelp.filemaker.com/fmphelp_10/en/html/func_ref3.33.10.html

...as unstored fields, provided the urge to display more than a handful of records could be resisted. But as such would summaries here counting and in particular the "running" feature be my best bet.

--sd