relationship criterias

[darno]

Hello everyone,

Ok, There's a little something I have not understood well. Here's the situation, hoping I describe it well:

Note that I can not use variables in scripts (I think) as I am using version 7... so I use a lot of global fields...

I have a global field somewhere, containing the date of a financial statement being currently edited (let's call it EFTable), which is copied, by calculation, in another global field of a table containing a list of edition fields (that can be called CodesTable). That last table is also linked to an occurence of a table containing the inserted values (let's call that one ValuesTable)

The ValuesTable has a field containing the corresponding Financial Statement's date which is included in the criterias of the relationship with CodesTable, (= CodesTable's global field I just mentionned)

Of course, It also contains a field with the corresponding CodesTable's code. Those two are also linked with the appropriate relationship criteria (=) in the same relationship

There are no other criterias for this relationship.

In a layout, I have a portal that shows every CodesTable "Label" textfield values, and a text object with a Merged field that looks like:

<>

Where "ValuesTable" is the one I just described and "Total" is contained in a ValuesTable's field, always properly filled through a script)

The problem is that no matter which Financial statement's year is contained in the both of the globals fields I mentionned at the beginning, the last inserted total value is displayed in the portal....

Is there a problem with defining relational criterias on global fields or what? I simply don't understand what I missed! I need help...

hoping it is clear enough, regards

Darno

Edited July 21, 2006 by Guest

[Søren Dyhr]

hoping it is clear enough, regards

To me it isn't, make a screendump of your relations graph ...and we might get somewhere. In a more generalized manner are globals simply not working as foreign keys - if that's what you're attempting to do, due to the lack of indexing they exhibit.

It seems like what you tries to do bear some resemblance to this:

http://www.filemakermagazine.com/modules.php?op=modload&name=News&file=article&sid=513

But note here that there's no globals in the keying.

Note that I can not use variables in scripts (I think) as I am using version 7... so I use a lot of global fields...

This is an entirely different matter, that only has to do with scripting ...do not expect that $variables can act as relational keys - if that's what you're on about. The problem you face is more likely an understanding of the way relations work.

I stumbeled over this:

which is copied, by calculation, in another global field of a table containing a list of edition fields

You say "a table" not "the table" - this doesn't fully embrace the use of several TO's of the table to make querries as well and not just structural dependency.

--sd

[darno]

Yeah, it does look like what is shown in the link you sent me... but not exactly. I didn't think of a multikey; it might work... I will think about it.

So, anyways, here's an image of what I am trying to do, and an image of a part of the database structure with nice graffitis meant to help (hope it does!). note that it is in french and made by a beginner (me!), so I'm not sure it will be easy to understand. In addition, I might be wrong all the way...

I just would like the portal to display values for the selected Financial Statement in a column (selected by the upper right 'select' buttons that do not work yet) AND for the two previous ones (a script would have to search and determine the appropriate dates I guess)

Does it help?

the problem I get:

I type a value (in this example: 566) while the 2000-01-01 Financial statement is selected (i.e. is in the global field) and the value displays allright where it should.

Then, When I select another financial statement (change the global field shown to the value of another financial statement: (2006-01-01 in the example) from the list you see), the value should not display, as the global value is not the same as the DateEF field of the table that contains the values... That's what I have got wrong... but I still don't understand!

I thought it would work this way, but it seems that it doesn't, so I'll have a look at these "multikey" methods of filtering portals. It's not exactly the same idea though, but it gives me a clue for another way of displaying... I will have to figure out how it works...

Maybe you have other links to more detailed explanations on 'multikey' or displaying filtered data in portals?

[Søren Dyhr]

I think see what you're after, but still isn't it overwhelming clear!

Look at my attachment, that borrows from this template in the reasoning:

http://www.filemakerpros.com/CALBASIC.zip

The utilized CF is this one:

http://www.briandunning.com/cf/422

--sd

test2.zip

[darno]

but still isn't it overwhelming clear

sorry, I did the best I could... I think it's not so bad for a french guy!

I'm having a look on what you sent... I think I will manage... (Where is it from? october is written with a "k"... looks weird. But it works. Does almost exactly what I was trying to do)

Thanks a lot, I don't know about any other web forum where I can get a good service like this one! Congratulations, and regards,

Darno

[Søren Dyhr]

Thanks for the kind words!

(Where is it from? october is written with a "k"... looks weird. But it works. Does almost exactly what I was trying to do)

As I recall it, does the majority of germanic languages use K and not C, the exception is English which is a hybrid between roman and germanic ...but in my case is it danish.

--sd

[darno]

Enough!!

I still can't figure it out!

you just built something very similar, and it works... mine should work the same way, as it is almost the same, yet it doesn't!

So here's what I propose: I send you my file, and you carefully follow my instructions:

- Open the file

- Go to the layout named "Saisie"

- In the upper right corner, hit the button 'select' on the 2000-01-01 line: That same date should now be displayed in section "3 - Sélectionner l'élément à saisir"

- Hit "État des résultats" in section "1 - Sélectionner la section de saisie"

- Hit "Produits" in section "2 - Sélectionner la sous-section de saisie"

- Hit any item in section "3 - Sélectionner l'élément à saisir"

- Write some numbers in the section "4 - Inscrire le(s) montant(s) à saisir pour:"; The total should appear beside the section title...

- Hit the green button with a check; the total should now appear in the list, in the appropriate line...

Now, we get to the point:

- In the upper right corner, hit the button 'select' on the 2006-01-01 line; this should update the section 3's display list... and the number you just entered should not display. Yet when I try it, the numbers still display!

Does your total still display? If yes, well, it shouldn't! as the relationship criteria is not valid anymore! what's wrong?

Man, I think I am going to give up soon!

Prototype.zip

[Søren Dyhr]

It'll have to wait until thursday, i have an appointment tomorrow!

But anyways - Yes I'll take a look at your file.

--sd

[darno]

Thanks a lot! Thursday is allright to me. I might figure it out before then though. If so, I'll let you know by leaving a message here.

I had an idea: I thought the problem might be because the value to display is an aggregate function (sum), whose context is not correct... but even when I display another field, it doesn't work better.

Edited July 25, 2006 by Guest

[Søren Dyhr]

No it's a different problem, the global that pull's one of the calculated legs of the relation are not goin to work, without flushing cache to disk or turning the field into a straight forward global and set it by script.

Similar should all the iconic gestures in the field status go straight into ValeursSaisies as stored values, instead of being calculated over the dependencies....

I then have some reservations on the use of repeating fields, they makes the scripting quite cluncky, ideally should they be pushed a relation further away, and there is nothing that justifies denormalization done with Valeur saisie!! The way of getting them from the interface's repeater to the new related table could be Import between tables in the same base with the splitting option turned on.

But to be honest would I instead just after choosing a line in selection 3 grap the unique ID of the of the line SaisieCODES_List, and make yet a relation in order to write straight down into the new table i suggests you to make, you can cut up the portals to give the horizontality you have at present.

I attatch the quick fix to you solution that makes the coloumn respond (and just it!) to your choises, while leaving it to you to make the rest of the fixes....

If you however don't get what I'm explaining with the normalization I urge you do, will I gladly help you with it.

--sd

PrototypeA1.fp7.zip

[darno]

Did you actually make it work simply by adding two "set field" lines to the "select" button script? I can't believe it!

[Søren Dyhr]

Ah yes almost! Although the field is bound not to be a calc'field dependant on a global - so it have changed it as well.

You need to get the fields in their correct places, and a lot of you globals serves no purpose as soon as you get rid of the repeaters!!!

--sd

[darno]

...or turning the field into a straight forward global and set it by script

ok, that's what you did, right? It was simple. You are a genius!

Similar should all the iconic gestures in the field status go straight into ValeursSaisies as stored values, instead of being calculated over the dependencies....

Of course, I understand. (these iconic gestures were placed there at first not to identify which entries were inserted and which weren't, but simply to identify which entries were optionnal, required or informationnal. They were not supposed to change as values are inserted. The little check trick was not planned. That's why I placed it in the SaisieCodes table.)

I then have some reservations on the use of repeating fields, they makes the scripting quite cluncky, ideally should they be pushed a relation further away, and there is nothing that justifies denormalization done with Valeur saisie!! The way of getting them from the interface's repeater to the new related table could be Import between tables in the same base with the splitting option turned on.

Ok, that's where you got me totally lost. What is that denormalization you are talking about? And splitting option? Creating a separate record for each entry would make it quite complicated to manage I think, as the position inside the repeating field matters... It seems to work this way. I see no problem...

But to be honest would I instead just after choosing a line in selection 3 grap the unique ID of the of the line SaisieCODES_List, and make yet a relation in order to write straight down into the new table i suggests you to make, you can cut up the portals to give the horizontality you have at present.

You mean enter the values inside the selection 3 portal instead of making a step 4? That was what I thought first, the only problem: I need to display the two previous financial states' values too, so multiple values would not fit. Still, I don't understand why and where you would place another table...

If you however don't get what I'm explaining with the normalization I urge you do, will I gladly help you with it.

The correction you brought was actually totally satisfying. As this prototype will only be used as an example to be developped under another database system (not filemaker), it doesn't have to be fully functionnal for now. But as you insist , you got my curiosity turned on!

[Søren Dyhr]

Creating a separate record for each entry would make it quite complicated to manage I think

No - you have at present chosen the toughest manageable! As I said would a lot of scripting and global fields dissapear.

You mean enter the values inside the selection 3 portal instead of making a step 4? That was what I thought first, the only problem: I need to display the two previous financial states' values too, so multiple values would not fit.

No step 4 remains, only ...it would be 10 individual records listed horizontally in a cut up portal. You have apparently already chosen or desided that the section 4 only deals with the leftmost coloumn??

If say you wish empties in the sequence of the 10 records, is it the same structure again with an inserted table with 10 placeholder records similar to what your large portal does.

We're today so fortunate, that tunneling not requires calc'fields any more - so if say a field is either one or four relations away we won't be bothered, just turn on allow creation of related records along the path.

--sd

[darno]

You have apparently already chosen or desided that the section 4 only deals with the leftmost coloumn??

No, why? The values are kept inside the repeating field and a summary field displays the total in the section 3 large portal...

No step 4 remains, only ...it would be 10 individual records listed horizontally in a cut up portal. If say you wish empties in the sequence of the 10 records, is it the same structure again with an inserted table with 10 placeholder records similar to what your large portal does.

ok, I got it. But my large portal displays vertically. How do you cut up a portal? Do I have to align 10 portals, each one displaying only one of the new table's records? That would mean create 10 occurences of that table in the database definitions, with 10 different relationship conditions? If you say it's simple, you probably have a better way of cutting up portals...

Why wouldn't this table have only one record and 10 fields? I could display them in a regular portal, with 10 horizontally aligned fields... Or simpler, why not use a repeating field and leave it just like this? To my beginner's eyes, it seems much simpler!

We're today so fortunate, that tunneling not requires calc'fields any more - so if say a field is either one or four relations away we won't be bothered, just turn on allow creation of related records along the path.

As I am new to filemaker (and slowly learning), I don't know about how it used to be...

Edited July 28, 2006 by Guest

[Søren Dyhr]

OK... Hold on I'll make you a template. The issue is that what you make scripted doesn't require any or rather hardly any at all!

--sd

darno.zip

Edited July 28, 2006 by Guest
made a template

[darno]

Wow, I am amazed by it's simplicity... I had never noticed the "initial row" portal checkbox before!

Thank you again

Darno