Slightly more complicated OE system

[Agent Ska]

Ok, so every article I read that talks about Order Entry Systems and how Invoices, Products, and Invoice Items relate, all seem to rely on a fairly simple products table.

In each example, a widget, is a widget and a whatzit is a whatzit.

The system I am starting to lay the ground work for is a tad different.

For example, say I have 1000 of art files. And those art files can be produced on 10 different items.

To fit into all of the examples I have seen, I would need to have a product table consisting of 10,000 products.

But, what I would really want is a table of art, and a table of items, and perhaps a table that joins the two together so Art001 and Item 002 is the "product" being used in my Invoice Line Item.

To further complicate matters, I'd like each customer's record to be able to keep track of what "product" they have ordered in the past for easy re-ordering.

Anyone have any idea on how I would begin to approach this?

[comment]

Can you explain what you mean by "art files can be produced on 10 different items"? Would every art file have exactly 10 items? And what exactly is an "art file" and what is an "item"?

[jansenw]

Looks like you need to implement a Bill of Materials (BOM) module which maintains products consisting of other products.

ItemA

ItemB

ItemC

ItemD is consists of ItemA, ItemB and ItemC

ItemD is consists of ItemA and ItemC

etc...

[Agent Ska]

an art file in this case is a digital image.

We are printing that digital image on many different products, I picked 10 for round numbers. But, no, not all art is printed on the 10 different products. Keeping it simple, a product could be a mug, poster, etc.

[comment]

But, what I would really want is a table of art, and a table of items, and perhaps a table that joins the two together so Art001 and Item 002 is the "product" being used in my Invoice Line Item.

I see no reason why your InvoiceLineItems table couldn't be a join of three tables: Invoices, Art and Items. The only question here is if you can easily determine the final price using the information looked up from Art and Items.

[Agent Ska]

Well, each item has it's own price point (regardless of the art that is utilized). And the art has no cost associated with it.

Edited December 9, 2008 by Guest

[comment]

OK, then you shouldn't have a problem. Instead of creating all the possible combinations beforehand, just create them on-the-fly as they are ordered.

[Agent Ska]

I'm thinking that is what I want to do.

I'll be trying to put it together shortly...

[Agent Ska]

OK, then you shouldn't have a problem. Instead of creating all the possible combinations beforehand, just create them on-the-fly as they are ordered.

Ok, so I am just not seeing how to pull the data properly.

• artFiles Table related to lineItems via the artFilesID field.

• productItems are related to lineItems via the itemID field.

• Invoices are related to lineItems via the invoiceID field.

all of the tables can write to the lineItems table.

During data entry of the job information, I want to be able to type the artFileID number and have the artDescription field populate, and then tab to the productID field, text enter that ID number, and have the item description and price populate, and then enter the quantity for the calculation of the line item price to calculate.

I don't see how to pull the artDescription, and the lineItem description/price to (either) the lineItems table or the Invoices table.

Can anyone help?

[comment]

See if the attached helps (substitute art files for colors).

Note: only Invoices should be allowed to create records in the LineItems table.

ProductColors.fp7.zip

[Agent Ska]

That did help. Thank you.

[Agent Ska]

Now, adding onto that same database, I've added a Customer's table.

And what I would really like to have is a layout that has the individual line items that customer ordered. I have done that... however, if the same art-item combination has been ordered more than once, than, the portal displays them more than once.

Is there a way that the portal can display each art-item combination only once?

Furthermore, the ideal purpose of this layout would be for a sales rep to look at this customer record, see what line items they have ordered previously, and be able to quickly set-up a re-order.

So, tell me if this is possible - to have a check box in the portal row, where if enabled, we call a routine that converts the selected items to an "invoice" for that customer.

Edited December 17, 2008 by Guest

[Vaughan]

"Is there a way that the portal can display each art-item combination only once?"

Set the portal to display the art-items records instead of the line items. (This assumes that art-items is related to line items.)

[comment]

Is there a way that the portal can display each art-item combination only once?

It's possible, though not exactly simple. You could use either the so-called 'Ugo method' to de-dupe the portal, or a custom function such as this:

http://www.briandunning.com/cf/890

to display the results directly in a field.

So, tell me if this is possible - to have a check box in the portal row, where if enabled, we call a routine that converts the selected items to an "invoice" for that customer.

I wouldn't use a real checkbox for this, because two salespeople working simultaneously would interfere with one another. But you can have a button in the portal that puts/removes the selections into a global field, which can then be used to generate a new invoice. The selected items can be marked by conditional formatting or by an unstored calculation field.

Set the portal to display the art-items records instead of the line items.

Yeah, well - this entire thread is about art and items being in two separate tables...

[Agent Ska]

It's possible, though not exactly simple. You could use either the so-called 'Ugo method' to de-dupe the portal

What in the world is the Ugo method?

[Agent Ska]

Ok, ok... so, as I dig deeper into this, I think I can see the value to having a table of previously ordered art-item combinations.

If we back-up, just a bit.

Would it be possible to do this - keeping the invoice layout the same select the artID, enter the itemID and have filemaker perform a lookup in the existing art-item tables to see if the combination exists.

If it does, it ties to that art-itemID.

If it does not, it creates a new art-itemID and ties to that record?

This would actually be beneficial as I could see a script being called when that new art-ItemID is created that adds a line to the invoice so that production knows they have work to do in order to complete the job.

Then, the customer's portal can tie to the table of previously art-ItemID's rather than line items. Which only one of each would exist, and solve that problem as well.

Right?

Edited December 17, 2008 by Guest

[comment]

I don't like this method very much, because a scripted process is always vulnerable. In any case, you will eventually end up with all the possible combinations, so why not just create them once and be done with it?

BTW, a portal to such table will not summarize the quantities, unless the customer's ID is entered into a global in the same table.

What in the world is the Ugo method?

Something for you to search for?

[Agent Ska]

ha! I had posted the question after doing a search on here for it and not coming up with anything. Since, then I did find it, but forgot to ammend my post.

I guess the point of not creating it in advance is to be able to route an order appropriately. If the product has been ordered for that artwork before, it can go right into production. If it has not, than a production person has to do something to prepare it.

So, somehow, I have to delineate the difference & would like the system to be able to do so for me.

[Agent Ska]

BTW, a portal to such table will not summarize the quantities, unless the customer's ID is entered into a global in the same table.

For this, use, I am not in need of totals, simply a way in which I can see what line items have been ordered - and an easy way to select those line items and set-up a re-order.

[comment]

If the product has been ordered for that artwork before, it can go right into production. If it has not, than a production person has to do something to prepare it.

I don't see you workflow, so obviously my advice is limited, but it sounds like the production person ought to enter (or report) the fact that he/she has indeed prepared it. This would then be the "missing table" of previously ordered combos.

Alternatively, you could define a self-join of the LineItems table, matching on both ItemID and ArtID. This would enable you to automatically flag the first combo of its kind (by virtue of being the same one as the first related record). This same self-join, BTW, is also the first step in implementing the Ugo method.

[Agent Ska]

Ok... So, I think I am very close.

Attached is an example.fp7 file.

I have followed the examples found here for the "Ugo Method".

I have two hiccups with what I have done. First, I added a lookup to the lineItems table to include CustomerName. That does not auto-populate on line-item creation for some reason (and the customerName has already been selected on the Invoice layout).

Secondly, when I view the portal on the Customer's Line Items layout, it now displays blank lines for each time the product was ordered.

Can someone take a look and tell me what I am doing wrong with both those issues. (and any other you may spot) :

example.fp7.zip

Edited December 18, 2008 by Guest

[comment]

I changed a few things in your relationships, and pointed the portal to the correct TO. There were a few other minor changes which I didn't track, so you'll have to compare the files carefully.

example.fp7.zip

[Agent Ska]

Thank you! That looks great, except the customer name is still not auto-populating in the lineItems Table.

And, I don't see a reason that it wouldn't perform the lookup.

[comment]

I haven't looked at that part. Why do you need to duplicate the customer's name in the LineItems table at all? It is available through the relationship from the customer's record.

BTW, you should link the invoices to customers on Customer's ID, not name.

[Agent Ska]

I haven't looked at that part. Why do you need to duplicate the customer's name in the LineItems table at all? It is available through the relationship from the customer's record.

I guess you are right, I shouldn't need it.

However, there is something not working quite the way we want it to with the calculation.

If a line item was been ordered by two different customers, it will only display on the first customer's portal.

BTW, you should link the invoices to customers on Customer's ID, not name.

Yes, thanks, I corrected that.

[comment]

Oops, I am sorry - I lost track of the goal while we were discussing other things. For this, you will indeed have to lookup the CustomerID into the LineItems table, and add it to the filtering self-join.

You must make sure that an invoice is committed before entering new line items, otherwise the lookup won't work (as you have noticed).

[Agent Ska]

Oops, I am sorry - I lost track of the goal while we were discussing other things. For this, you will indeed have to lookup the CustomerID into the LineItems table, and add it to the filtering self-join.

Ok, I think I now see exactly how the self join is supposed to work. Thank you for all your help on that.

You must make sure that an invoice is committed before entering new line items, otherwise the lookup won't work (as you have noticed).

Why would I need to commit the record? I thought once the customerID field was committed, the lookup should work fine. Isn't the customerID field committed once the customer is selected from the drop-down box?

Edited December 18, 2008 by Guest

[Agent Ska]

I wouldn't use a real checkbox for this, because two salespeople working simultaneously would interfere with one another. But you can have a button in the portal that puts/removes the selections into a global field, which can then be used to generate a new invoice. The selected items can be marked by conditional formatting or by an unstored calculation field.

I would think the only way to handle this would be with a script, as there is no way that I can see to add multiple values to a global field with any of the options in the button set-up. Am I correct?

And, I'm confused, don't the global fields present the same issue that the checkboxes would if multiple sales people were logged in at the same time?

[comment]

Why would I need to commit the record? I thought once the customerID field was committed, the lookup should work fine. Isn't the customerID field committed once the customer is selected from the drop-down box?

Fields aren't committed, only records. It's a buglet in Filemaker: if the parent record isn't committed, the child will not lookup from it. In practice this means that the following sequence of events will not work:

• Create a new invoice

• Select a customer

• Enter data in portal

But this one will:

• Create a new invoice

• Select a customer

• Commit the invoice record

• Enter data in portal

there is no way that I can see to add multiple values to a global field with any of the options in the button set-up

Well, Set Field could do this (if the product isn't already in the field, set field to itself + the product, otherwise remove the product from the field).

don't the global fields present the same issue that the checkboxes would if multiple sales people were logged in at the same time?

Globals are user-specific (or rather session-specific).

[comment]

It just occurred to me that since you're not interested in summarizing the quantities, there might be a much simpler way for you:

1. Define a field in LineItems to concatenate the item and the art (it can even be an unstored calculation, getting the descriptions directly from the Items and Art tables);

2. Define a value list based on this field, showing only related values starting from Customers (Filemaker will complain if the field is unstored, just ignore it);

3. Define an unstored calculation field in Customers as:

ValueListItems ( Get(FileName) ; "YourValueListName")

and show it on the customer's layout.

[Agent Ska]

Well, now that we are past the holiday's, I am back attacking this project and this is the step I am hung up on currently.

Well, Set Field could do this (if the product isn't already in the field, set field to itself + the product, otherwise remove the product from the field).

Is there a function that would be able to test if the string exists in the field - and then to add or remove depending on it's existence?

I think I have figured out how to add it to the field, but definitely not how to remove it. And I have not found a similar topic on here - although I find the search feature on this site a bit clunky.

Thanks, in advance.

[Agent Ska]

It just occurred to me that since you're not interested in summarizing the quantities, there might be a much simpler way for you: ...

Although, I prefer the other method because of the added flexibility it provides me.

Thank you for this thought, however. I may integrate it into other areas of this or other solutions.

[comment]

Is there a function that would be able to test if the string exists in the field - and then to add or remove depending on it's existence?

See if this helps:

http://fmforums.com/forum/showtopic.php?tid/191281/post/272278/#272278

[Agent Ska]

OMG!

That is gorgeous.

Thank you!

[Agent Ska]

One of the features that I thought would be very useful to add to this portal is a date field that shows the last date the line item was ordered. I have a date created field in the line item and I am calling that to display in the portal. However, it appears that the calculation is removing new duplicates of the line item, therefor only displaying the date which the line item was originally ordered. Is there a way to reverse that so that it shows the most recent line item?

And, reconsidering your suggestion of summarizing the quantities of times the line item was ordered, if I was going to add a summary field to the portal to illustrate how many times the line item has been ordered, where would that calculation be performed?

Edited January 6, 2009 by Guest