lost between the cracks

[tonyreay]

hello there..

well, we have discovered what would appear to be a Filemaker pro black hole of information.. nestled somewhere in the deep fissure between idiot knowledge and hi-tec egghead type computerese.

We

[LiveOak]

Wow, FileMaker rage!

I think I was able to pick out the question . You need to use a new composite field to perform your product lookup. You will also need to import all your product information into one file. Create a the following fields:

PartType (text) with value list "Avon", "Betterware"

NewPartNum (calculation, text, indexed) =

Case(

PartType = "Avon", "A" & OldPartNum,

PartType = "Betterware", "B" & OldPartNum,

"")

Replace "OldPartNum" with whatever the name of your current part field is in each file, respectively.

These fields are created in both Orders and Products. Drive your lookup from this new composite part number field, not your original part number. This solution assumes you move all your product information into a single file and keep the information separated by using the field PartType. If you don't wish to do this, the solution becomes quite a bit more arcane and really offers no advantage.

-bd

[tonyreay]

quote:

---

Originally posted by LiveOak:

Wow, FileMaker rage!

I think I was able to pick out the question

. You need to use a new composite field to perform your product lookup. You will also need to import all your product information into one file. Create a the following fields:

PartType (text) with value list "Avon", "Betterware"

NewPartNum (calculation, text, indexed) =

Case(

PartType = "Avon", "A" & OldPartNum,

PartType = "Betterware", "B" & OldPartNum,

"")

Replace "OldPartNum" with whatever the name of your current part field is in each file, respectively.

These fields are created in both Orders and Products. Drive your lookup from this new composite part number field, not your original part number. This solution assumes you move all your product information into a single file and keep the information separated by using the field PartType. If you don't wish to do this, the solution becomes quite a bit more arcane and really offers no advantage.

-bd

---

It is .. wait, firstly THANK YOU for responding so quickly...

it is clear that you know what the problem is... and even clearer that you know the solution...

now, if only we can get a translator..hahaha

if you could kindly rephrase your instructions using the following terminology then I will understand which words apply to me and which to filemaker instructions...

in my current two lookup files I have the following fields..

Lookup file #1 contains

Item Number

Page number

Brand/style

Item Description

Price

Price type (sale, discontinued, special.. etc)

Lookup file #2 contains

Item Number

key code

It took me some time to get these two companies to be comfortable with the idea that I should have this information BY COMPUTER, (even tho I could obviously type it all out in longhand). They supply it in two seperate files because they come from two seperate departments. Unfortunately, we need ALL the info contained in both files and, as you've probably realised, are not capable of figuring out how to merge them.. so we have two lookup files.

Anyhoo....

I type in, on the master... say.... Avon.. invoice, the product number.

One set of instructions goes to the first lookup file and produces the wealth of info.. typing it neatly into the correct fields.. and the other goes to the second lookup file and does the same.

your advice to...

"Create a the following fields:

PartType (text) with value list "Avon", "Betterware"

NewPartNum (calculation, text, indexed) =

Case(

PartType = "Avon", "A" & OldPartNum,

PartType = "Betterware", "B" & OldPartNum,

"")

Replace "OldPartNum" with whatever the name of your current part field is in each file, respectively.

These fields are created in both Orders and Products. Drive your lookup from this new composite part number field, not your original part number. This solution assumes you move all your product information into a single file and keep the information separated by using the field PartType."

unfortunately lost me right after the

"Create a the following fields:

PartType (text) with value list "Avon", "Betterware"

Assuming that the word "PartType" is your version of one of my fields.. which one...??

and, after I make it a text field and call it that, how do I make the value list?

I really don't mean to make it appear so goshdarn hard.. I thought it would be a simple matter (and, to you, I'm sure it's mind-numbingly soring to have to deal with goldfish brains like myself wanting instructions on how to reinvent the wheel..but,

having said that - thanks again... I guess just type slower.

ps the good news is that I should only have to be shown once...

------------------

remember to hug your

kids.. it's the answer!

[Ocean West]

What LiveOak Is Suggesting is

to only have 1 inventory or look up file.

all the products for Avon

and all the products for Betterware

reside in one file.

you would have all the fields you need

just ad one more to identify which product

type each record belongs to: (A or :

Your Fields Would BE:

Item Type (AVON or BETTERWARE)

PRODUCT CODE: (Item Type & Item Number)

Item Number

Page number

Brand/style

Item Description

Price

Price type

Then in your invoice file you would type in

your product number: A505, and or B505 and it would look up the correct information via a relationship:

Invoice File: Product Number =

Product File: Product Code

hope this helps..

[tonyreay]

well.. it kinda helped..except that the way I get these files with about 2500 items in each one prevents the feasibility of sitting and typing an a or b in front of each number..

can it really be that there's no formula that simply says to a field..

"look at this "Product" field and if there's an "A" go to the A lookup file to get and post the data. If there's a "B", go to the B lookup file.

then we can simply type in the item and mark the "Product" box a or b.

I'm sure it can be done, I just don't know how to write the formula relationship/lookup - although I think it involves "if" and 'case" commands.

anyway, thanks for the help so far - don't give up on me.. we'll get there

------------------

remember to hug your

kids.. it's the answer!

[BobWeaver]

You can use the replace command to set the Manufacturere field to all A's or all B's in the two separate files, and then when you have done that, import one product line file into the other. Voila, you have one product file with the Manufacturer field properly set.