Jump to content

This topic is 7349 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

Hello,

how can I receive a value from another database file inside a calculation?

My calculation e.g. shows = If( FIELD_A = "YES" , "value from data.fp5 FIELD_X" , 0)

The values that I want to look up in data.fp5 are $$prices. The purpose is not having to go inside the complex formula to change the value/price but go to data.fp5 and change the price there in FIELD_X.

Maybe I'm still thinking excel too much...

Thanks

Andy

Posted

You need a relationship from your current file to data.fp5. What sort of relationship that will be depends on your setup. How do records in the main file correspond to those in data?

Posted

Currently they don't correspond at all because I still have to create data.fp5.

data.fp5 shall contain all different fields with prices. For each price that changes in my If-formula I want to refer to that certain field from data.fp5.

Posted

I am guessing this is some sort of inventory system? You will need a relationship between your main file and the data file, probably from the Inventory ID number to the Inventory ID number. Then you can return, through a relationship, numbers from data.fp5 that match records in the main file on the basis of the field used in the relationship.

I hope i'm being clear...

J

Posted

well, what I have is this: I organize guided motorcycle tours. I have people that book direct with me (=retail) or with a tour operator (=wholesale). These are already 2 factors that influence my tour cost in my field COST.

But there is more: prices differ for people that:

1. stay in sgl room occupancy

2. share their room with someone else

3. ride with a passenger

So for my file COST I basically have 6 different options for what my price can be. What I've tried so far is that I've created a formula for my field COST that contains all those different options but, you can imagine how huge this formula is with the above 6 options AND 8 different tours.

I don't know if this is the right way but it works. Anyhow I'd like to avoid having to change the tour prices within my formula, I think the better way is to have all prices in a separate file. This way someone else (e.g.an employee) could change prices which wouldn't work if the employee had to go and look at this enormous formula.

Souds confusing right? Maybe I oughta send you my .fp5?

Andy

Posted

  Quote
I think the better way is to have all prices in a separate file.

Absolutely. Not confusing at all, in fact, this is probably just how i would have done it in your situation.

Probably the easiest way to explain this is by example. So yeah, post your file if you can (you'll have to put it into a zip file).

J

Posted

Okay, i'm looking at the Tours file now (nice color scheme, BTW) and it looks like it's pretty well built from my cursory examination. The next step i would take would be to create a field there called TourID. Make this a text field with an auto-enter option of "serial" and think of this as your primary key. That means this field will be the conduit by which this file accesses data in other files.

Now make a file called Hotels.fp5. This will have the fields:

Nights

Cost Per Night

Smoking

etc...

Make sure this also has a field called TourID. We will use this field in a relationship.

Back to Tours.fp5. Create a relationship from the field TourID in Tours.fp5 to TourID in Hotels.fp5. Check off the option for "Allow creation of related records." On a layout (any layout) in Tours, go to layout mode and add a portal based on this new relationship, and add several related fields. Now, in browse mode, enter data into these fields, and (if you go into Hotels.fp5) you will see that there is at least one record.

This is the bedrock of relational database design: Different types of objects have different files. It may seem overwhelming, but believe me, you'll be better off in the long run if you do it this way. There are several books you could buy that will give you a much better introduction to this way of designing a database than i could ever do.

HTH,

Jerry

Posted

I've taken the liberty of simplifying your field definitions and scripts, replacing Clear steps with Set Field steps, nested If functions with Case functions, and reorganized the logic so that the calcs are cleaner and less redundant. See attached for changes.

Yeah, I was a little bored. wink.gif

TOURSv2.zip

This topic is 7349 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.