Problems with field content that requires circular definition.

[tini]

The topic of this post sounds a bit unlogical... I know... ;-( Here is the case. I need to change content in a field without using scripts. Text Field A autoenters the first word from Text Filed B when a new record is created. After this i want to change the content in Text Filed B so everything remains except the first word (the word that now also is in Text Field A). Well... this is a circular definition! I get it to work all right with scripted buttons but in my sollution buttons is no option. If I could get a script run everytime a new record is created my problem would be solved... but that is impossible... isn´t it...? -So the only option is to make Calculation Fields that won´t end up with the message "circular definition". Any help out there... please... ?

PS. I´m using FM 6

[ThatOneGuy]

Hi tini:

I read over your dilemma, then drafted a nifty little calculation, only to notice just before adding my post that you're working with FM6. Bummer! While FM7/8 allows a degree of circularity in AutoEnter calculations, earlier versions choke on it.

Not all is lost, though. Think in terms of using a third field as either the result field or the source field for your calculation. I'm betting you can work it out, but if you need help with the calculation, just ring again.

That's about all I've got except I can't help thinking there's something awry with the structure when you're facing this "chicken and egg" problem. I could be off (many think so), but it kinda smells funny.

[tini]

Thank you ThatOneGuy for your reply. Yes you are right when noticing there's something awry with the structure... It´s really annoying. I fix this with no problems with ScriptMaker, Flash, Lingo and old good HyperCard, but the limitations in FM6 and the fact buttons are no option (since this is supposed to work with IWP) makes this tricky. - Yes, I have tried with both third and fourth and related fields but still end up with no succes. The main problem is that I can´t change the contense of the source field.

This i s basically the structure I´m using. A text fieldA has a Autoenter Calculation using MiddleWords(textfieldB, 1 ,1). Global TextFieldB is the source field with the text "1, 2, 3, 4, ...1000". When a new record is created the text fieldA = "1". So far so good. Now I need to prepare the source field so it is ready for the next creation of a new record. This meens I need to get rid of the first word ( "1,") The easiest way would be to make the source filed a calculation filed and to use MiddleWords(textfieldB, 2 ,1) This is where the circular definition hits. The same happens even if I use a third field as a temporary container. I do understand why.. but still I´m stuck!

[ThatOneGuy]

Hey tini!

Okay, this is probably a silly question, but I gotta ask. Are you using these numeric values as the source for Record IDs in your file?

Maybe they're for something more mundane (or less critical), like "ordinals" for your records, or such. Were you being literal when you used "1, 2, 3, 4, ...1000," or was that just an example instead of using "words?"

If we knew how/what they're used for, we may be able to come up with a solution.

[tini]

:-) well... "1, 2, 3, 4... 100" are actually the real words/values, but I´m not using them as RecordID:s. I need to generate unique serial numbers in a limited range (lets say 1 -100). Since the range is limited it also is important to be able to reuse deleted serial numbers. Let´s say record 1 in a database gets sn. 1, record 2 gets sn. 2 etc. If I delete record 1, sn. 1 ought to be free to use again. Here the source field comes in. In an other script I´m putting back the freed serial numbers at the end of the source filed. This meens that the source might look like this when in use:

"21, 22, 23... 100, 1, 2, 3"

[ThatOneGuy]

Ah, that sounds familiar. I checked the other topics you've posted and found related threads. You've been working on this for a while. I'll hand it to you; you're persistent.

I suppose you've run into a hurdle having to do with IWP. With Flash, Lingo and HyperCard in your tool belt, I assume you have some considerable skills. Yet, you've never really explained why you're having to do this ... the big picture thing, you know.

But, gee Tini, nothing comes to mind how you're going to reset those Numeric values (1) without scripting and (2) without upgrading to FM8 ... kinda like having both hands tied behind one's back. It doesn't help that FM6 doesn't allow global storage of calculations. (Seems there was a way, but I can't recall ... was it creating a Calculation, then changing it to a Number field?... It escapes me, and it may not have even been reliable.)

I don't know. If you could elaborate on this question, maybe we could take a different tack.

[tini]

The "big picture" is just a simple task. In our organisation (school)we need to reorganise and get an overwiev of three separate functions all with similar setup. Lets take one of them, copiers. The copiers are spread around in three different buildings and administrated by three different persons. Our teachers need to have access to several of these copiers since they work all around the place. Today they do have different ID:s and passwds. on every copier since it technically isn´t possible to administrate the copiers centrally. Thats why I want to build a sollution, accessible for all three of the copier administrators, where the ID:s and passwrds are given automatically so that they can do a reservation making sure the numbers are unique for each user. Limitation 1: The copiers can only handle 300-1000 unique given codes (we have a lot of people coming and going during one year). That´s why I need to make the list of serials loop, meaning that freed up ID:s can be reused. Limitation 2: FM6 and IWP as the only reasonable "interface" right now. Limitation 3: My own lack of skill :-(

In my earlier posts I tried to narrow it down to the main FM problem; I need either get a script running everytime a new record is created/deleted or get the fields make internal calculations so the source field changes.

Of course I can argue for an upgrade to FM 8, which I will eventually... but until then... well I don´t like to give up ;-).

Sorry for bothering you!

[tini]

Oh... and just to be clear.... the codes won´t be "1, 2, 3, 4...1000". I have a formula that generates more complicated serials :-)

[comment]

So basically you have a database of codes. I think you would find it much more convenient to administrate the database in a Filemaker FILE, instead of trying to build db functionality into a field. And, instead of constantly creating and deleting codes, you could simply ASSIGN them (by relationship). That way you would also retain the history of who had which code when.

[tini]

"Comments" suggestion sounds like a step forward. As I mentioned earlier i tried with relationships but I failed. Could you, Comment, elaborate a little. I don´t get it... How do you assign a given set of codes from one file to another and at the same time keep track which code is taken and which not?

[comment]

Option A:

File PEOPLE:

PersonID (pk)

Name

File CODES:

CodeID (pk)

Code

PersonID (fk)

Assigning a code is done by seting the PersonID field in CODES to the ID of the person.

Here you can have the next available code fetched by a second relationship based on

PEOPLE::constant1 = CODES::cAvailable

where cAvailable = IsEmpty (PersonID).

So to assign a code:

Set Field [ AvailableCodes::PersonID , PersonID ]

To unassign a code:

Set Field [ Codes::PersonID , "" ]

Sweet and simple - but no history is kept.

Option B:

File PEOPLE:

PersonID

Name

File CODES:

CodeID

Code

File ASSIGNMENTS:

AssignmentID (pk)

PersonID (fk)

CodeID (fk)

StartDate

EndDate

Assigning a code is done roughly like this:

- Find an available code - i.e. a code that does not have an active assigment (this could be done by a relationship in v.7 and higher);

- Create a new record in ASSIGNMENTS and set PersonID to the ID of the current person, CodeID to the ID of the first found code, and StartDate to the current date.

To unassign a code, set EndDate to current date.

[Søren Dyhr]

You can't use Set Field in pre fm7 filemaker IWP only CDML allows you to run scripts!

--sd

[comment]

Hmm. I know nothing about IWP, so I looked up the available script steps. I don't know how anything can be achieved with such a limited set. In worst case, one could resort to a very clunky solution:

1. Remember the current person;

2. Go to Related Record (from AvailableCodes);

3. Assign the code using a value list of ALL people (hopefully you still remember which person it was).

However, I wonder if something like this could work:

Fields:

File PEOPLE:

PersonID (pk)

Name

cUnAssigned (Unstored, Evaluate Always) = IsEmpty(Codes::PersonID)

File CODES:

CodeID (pk)

PersonID (fk)

Code

cAvailable = Case( IsEmpty( PersonID ) , 1 )

Relationships for PEOPLE:

Codes:

PEOPLE::PersonID = CODES::PersonID

Available Codes:

PEOPLE::cUnAssigned = CODES::cAvailable

MyID (self-join):

PEOPLE::PersonID = PEOPLE::PersonID

Value List "MyID" in PEOPLE:

Using values from field MyID::PersonID, only related values.

On People layout:

1. Place the field PersonID from Available Codes in a 1-row portal to AvailableCodes;

2. Place the field PersonID from Codes anywhere on the layout.

3. Format both fields as checkbox, using MyID value list.

Assigning a code is done by clicking the PersonID field in the portal ON. Unassigning is done by clicking the other PersonID field OFF.

[tini]

THANK YOU, Comment! I see where my thoughts where/are lacking. I will try this as soon as possible. This is probably a stupid question... but just so I´m not missing anything. What do you meen by (pk) and (fk)? (I have a feeling I ought to see it straight away, but I don´t... sorry...)

And a short comment to Soren (who helped me on the track earlier.. thanks... :-) ). Yes, you are right! SetField doesn´t work with FM6 and IWP. I managed to get everything else done with CDML and -script.PreFind running my scripts, except creating a new record and at the same time assign the next available code from a global text field. This of course is because -script.PreFind requires a search function whereas creating a new record with CDML requires sending ina form. Had the same problem with deleting a record, but found a way around it by mixing in FM href-tags.

[comment]

Don't thank me yet. When I said "I wonder if something like this could work", I meant just that. It's a mere speculation - I don't even have version 5/6. And as I said, I know nothing about web publishing. I am under the impression that if you're using CDML, you must be using CWP? So perhaps there are some more options available to you?

Anyway:

"pk" - primary key (usually an auto-entered serial number);

"fk" - foreign key

It's entirely non-essential here, as long as you understand why there's a field named PersonID in both files.