Hi,

I've run into what should be a common occurence, and I'm wondering how others have handled this situation...

If you have set up 5 tables...

Departments

Accounts

Invoices

Line Items

Products

And you want to create Line Items in a portal on an Invoice layout.

Looking up prices into the Line Items from Products is fine, but what if you also want to look up a markup value from the Department table via Invoices and Accounts? If the Invoice record has not been committed prior to adding line items in the portal, the lookup doesn't work.

Would you force people to commit the record prior to creating line items by making users script related record creation, or would you just do a relookup script for the line items during the script which finalizes the invoice record?

Thanks in advance,

Dana

Is this the general idea of what you're looking for? (I didn't include Accounts in the example...same principal, though)

Invoice.zip

Thanks for the reply,

My problem was that the department id was defined in the invoices table rather than the line items table (each invoice is only attached to one account, which only has one department)

So my lookup wasn't triggering to auto-enter the correct markup in the line items table unless it was looked up after the invoice record was committed.

I solved the problem by looking up the markup value into the invoice table instead of the line items. Since there's only one account (and one department) per invoice, there only needs to be one markup for the whole invoice, not for each line item....

Thanks again,

Dana

This has been siting on my desktop since your first post - and I still don't see a good solution for it. You are lucky to be able to escape the problem, but it is not going to work when, for example, a customer has a discount rate field, and a product has a discount status field (apply customer's discount to this product or not). There seems to be no way for a 'ghost' record to lookup data from, or through, another 'ghost' record.

maybe that would be one of those times where a script-triggering plugin would be of use... changing the customer id in the invoice record triggers an auto-enter calc which runs a relookup script in the line items to pull in the updated markups/discounts

I believe I'd be more comfortable with a "scratch" invoice - the entire 'commit' concept looks a bit half-baked to me anyway. Either way, it's a lot of resources to invest in an otherwise trivial problem.