Pop-up Menu based on another field in same layout

[eSBee]

Hello

I'm not sure if this is the right group to be posting to, so apologies if it isn't. I have a file called Estimate and I want to be able to display a pop-up menu of all the estimate dates for the active street address line. Can anyone tell me if this is possible and if so where do I begin?

I suppose a value list would be required but I'm not sure if I need to do a find on the street address line first to get all the estimate dates and then I don't know how to get them into a value list.

Any help would be most appreciated.

Many thanks

eSBee

[eSBee]

Hello

I'm not sure if this is the right group to be posting to, so apologies if it isn't. I have a file called Estimate and I want to be able to display a pop-up menu of all the estimate dates for the active street address line. Can anyone tell me if this is possible and if so where do I begin?

I suppose a value list would be required but I'm not sure if I need to do a find on the street address line first to get all the estimate dates and then I don't know how to get them into a value list.

Any help would be most appreciated.

Many thanks

eSBee

[stevie.at]

Sure. Just define a new Value List (as required for pop up lists and pop up menus as well) with the specific field as source. Than choose the new VL in the dialog "Field Format" while selecting your field. That's it.

[stevie.at]

Sure. Just define a new Value List (as required for pop up lists and pop up menus as well) with the specific field as source. Than choose the new VL in the dialog "Field Format" while selecting your field. That's it.

[MoonShadow]

eSBee, you want a Conditional Value List.

... if I need to do a find on the street address line first to get all the estimate dates and then I don't know how to get them into a value list.

Find no, relationship yes. Value Lists are based upon all values in a Table so you need to use a relationship to isolate only the corresponding dates for THAT address; otherwise it will display all dates for ALL addresses.

Create another table occurence of Estimates in your graph (called Streets), joining street address to street address on =. This new table occurence will limit the dates to only matching addresses. Keep in mind these street addresses must match exactly and if not, it's best to use a Unique AddressID instead.

Then create your Value List. Select 'Use Values from Field' then Specify Field. Select this new Streets TO in 'Select Values from First Field' (and select the date field). Then below in 'Value List Content,' click 'Include only related values' and select your Estimates table name there.

Note that (when using a self-join) you can reverse these two, ie, Select Values from First Field can be your original TO and 'Include only related values' can be your newly created TO.

[eSBee]

Thank you for the step by step instructions, MoonShadown, which seems pretty clear. Unfortunately, I'm unable to put this into action at this moment but will perform your instructions as soon as is possible and get back to you.

Can I just ask, does the street field need to be a pop-up menu/list? It's only the estimate date field that I require to be a pop-up menu/list. Hope this is not too silly a question.

Also thanks to stevie.at for responding, however MoonBeam's instructions capture what I want to implement.

Many thanks to all

eSBee

[MoonShadow]

Hi eSBee,

No, Addresses doesn't have to be a Value List - only the date field.

MoonShadow aka MoonBeam

[eSBee]

Oops, apologies to MoonShadow for writing his name incorrectly. I guess I thought MoonBean due to the clarity you opened up in my thinking and the other was simply a spelling mistake. Hope I'm forgiven.

eSBee

[MoonShadow]

No problemo! And good cover there too - sucking up always works. And he's a she (me) ... how would you know? You wouldn't but you're fun to tease!

[MoonShadow]

eSBee, I got a bit sloppy on two issues:

TO means table occurrence; not 'To' as in to-and-fro. I tripped someone else up by using this shortcut and I shouldn't have taken it for granted that you understood it.

And I was so busy chuckling (not offended in the least by misspelling) that I almost overlooked the importance of your Address field question. Truth is ... it wouldn't be a bad idea to create a popup out of it (use Values From Field and select your Estimates table and All Values).

In this way, your Users will select an address from prior field entries but could add different ones also if you choose to allow it (see Field Format popup options). It might protect from a mistype of the same address. An address is difficult to match exactly. Examples: SE or S.E., Road or Rd and if that happens, your relationship will not produce the desired dates. This may not be an issue for you but I felt it was worth mentioning.

Mr. MoonBeam

[eSBee]

Cheers MoonBean , for being so understanding. Yes, I did know what TO meant so was ok.

Thank you very much. I finally managed to carry out your instructions and it worked perfectly. However, when I move on to the next record the fields does not appear to update automatically. Shouldn't this be the case? Could you help on this matter?

FYI - the Estimate file is linked to an Enquiry file which is linked to a Customer file, and so gets the address details from the Enquiry file. Not sure how that will affect what I want to do. Basically, if I'm working from the Estimate file, the estimates date list should be available for every record as it's displayed. Hope this makes sense.

Many thanks

eSBee

[MoonShadow]

eSBee said ... FYI - the Estimate file is linked to an Enquiry file which is linked to a Customer file ...

Oh, that does made a TAD bit of difference.

Your Enquiry file contains the Address and not the Estimates file? I would have thought they would be stored in the Customer file (but I thought you only had ONE file originally - the Estimates file). Whether the Address is stored in the Enquiry file or the Customer file, there would be no need to store them again in the Estimates file so are you maybe just placing the Address field from Enquiry (or Customers) directly on your Estimates layout?

Are you joining these files based upon a UniqueID?

if I'm working from the Estimate file, the estimates date list should be available for every record as it's displayed.

I'm afraid you've now lost me. What is the estimates date list? Is it just one unique date existing throughout your estimates file? If so, stevie.at was correct in that you just need a regular value list (based upon Estimates and All values). But you indicated in your original post, "I want to be able to display a pop-up menu of all the estimate dates for the active street address line" suggesting a filter (based upon customer address), so you only want an Estimate Date to appear if another Estimate also has the same Address?

If you joined Estimates to Estimates on Address line, it should update (filter the popup) whenever you change records and the address changes (if the address is a field in Estimates). But you'll need to click on the popup for it to change.

Before I ramble further, it would help to know more (or see your files) - know how they're related and where that silly address field is from. And is it bigger than a breadbox?

Not to worry ... we'll get you through this. Just explain a bit more so I can fill in all the pieces of the puzzle and best assist you.

[eSBee]

Apologies for the confusion, seems to be a major habit of mine. Originally the files started off in v2 or earlier and then were upgraded to v5 using external links, and later I inherited them and upgraded them to v7. I think that is why fields from one file is in others, not sure as I'm still a newbie. If I'm honest, the whole system needs to be created from scratch but it's not possible at this time.

Anyway, what I said in my original email was correct and I had hoped that I didn't need to click on the street field to update it once the record changed. Would I need to write a script to automatically update it?

As for estimate date list, I was referring to my pop-up menu for all my estimate dates , sorry.

The relation between the files is as follows:

Customer - Customer Number (UID)

Enquiry - Enquiry Number (UID)

Estimate - Estimate Number (UID)

Customer to Enquiry (1:m)

Enquiry to Estimate (1:m)

If this is still not clear please let me know and I will try and sort it out but the information you have given thus far, works fine.

Many thanks

eSBee

[MoonShadow]

Okay, eSBee, we're almost there (I think):

... hoped that I didn't need to click on the street field to update it once the record changed.

1) This street field ... it doesn't currently contain information? You want the information to APPEAR in it? And you want the information to appear in it when you select a date from the popup? Or is the Street already filled in, but the popup doesn't filter down to only the associated dates?

2) This street field ... if you opened Define Database and look at your Estimates table, is there a street field listed there?

3) If you double-click this street field on the layout (while in layout mode), what is the file name assigned to it?

4) Tell me how you determine what dates should appear in that popup - All estimate dates existing in the Estimate file (whether the current customer has an estimate with that date or not); or whether only CERTAIN dates should appear and based upon what criteria?

It might help if you walk me through your thinking - pretend you're teaching me your job and explaining what to do when a customer needs an estimate; or even pretend I'm your 5-year-old. Sometimes it's quite difficult on this end to visualize what you mean or want.

The above questions should give me the remainder of the puzzle and I have confidence we'll make that address field succumb to your wishes very soon now.

[eSBee]

I'm pleased that you are feeling so positive. I'm beginning to feel more foolish as we go on (ha ha). Here goes...

My layout refers to the Estimate file and shows the Unique ID and foreign keys for Enquiry and Customer, other fields which are not relevant, as well as the following:

First Name

Last Name

Estimate Date

Street

Post Code

Invoiced?

[MoonShadow]

Mr. MoonBeam says (ever so slightly shaking his head) ...

eSBee, I'm afraid there has been a misinterpretation of my instructions on this point:

eSBee said ... On following your instructions I created 2 new fields, one for street (vlStreet) and one for estimate date (vlEstimate), which I formatted as pop-up menus, and created the TO via Street.

That vlStreet field will never populate because it isn't supposed to exist. You only need to create value list for street; not a field. I'm VERY glad you didn't bother deleting your real address field.

Don't get frustrated or feel bad. I can't tell you how many times I've done these very same things over the years. Personally, I think 'proper User Instruction' particularly when only using words, is more difficult than brain surgery, and should be considered its own art form.

Okay, I think the best approach is to let you re-read from the beginning (if you so wish) and bring your solution into synch will your/my plans for it. Then quote me where you are unclear and I'll explain it again.

Wow. We were close to major problems. I sure hope you back up your files before these types of changes.

[eSBee]

I'm so glad that you've stuck with me through this, phew. I will reread as you advise and come back to you with any problems.

No worries about backing up, I'm not working on the live files but I do backup before implementing any changes. I've got enough frustrations trying to sort out the new tasks, so don't want to create any more headaches

Thanks Mr MoonBeam you've been fantastic. Am I allowed to post to you directly?

eSBee (I can see your light )

[MoonShadow]

These new Value Lists - attach the value lists as popups to your EXISTING fields (street and estimate date). No new fields for either of them.

[eSBee]

Ok, will do.

Ta very much.

eSBee

[eSBee]

Hi MoonBeam

Your light is shining so bright! I've done it! After a couple of days rest, I returned to your instructions and bam, it worked. I've got rid of my new fields and started from scratch, by checking my relationship the orginal table and the TO, which was wrong (ha!), then slowly walked through the creation of the Value List, followed by making my Estimate Date field a pop-up menu and it all works great!

Thank god for that, I was beginning to go crazy. Result! Ta very much, you are fab!

Many thanks

eSBee