Jump to content
Salesforce and other SMB Solutions are coming soon. ×

I thought I had it, but I can't make it lookup


spongersgirl
 Share

Recommended Posts

I have a guest table and a tours table (which may need to be a portal). In the tours layout I have a guest ID and guest name. I want to be able to put in the guest ID and have it lookup the guest name automatically. I assigned the guest name field as a lookup field from the guest table... but when I input a guest ID on the Tours layout, nothing happens. I'm totally new at this and am attempting something rather complex (this problem is only one of many, but if I can figure it out it may help with the other issues). Relationally, both tables are related at guest ID and guest name....

Help! I'm at a loss about why it won't work....

Thanks,

Amanda

Link to comment
Share on other sites

Hi Amanda,

 

"related on ID and guest name.."

 

If this is true, herein lies the problem. They should only be related on ID. Then your Lookup on the guest name should work. :)

 

LaRetta

Link to comment
Share on other sites

Relationally, both tables are related at guest ID and guest name....

relate only by guest ID and it should work fine. if you need further assistance, please post

 

maarten

Link to comment
Share on other sites

That worked! Thanks so much. Can I ask one more question?

I have another table called parks with about 10 different parks. On the Tours page I want to be able to input the park ID and have it lookup the park name. The only catch it that I want to look up more than one park ID per tour id... The tables (tours and parks) are related through park id and nothing else (based on my last issue).

The problem I'm running into is one of two things.

1. if I do it as a lookup, it always fills the same park based on the first park id I input and it ignores the subsequent park id entries.

2. if I try it as a portal, it won't let me enter more than one line (or on a new record, it won't let me enter any data in the portal.

 

What am I doing wrong?

Thanks everyone!

Amanda y

Link to comment
Share on other sites

Hi Amanda,

 

It sounds like you need a many-to-many join table. It would only contain two fields - ParkID and TourID. Since one tour can tour many parks ... and one park can have many tours, an n:n (many-to-many) fits the bill.

 

Each time you select a park, you will create a new record in this join table. It's sorta like a LineItems-to-Invoices situation. The new join record will grab the TourID and, when User selects a park, the selected ParkID will be grabbed as well. So if TourID is 6 and it tours 3 parks, the new join table would contain 3 records as:

 

TourID 6 and ParkID 34

TourID 6 and ParkID 21

TourID 6 and ParkID 15

 

I could put together a sample if you like.

 

LaRetta

Link to comment
Share on other sites

Would you mind? It's a lot easier for me to see it and understand it than read it and understand it. :) Especially since I'm so new to this program.

THanks so much!

Amanda Y

Link to comment
Share on other sites

Here is one way smiley-laughing

 

It will give you the idea. I am sure others will have better ideas. There are many approaches to creating new records in a join table. I've changed the file.

Link to comment
Share on other sites

That's pretty cool. I think though that you were right earlier when you said it was like an invoice, where there are multiple item listings. If I upload my file, would you look at it and let me know if I'm on the right track? If not, that's okay. But I feel like I'm spinning around and am quite lost.

Amanda Y

Link to comment
Share on other sites

Amanda, you are using repeating fields to hold the ParkIDs; in fact, most of your IDs. When you have a one-to-many, you should break them out. Because one tour can stop at many restaurants, many parks and stay at many hotels?

 

I adore repeating fields but I would NOT use them in this instance. You will have serious problems if you do so. Are you willing to restructure your whole solution? It appears that you are early in the design. Otherwise, use your repeating fields for ParkID - attach a value list to each one for selecting each. But, as you see, you will have a mess matching the Service Dates to each repeating ParkID, RestaurantID etc as repeaters.

Link to comment
Share on other sites

I am totally okay with restructuring it. I'm still early enough in that it's not a huge deal.

So basically, I should just make 7-10 (or however) park ID fields (named/numbered differently) and 7-10 park name fields (named differently) and then just use the lookup function?

And then do the same with the restaurants and transport and all?

Amanda Y

Link to comment
Share on other sites

Not FIELDS or repetitions ... RECORDS. smiley-smile

 

The park name can just be planted there (from the relationship). The portal on my demo (the last demo, blush) has the JOIN portal. That is the records which replace repetitions OR fields in Tours. The park name in that portal is from PARKS. You wouldn't use Lookup at all. The park name will just display because it is related (from the Join table to Parks table).

Link to comment
Share on other sites

I feel rather stupid b/c I still can't get it.

I added the join table for parks (it has a park id and tour id and is related to both tables).

On the tour layout I see the gParkID and I setup my portal for the park id and park name.

However, I don't want to use a checkbox like in your demo b/c I need to specify which park/which date. So I don't have that there.

When I switch to browse mode and try to enter a park ID in the portal, it won't let me. Would it be better to just use multiple drop down boxes (one for each date)? That might simplify it.

Sigh. I'm sorry to ask so many questions. I know what I want in my head, but I just can't seem to wrap my mind around how FileMaker relates it all.... This is my first database experience... my brain hurts. :)

Link to comment
Share on other sites

" ... my brain hurts."

 

Ah. Yes indeed. That is good. It means your brain is expanding. Hopefully, so is mine. smiley-laughing

 

I don't know your needs well enough. Which park, which date? Tours should tell you that.

 

But that adds the missing piece for me. You have Tours (which should just contain tour name, DateFrom and DateTo (etc) and then you ALSO need EVENTS (lineitems), which is your n:n join table. It would have ONE day, the hotel, restaurant, client (each of the IDs) and also the TourID which pulls them all together.

 

Maybe someone else has a demo of a many-to-many reservations system? It isn't something that can be explained in a post. These can get complex. :rolleyes:

Link to comment
Share on other sites

Yeah, you're on the right track. I'll look around and see if there any examples I can look at... I saw one, but short of paying about $3000 for it, I couldn't look much at it. :(

I'll play around more. My company runs tours in the Orlando area, so I'm trying to put together itineraries on the tour page.... Who would have thought it would get so crazy! :)

I really appreciate your help though. It had given me more food for thought.

Amanda y

Link to comment
Share on other sites

You need to get some relational theory under your belt. I suggest you take advantage of http://www.fmpug.com/ . GO to the meetings - they probably have a chapter in your area! Put your solution aside and break it down on paper - Andy will help you. And be prepared to research all forums for similar bookings/reservations examples. I suggest NOT touching the design until you have the flow on paper.

 

But it looks like DAY is your finite piece. It will need to be tightly scripted. In the JOIN table (lets call it Events), place fields for each ID. Present the client with the tour (and it's date span) by selecting TourID (displaying name) from value list popup. All details about the tour can display at the top (just place the Tours fields). Since a Client may attend one tour for only PART of the tour, each day will be a record.

 

ClientID

TourID

EventDate (conditional VL only related to Tours)

RestaurantID ... etc.

 

I just don't know enough - for instance, is a TOUR set in stone and the hotels and restraunts pre-determined? Or can a Client stay in a different hotel than the group? Or eat elsewhere? I have far more questions than answers. Can a Client only attend a Tour for part of it? Scheduling a Client to pre-select date span can be handled by script to create each daily record.

 

Be prepared for additional brain strain. And I'm happy for you. But please don't use repetitions or fields - go relational because otherwise your whole solution will choke you and soon. Get the base right. smiley-smile

 

Corrected: Removed the mention of using globals ... that was before I realized the point of view for creating records would LIKELY be the join table.

 

LaRetta

Link to comment
Share on other sites

Thanks! I'll check out that site you mentioned. It'll probably help to sit down and ask a ton of questions. I tried to do it on paper, but while it works in my head.... it's like calculus... there are a lot of abstract (to me) angles that warp my head. :)

I also got a book to try and fill in some of the blanks. FileMaker Pro 8 - The Missing Manual or something like that.

I really appreciate all your help. :)

Amanda Y

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use