Relationship to Range of Values of a Field

[keshalyi]

Hi all, I've got a stinker, that I want to get worked out before I roll out my big DB and cut off my options for deep level changes...

Here we go:

I've got a database that stores classified ads, and a linked database that stores instances where those ads are to be printed (basically, combinations of date and location). Now, in the Ads database, I need to show a portal with every publication that they've scheduled any dates for. There's my problem. Every publication can from anywhere between 1 and infinite dates for a specific ad, and no 1 date is shared by every publication, so how can I make a portal that links to just the first instance of each publication for a specific ad?

Any thoughts?

[LaRetta]

Hi Keshalyi

I'd like to know your existing relationship before responding. For instance, are the dBs currently related on AdID? And you want to sort your portal by date? By 'range of values' do you mean date? Does Ads have a date?

You indicate that you want a portal that shows all related pubs but then say you only want the first instance? I'm a bit unclear. But if you only want to display, or as you say, "links to just the first instance of each related publication for a specific ad," you might consider that you don't even need a portal (or you could use a portal, select only one line and remove the 'show vertical scroll bar' in portal format. If you want to display only the first instance, do you mean the first related record entered in your pubs dB? If so, just place your fields from pubs on your Ads layout -- the *first instance* will always be the first related Pub recorded added (if you remove the relationship option to sort).

If you could explain what you mean by *first instance* we might be able to help you further, but the *first instance* (if Pubs is unsorted) will always be the first related Pubs record added. If you mean the *first (or oldest) date and data in Pubs is added out of date sequence, you might consider something else, such as sorting your Pubs by date ascending upon opening it! It might be the simplest way. There are other options also.

Can you be a bit more specific on which Pubs records should be displayed? Or is there a reason you need to relate them further according to date?

LaRetta

[keshalyi]

I apologise, I wasn't very descriptive was I? You get in the middle of it, and you forget how incomprehensible it must look from the outside!

Let me clarify....

Database one is linked to database two by ad number. It is a one to many relationship - one ad will have many instances. The instances are, for the purposes of the illustration, intended to store three things in each record: An ad number, a date, and a publication. What I'm setting up is a database that lets them sell their ads into a number of different weekly newspapers in the area. So, if Ad A is printing in Pub A on 3/23, and 3/30, and Pub B on 3/16, 3/23, and 3/30, you end up with the following records:

Ads DB:

Ad A

Instance DB:

Instance - Ad A, Pub A, 3/23

Instance - Ad A, Pub A, 3/30

Instance - Ad A, Pub B, 3/16

Instance - Ad A, Pub B, 3/23

Instance - Ad A, Pub B, 3/30

Does that make more sense?

Now, I need a portal in the Ads DB that lists the range of publications that have instances for the displayed Ad. Thus, for the example above, the portal would need to have Pub A and Pub B, that's all. So, it doesn't really matter if its the first instance of Pub A for Ad A in the instance table, I just need to show precisely one instance for each pub.

[LaRetta]

Hi keshalyi,

Well, you've stumped me! You would need to relate via calc which identifies only one instance of each Pub and only the first date. I'd have to chew on this quite a while I wouldn't know what calc to write to identify this unique set! Good luck to you though!

I look forward to seeing how the experts resolve this for you and I'm sorry I don't know the answer.

LaRetta

[keshalyi]

*grin* Well, you're kind to try, thank you.

If anyone else has any thoughts, I'm buggered. Thanks.

[Ugo DI LUCA]

This surely is possible...but you will need a calc and a lookup to index this calculation.

You have to create a field in Add file (call it t_matchallinstances)

For this example, say t_matchallinstances = "All Adds" (could also be a global)

Now, in the instance file, use a calc to flag the last (or first) instance for any Add :

Seljoin on Add ----> Intances::Add_ID:: Intances::Add_ID

c_flaglast = Case(Add_ID= Max(SeljoinonAdd), "All Adds", "").

You will therefore have a "All Adds" that will match the "All Adds" in Adds

As this calculation use a selfjoin, you will need to index this unindexable calc...

Using Ray's (CobaltSky) technique to index this calc :

create a c_lookup field = (Mod Time * 0) + Serial (any serial # of your instance file)

and a Selfjoin (sjlookup) using c_lookup at left and Serial at right

Create t_matchallinstances in Instances file and set it to be a lookup from c_flaglast using this sjlookup

Use a new relationship Instances:t_matchallinstances::Adds:t_matchallinstances and displays the Add_Id's in the portal.

Last but important. When in File Add, you will have to script a relookup of Sjloookup to update your Instance file

But you surely have your portal showing one Add per row.

[Ugo DI LUCA]

Test file here.You would adapt the calc to your exact needs

[keshalyi]

Well... I was trying to get around the lookup idea, I do appreciate your input, but the list of pubs is VERY dynamic, changing continuously, and I'd basically have to do a lookup every time - believe me that's a LOT of lookups. I guess t hat's what I'll have to do though... sigh. Thanks.

[LaRetta]

Hi keshalyi

Don't give up! What you are requesting is certainly possible. I believe you won't even need a relationship - just good portal filtering to limit the display of Pubs records in your portal. It CAN be done!

I would suggest posting again but keep your focus on how to filter your portal so only Min(Pubs::Date) and Last(PUBS::PUBID) are listed. I'm not good with portal filtering but I know it's certainly possible. Everything is possible with FM. Oh, did I sound biased

LaRetta

[Ugo DI LUCA]

There could be another solution...

Set the new match field manually in each records for last Add "published" .

Then, when you script a new publication from the Add file, just use a script that would "cut" the value of the previous "related" record and "paste" it into the new one. So you will always have only one value per Add in Instances to match the key field in Add file.

Edited after reading La Retta's post.

Do you want the min and max here ? I figured you just wanted one raw per add "published" ?

[LaRetta]

Hi Ugo!

Min and Max will return the lowest or highest value in a field, depending upon the field type. So for instance, Max (Payments:: PaymentDate) returns the most recent date a payment was made on an account. And I *believe* they can be used to filter the portal display.

Like I said, I don't know the specific answer for him - I've only been using FM since September BUT, I know there IS an answer somewhere and I don't think he should quit looking until he finds it

LaRetta

[Ugo DI LUCA]

Yes, Min or Max are what I used in the test file. If the first instance was Add A and last instance was Add A again, does he need the Portal to show 2 rows with A ?

I must apologize for my french misunderstanding.

The test file is based on Max or Min.

[Razumovsky]

If you dont mind a slight compromise, you could accomplish this fairly easy without lookups using a combination of both Ugo and LaRetta's suggestions.

first thing is to create an identifying field/relationship in InstanceDB:

Concatenatedkey: PubName&" - "&AdName

Then a self join relationship: CaoncatenatedKey::ConcatenenatedKey

then a calc field (lets call it "COunt"):

Case(Max(ConcatenatedKey::RecordID) = RecordID, 1,0)

With this, now each group of ad/pub combinations has only one record that contains a 1, all others for the same ad/group has a 0 in count.

now, hop back to AdsDB.

your portal relationship remains the same (probably AdID::AdID), but you sort this relationship (under define relationships) by Count (descending).

You will still see all related ad/pub records in the portal, the difference is that if there were 35 representing 6 different publications, the first 6 records would be one from each of the 6 unique publications.

If you dont want to see the names for any duplicates, you could create another calc field in InstancesDB:

NameDisplay: Case(count=1, PubName, "")

then use this field in your portal in AdsDB to display the names for only unique PublicationNames.

So, your portal would still have 35 rows, but only the first 6 would have names showing from the 6 different publications and be completely dynamic, the rest would be blank.

Does that work?

-Raz

[LaRetta]

Hi Ugo!

You are certainly right! He doesn't need two rows with PubA. And I realized also that portal filtering is based upon establishing an additional relationship to accomplish the filter, so my comment that he didn't need a relationship is incorrect. But I thought he just wanted the oldest date per each unique Pub to display. My only reason in responding was to encourage him to not give up - that it CAN be done using the right calc to grab only one unique Pub and only the oldest date.

LaRetta

[keshalyi]

Raxumovsky, thank you, that is the best idea I've heard in ages, took me a minute to wrap my little brain around it, but I like it, I do, I do! I'll try it and let you know how it works. Thanks, ugo, for all your help, too, I've gotten it working with the method you first suggested, but I'm nervous to leave it that way, because I know, I'll add a script someday that adds or subtracts dates, and forget to insert the lookup . Laretta, thanks, s'much for the encouragement. I'm in that 'finishing' phase, and now suddenly everybody wants me to instrumentally redesign the database. Grr!!!!

[LaRetta]

Hi keshalyi!

Oh, I hear you! It can be discouraging to change when you're in the final phase. But, better now than after it's beem implemented for three months - believe me. Been there, done that Good luck to you on your design.

LaRetta

[keshalyi]

Thanks, guys, that worked great! I'll put that little fix in the ol' toolbox, right away...

[Ugo DI LUCA]

I agree Raz got the good final touch to this. The 0/1 and invisible fields is more than a good catch, specially if you just want a lit of all ads (published or not). More than a compromise, a real good catch.

Now, just one thought. Nobody asked (nor me)and I'm curious to understand what you needed that portal for ? You surely won't use it for data entry, nor to trigger a script ! And as it is displayed in the Add file, for any record of this file, it's not even a filter (you would use a conditional value list for that) !

Curious to understand