I have a field Product Code on the Main Page. I have 10 different Product tables (Product 1, Product 2 etc.) all related to Main Page

What I want to do is instead of creating a different script for each of the different product, is to create one generic script step.

Currently my script is essentially:

SetField(Globals::_Global Product Code_;Product 1::Product Code)

Whereas I want

SetField(Globals::_Global Product Code_;[layout by calculation]::Product Code)

Is this possible? all I can think of having is

get(layout name)::Product Code but this doesn't work

Any comments or help would be greatly appreciated

what I

Try this:

Set Variable($Layout;Get(LayoutName))

SetField(Globals::_Global Product Code_;$Layout &"::Product Code")

That should work.

Just one question, why 10 product tables? Couldn't you have all your products in one table?

Thanks aldipalo for the quick reply, but actually what is happening now is that instead of the serial number printing on my main page its actually printing PRODUCT 1::PRODUCT CODE. Even though _Global Serial_ is set up as a number.

The only reason I have multiple tables is that I read it (possibly mis-read) it on a forum post. But as it is the setup works fine. My main concern was incrementing different serial number values for different products (ie Product 1 could be at serial # 9999 and Product 2 could be a serial # 1000) I'm sure that I could script the database differently but at the moment unless anyone can see a glaring issue with the way my database is setup i'll keep it as is

Edited September 10, 200817 yr by Guest
Added a screen capture to show what I mean

I'm sorry, I thought you wanted to set your global Field to the Product Code from a specific record, not generate a serial number in a list view.

Please explain in greater detail what you specifically want to accomplish. How you would like it to work.

If possible, give us a cloned copy of your DB to look at. Add a few records.

Thanks again for the fast reply.

So the company has many different product lines that we produce (Represented by Product Code) and as it is now, every time that they want to generate a new serial number they write it out by hand in a book, fine for 1 or 2 new products but a real pain for 100. So what I'm doing in my database is having the user select from a drop down list the product that they want a serial number for. They then provide how many of the product they have, the batch number (just when the product was made ie 1st or 2nd product run) and the sales order number as a reference.

They then click on generate serial number and the database does the work for them. Going to the appropriate table and incrementing the serial number.

Essentially what I wanted to have because of the way that I set up my database was to have generic scripts that ran and found the appropriate product code and created the right serial number on its own. Which is where I ran into my problem of grabbing the serial number. Because depending on the product series it would go to a different table altogether. So telling the database which field to grab, without making a new case for each product code was my problem.

I've attached the database as it is so far. It's still pretty primitive so not everything is as it should be and there is zero formatting, but you'll be able to see what I'm describing.

Untitled.fp7.zip

I have a meeting to go to in a few minutes. I'll look at this tonight or tomorrow.

I have a field Product Code on the Main Page. I have 10 different Product tables (Product 1, Product 2 etc.) all related to Main Page

Oops. Sounds like a basic structure problem.

You should most likely hae ONE product table.

More detail on what you're trying to accomplish would be helpful.

I've explained in greater detail in my last post, but I'll give some more detail about the serial number setup just in case it's confusing. The form is:

XXXXPPYMMB

Where XXXX is the serial number that is generated

PP is the product code that is selected

Y is the last digit of the year

MM is the month

B is the batch code that is set

I took a look at your file this morning and have to agree that it is a structural problem. You only need one table for products. It will make your relationships and calculations work much better. You can identify your products through drop downs using conditional value lists.

Try it this way?

BatchGen.fp7.zip

Thanks for all the help! Comment that's definitely a better (and MUCH) simpler way to set up the database so thank you