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

Help: Pull Down based on OTHER pulldown?!


Hoven
 Share

Recommended Posts

Hi all,

OK, here's a problem I could use help with.

 

I have 2 databases: Sales Orders (invoices) and Inventory.

 

* In the inventory, I have records for artwork stocked.

On each record is: Product Name, Artist, Category, Amt-In-Stock, Price, Size, etc.

 

* On the Sales Order form, I have the fields:

Artist, Product Desc, Quantity, Price, etc.

 

Now, I may be going about this the wrong way, but I want to popluate each row of the order form with an item from inventory. But rather than do a pull-down by unique Item Number (a very long list), I thought I'd try to shorten it up a bit.

 

So....

I have the Artist field set up to pull down a unique list of the Artists from the Inventory database (a bit tricky, but I got that!).

 

Now, what I need to do is define a pull down from the Product Desc field that includes products from the inventory that only match the artist selected in the Artist dropdown (for that row... 4-6 rows on the order form).

 

Here's an example.

The inventory has artwork from Abby, Bob, Chuck, and Don.

Chuck has 3 different art pieces in stock (Trees, Cars, Landscape).

So, on the Order Form, I would pull down the artist list and select Chuck.

Then (what I want) is for the pull down list under Product to only show Chuck's items, where I can then pick... oh, Landscape.

Or, maybe after picking an artists, I get a pulldown of unique Item Numbers and the other details fill in automagically.

 

Plus, in the end, after I've pieced together the item this way to make a row on the order page, I'll need to have figured out the exact item number so I can reduce stock from the inventory database.

 

Thoughts? Help? I'd appreciate either a better way or the settings/scripts suggestions to make this 2nd pull-down work.

 

T h a n k s ! ! !

Link to comment
Share on other sites

Hello Hoven,

Yes, that can be done.

 

To accomplish it, you will first need a relationship which matches the Artist field (the one with the first drop-down list attached) to the corresponding field in a new table occurrence (TO) of the inventory table.

 

Once you have this new relationship in place, you will be able to create a new value list and define it to use values from a field. Select the item number field from the new TO (as above) and then select the option at the bottom left of the dialog for only related values and select the TO that current layout is based on as the starting from point.

 

Once you've done this, the new value list you've created will be filtered so that it only includes inventory items for the artist whose name has been selected in the first list. ;)

Link to comment
Share on other sites

Hello Hoven,

Yes, that can be done.

 

To accomplish it, you will first need a relationship which matches the Artist field (the one with the first drop-down list attached) to the corresponding field in a new table occurrence (TO) of the inventory table.

 

Once you have this new relationship in place, you will be able to create a new value list and define it to use values from a field. {...}

 

So, Let me see if I've got this...

I'm setting up a relationship between the Order Artist and the Inventory Artist (via the Database Setup / Relationship tab).

Then I'll be defining a new List Values based on the relationship.

Then I'll be setting up the Order Description behavior based on the Artist Value List??

 

Whew! Did I understand you right? :eek:

 

I'll play with this some.

Thanks Muchly!!

===[ Hoven ]===

Link to comment
Share on other sites

hmmm. fwiw, in this thread you can find a sample conditional value list to take apart.

 

kjoe

 

Hi there,

OK.... I've plowed through the examples and threads and I sort of get it, but OK, I don't really get it. I did use the example from kjoe to do the one field from the other (thank you very much)... but (and again, I may be doing this the wrong way or making too hard on myself) Here's what I'm trying to do:

 

I'll have a very long list of items in an inventory table. So, in the Sales/Invoice table/form, I want to make the data entry easy by reducing the choices in steps. The Form layout is like this:

 

Artist - Catagory - ProductName - ProdID - UnitPrice - Quanity - ExtPrice

 

Oh, and just for fun, each of these will be repeating fields (4 or 5 times for the 4 or 5 lines on the invoice).

 

I want the user to Pick from a unique list of Artists from the Inventory table.

Then they will pick a category which is a list of all the categories that artist has in the inventory table.

Then they will pick a ProductName from a list of all the ProductNames in the inventory table which match both the Artist and the Category slected thus far.

At this point, the user will have selected a unique row from the inventory, so now the ProdID and UnitPrice should fill in automatically.

 

I've attached a mini-sample of what I'm trying.

 

I think I'm finding myself confused by the auto-enter:Look Up feature and the Field Format: Display Values and I even saw something about a "Join" as a new table connecting 2 tables (). There are many many places to set up validations and calculations and look ups and relationships and frankly I'm not sure which what I need.

 

Thanks for any advice/help!

===[ Hoven ]===

Link to comment
Share on other sites

You must zip your file before uploading otherwise download will fail.

 

Oh Really! I just found that this is a browser related issue. Safari fails, Firefox succeeds. Can anyone with a windows machine tell me how they fare (and mention their browser)

 

thanks

 

Maarten

Link to comment
Share on other sites

Hoven,

 

Now for some content:

I wonder whether the step-by-step selection is the way to go.

If this is a kind of browse-and-select procedure, why the hierarchical search? Some users might prefer to search by product name or category first.

 

maarten

Link to comment
Share on other sites

Oh Really! I just found that this is a browser related issue. Safari fails, Firefox succeeds. Can anyone with a windows machine tell me how they fare (and mention their browser)

thanks, Maarten

 

Well, I have both Mac and XP. The upload I did was from my XP (my daughter was on the Mac editing a movie for a school project!). I just tried to download it and it worked OK (Explorer v6). I probably should have zipped it first, just 'cause... but it was late and I was being lazy! Opps!

 

===[ Hoven ]===

Link to comment
Share on other sites

Hoven,

 

Now for some content:

I wonder whether the step-by-step selection is the way to go.

If this is a kind of browse-and-select procedure, why the hierarchical search? Some users might prefer to search by product name or category first.

 

maarten

 

Well, I'm all for a better, easier way.smiley-smile BTW, there really are only 2 users... my wife and I. We're it for the business.

 

The only reason for the step-by-step is to prevent us from clicking on the product pop-up menu and getting 600 items. We don't have or know the numbers for each item to just enter it cold. So I was trying to think of a way to reduce the selections from the pop-up on the invoicing form.

 

We sell artwork with caligraphed text. There are 8 artists. Depending on the artist, they have between 6 and 25 "designs". And each design can have 3 to 5 different texts. So as an average... 8 * 15 * 5 = 600 products!).

 

The categories are like media (litho print, giclee (ink-jet print), paper cut, embossing) and text type (reform, conservative, orthodox, etc). Maybe a better notion is to come up with a unique product key that gets us in the ballpark.

So, for example:

HF-LP-R = Howard Fox - Litho Print - Reform

AN-PC-C = Amalya Nini - PaperCut - Conservative

(although I'd do them without the hypens).

 

Then I could use that one field to provide a list of matching products.

 

Either way, once I pic a unique product from the Inventory, the rest (unit price, artist, etc) should fill in automatically.

 

Again, I'm open for ideas. I'm actually using the Business Productivity templates that came with my Filemaker Pro 8, so that's my starting point (although it didn't connect the orders and invoices... I'll need reduce the inventory by the counts on the order forms; but that's a problem for another day! :rolleyes: )

 

Many Thanks!

===[ Greg ]===

(aka Hoven)

Link to comment
Share on other sites

Hi,

 

I got carried away on a longish commute. see this.

 

I made a setup with a multi field filter for picking a product. this requires some discipline in entering category names etc (i split your cat field into three separate ones) in the inventory table. I added a feature for making sales line items relational instead of using repeating fields.

 

The amount in stock is updated automatically but I must say I don't know how this is tracked normally. Anyway if you delete the sales line item the stock is updated also (plus this time).

 

You might want to add a finalize-this-sale script whereby it is no longer possible to delete the lineitem; and of course customer data need to be related also.

 

please report any issues.

 

Maarten

Link to comment
Share on other sites

Hi,

 

I got carried away on a longish commute. see this.

{...}

please report any issues.

Maarten

 

Wow! smiley-surprised

Maarten, I was hoping for some pointers or a lesson or advice or something, not a re-write!

This is awsome. Thank you so much!

 

I've got some serious study-time ahead of me here to understand how this all works, and I already have a bunch of questions (with which I'll try not to flood you with!)...

 

Like, the query table isn't really a table (at least it doesn't show in the table list). It's only in the relationship graph.

So, a) how does it get there?

and

b) is that just a reference to the inventory table? So Inventory links to SalesInvoice links back to Inventory?

 

I'll play with this. Thanks again... I really appreciate your help!!

 

===[ Greg ]===

Link to comment
Share on other sites

Like, the query table isn't really a table

it's a table occurrence in the relationship graph, two relations between two tables cannot be represented by one occurrence, so an extra one is created when defining the relation.

 

maarten

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