Repeating fields problem

[DukeS]

Hi fm masters!

I have problem with concatenating repeating fields...

I have incoming products (one product many factory serial numbers) and outgoing products (same serial numbers like incoming).

So i need to display products and their serial number that are still in database.

For example:

Product one has been entered with next serial numbers A1234, B1234 and C1234.

Then we selled same product but only two of them with serial numbers A1234 and C1234. In stock then should be only one product with s.n. B1234.

Is it possible to make calculation from repeating field income and outging in other field on exmpl. sum (income-outgoing = sum) which should contain only s.n. B1234?

Thanks

Dean

[QuinTech]

Hi Dean, and welcome to FM Forums! My first recommendation would be for you to ditch the repeating fields. They're good for some things, but for the job (i think) you're doing, you'd be better off using related files. May i suggest that you have two files: Products and Units.

Products.fp5 is used to store information about products (Samsung 17" Television Set, Sony 17" TV, Sony 19" TV, ... ). Make sure this file has an auto-generated serial field called ProductID.

Units.fp5 stores info about particular pieces (Samsung 17" TV with serial number 1A2B3C, Samsung 17" TV with serial number 9D8E7F, ... , Sony 19" TV with serial number MNZ382, ... ). Make sure this file also has a field for ProductID.

Now establish a relationship from ProductID in Products to ProductID in Units.

Take a look at the attachments to get a better feel for how this works & let us know if you have any questions.

Jerry

dean.zip

[DukeS]

QuinTech thanks for fast reply and the example files!

Situation is that i have to change one previously written invoice program for one company, so I have to keep things like they are (as much as possible) becouse they already have about 3000 products in their database...

I have one more question...now they have products listed in portal (With serial numbers from repeating field). Is it possible to show portal in portal?

They need to see product number, description, etc. and their serial numbers.

Dean

[Fenton]

After you create the correct relational structure, i.e., a file with regular fields instead of repeating (which could be a modified clone(s) of what they have now), then start to import the file with repeating fields, the import dialog will present a checkbox on the second window,* "Split repeating fields into separate records?"

The number of products is really immaterial, 3000 is no big deal. What matters is whether they did anything else goofy with the structure. If not it shouldn't be too difficult to convert to proper relational structure. It will make everything easier in the long run.

And, no, you can't put a portal in a portal. You can however, "pipe" the data via relational calculation fields (if you have a relational structure). In v.7 you don't even need to pipe it.

Basically it's like this. You want to do something beyond what they are able to do now. One of the reasons they are unable to do it is that their structure is inadequate.

*The window where you normally only see "Allow auto-enter fields etc."

[DukeS]

Ok, I've splitted repeating fields into separate records (stock is now fine, it shows only records that are still in), but now entering data seems impossible to do...

Portal works fine (you enter name, product code, ...), but serial numbers...

I now have database products_in (this holds portal (from database product_positions) with products that firm ordered). Product_positions have portal with product serial numbers (database units_in). Everything works fine, but they want to enter data in first database products_in with their serial numbers also.

Is there a solution to this problem?

I attached simple shema of situation.

Please help me guys...

Dean

[QuinTech]

So you can enter data into the "Product code" and "Product description" fields, but not "Serial numbers in repeating field?" Check that the repeating field is a related field (has the :: before the name of the field in layout mode).

J

[DukeS]

Yes I can enter data in repeating field but i want to get rid of it.

Repeating field has only few rows but what if I need to put 100 serial numbers in it?

The issue is that they want enter product serial numbers with order layout (layout on which is portal with ordered products).

Dean

[QuinTech]

OK, so you want to make the repeating field into a non-rep. field but shown through a portal. (Stop me if i'm mistaken.) You can't put a portal within a portal, so what you must do is direct the user to a layout (can be done with a simple button) that shows Product Code and Product Description as non-related fields, with SerialNumber fields in another portal. So you will have to be in the file where Products are the top level in order to enter serial numbers.

J

[DukeS]

Yep, i guess that's it. I'll put a button on portal row, then connect it to product code and when clicked it takes you to the layout with product where you can enter serial numbers.

Thanks again

Dean