export found set to new database

[mmonsalveg]

hello , i want to know how to find an item from a portal an then export the found set to a new databse on a new record?

is this posible?

thanks

[LaRetta]

I am sorry but you have not provided enough information. What is the name of the parent and portal tables and how they are related? Records should not normally be moved around (exported and imported to another parent record). Usually you would just change the portal records' PARENT ID to change the parent they belong to.

And what do you mean by 'new database'? In FileMaker, we have files and within the files are tables. Both can be loosely called 'databases'. A fuller picture will help. :^)

[mmonsalveg]

Hello LaRetta ,

thanks for your reply ,

this is the layout for purchase orders ... where i instert all the orders by customer each user with different item .. about 30 items in one order ...

example:

then i make a report by item sell .... so the report show me all the items i had sell to al my customers globaly something like this :..... what i want to do is take this first row and then create a new record in a new database . somethig like image attached

i would like to has a button to export the line to a new record as this image;

the propose for this ,,, is to star new database with orders i have to put to my suppliers . i had tryed thy way of scripting but i dont know how to set a field with a found portal field....

hope you understan ..

thank you very much

mauricio

[LaRetta]

Hi Mauricio,

You said, "star new database with orders i have to put to my suppliers".

I would have thought you would generate your SupplierOrders from your Products table where outstanding stock can be summarized and grouped by Supplier. Writing from Products to SupplierLines will then write the Product's summary (of either inventory outstanding or LineItems sold). So you end up with ONE SupplierOrder with all the Products they handle (sorted of course by Product or by the Supplier's code, however they want it) and each line is a single ProductID and summary number and then of course your cost. Does this sound close?

You have done a good job providing me with helpful great screenshots but now I need to know the underlying relationships and process. And, ahm, are those repetitions or fields I see holding the amounts? :^O :-)

Also, it probably isn't necessary to duplicate Referencia and Categoria (unless needed for historical/audit purposes). After all, the tables are (or should be) related. But the SupplierLineID should be inserted back into the LineItems table since SupplierLines will be the 'one' side to many LineItems. That is ... if I understand your need.

It would be ideal if you could attach your file (you can 'Save As empty Clone' and zip it). If I'm misunderstanding your need and you simply want to split out the repetitions, you can handle that during a regular import. If we can't review your file then it would help to see a screen shot of your graph (only those tables referenced) please. And others may also respond with assistance, seeing something I'm missing - happens all the time.

BTW, cortes translates to 'cuts' ... my apology for my language incompetence. I assume this is your purchase order to your supplier, right?

[mmonsalveg]

Hello LaRetta ,

thank you so much for your time.

here i attached a file with my explanations,

in my real database, purchase orders are connected with products database , but not to check stock , is to check materials (fabrics , threads, buttons,) i forgot to said that we manufacture, ( cut to order) . actually in our company we take the " list report " and retype the totals in another software(not filemaker ) thats follows the cut tickets to final production.

and yes , cuts mean cortes ...

Thanks for your help ,

Mauricio

POrders.fp7.zip

[LaRetta]

Hi Mauricio,

I have reviewed your file. You are using fields (t2, t4, t6 etc) where you should use records in a related table. As you are already discovering, using fields increases the complexity of your solution ten-fold needlessly because you are forced to create individual summary fields for each field when, if structured properly, a single summary field will do. You also will not be able to generate reports grouped by size with your current configuration because multiple sizes exist in a single record.

actually in our company we take the " list report " and retype the totals in another software(not filemaker ) thats follows the cut tickets to final production.

I am not sure what this means but it sounds like needless work. Have you considered export instead of retyping? I am curious on how you receive this information, as you said, "this is where I start receiving orders from each customer." Is it an import from web or ? Do you receive the information already in T2, T4, T6 fields?

Please also give me an idea of what the cutting table should contain. Should it be as:

Reference: 2020 ... T2 total: 524 (total of all T2 for all customers)

Reference: 2020 ... T4 total: 30 (total of all T4 for all customers)

Reference: 4949 ... T2 total ... etc

Do you need ability to change those amounts in the Cutting table before sending to your supplier? If not, why use a cutting table at all (for the amounts). Why not just add the CuttingID into the LineItems table and use the records from there (once the CuttingID is inserted into LineItems, it has been 'sent to supplier')? A bit more clarification is needed before I can make final suggestions or provide you with a sample file.

Added: you would still want the cutting table for other fields such as SupplierID and DateSent.

[mmonsalveg]

Hello LaRetta,

thanks for your reply ,

i need the reports by global amounts… like how many pieces i sell from this Style (ref) in total and by size from all orders as the attached file

actually .. we do is taking the " list report " and then retype in another software that makes cutting tickets … we want to prevent that and make all integrate, prevent retype.

the cutting table should contain:

Reference …..T2 total…… T4 total ….. T6 total ….. T8 total and the summary of T2+T4+T6+T8

there is a cutting sample table i attached in the file.

we can't integrate the cutting with the line items , because from that cutting ticket another department will start the manufacturing process , like delivery times from third parties .. ( embroidery times , laundry times, etc..) also we make some changes in the amounts of the cutting tickets to have some stock inventory .

Thank you

Mauricio

[LaRetta]

also we make some changes in the amounts of the cutting tickets to have some stock inventory .

Then you do need the summarized amounts as standard data in the Cuttings table so you can change the amounts.

Reference …..T2 total…… T4 total ….. T6 total ….. T8 total and the summary of T2+T4+T6+T8

But this means your sizes are fields when they should be records. Both your LineItems and your Cuttings only need the following fields to track your styles and sizes in a much easier fashion than you have it now:

Ref ( the style ): 4949 or 2020 or whatever

Size ( T2 or T4 or whatever )

Qty ( the quantity )

(and of course the other fields such as CustomerID)

... and a single summary field for Qty. It is your existing structure (fields instead of records) on why it is difficult to resolve. Would you consider a design change to records over fields? If you really can't make the change then you can use Fast Summaries technique or another approach (looping your LineItems) but it is best to change it now. We will help you through it if you wish. Let me know how you wish to proceed. :laugh2:

[mmonsalveg]

Hello LaRetta,

thanks for your reply ,

I dont understand very well about fields instead records but I would consider a design change , as you said , " it is best to change it now"

Thanks for your time,

Mauricio

Edited September 12, 2012 by Lee Smith
Make the font bigger for readability.

[LaRetta]

Hi Mauricio,

Attached is a file showing how I could structure your solution. I hope this is helpful. Let me know if you have questions on it.

Rows instead of columns:

The biggest shock for you will be switching from Excel-column thinking to database thinking where every size is a record instead of a column. You can use filtered portals (or other techniques) to display each size in column fashion if you really need it but usually folks adjust quickly to rows. The idea here is to get your structure correct and only then look at the display needs from perspective of good structure.

Run the process from LineItems:

I would run it from LineItems so you can 1) summarize for multiple Customer orders into one Cutting Order for a Supplier and 2) it will be faster since you can take advantage of fast summaries. When it runs, it sets every LineItem in the found set with the date you specify for the supplier order (Cutting). Then this date is used in future runs to find LineItems not yet written (with an Amount) and provides a ‘trail’ back to the cuttings and so you know which LineItems have been written.

Options table:

I used an Options table to provide flexibility. It holds one each SupplierID/Style/Size. If you end up with more than one supplier who sells the same Style (REF) but you call it something else, you can add fields to hold your ‘InternalStyle’. If you have multiple attributes for Styles or Sizes (or you need to rename them easily), you will need to create additional tables for Style and Size. As is, I just used their actual values. Since this is obviously not your original file (which is fine), I do not know whether you are using anchor/buoy or how Products and Inventory or other tables relate in your setup or where the Style and size numbers come from so I am not including those tables. And I established your solution as entity with side TOG for the cuttings process so it is easy to see the relationships and translate the logic.

Tables, table occurrences and naming:

The black table occurrences are secondary table occurrences (colored black just for clarity that they are not separate tables but rather just graphical occurrences of them). The black Options TOs are used for conditional value list for selecting only ‘allowed’ values when entering LineItems in your POrders layout. You select Supplier then only Styles from that Supplier appear in the Styles pop-up and so forth. Why it is called Cutting and not Supplier Order or what ‘cutting’ means is still unclear so you may need to make some adjustments in the naming as well. It creates a new cutting order LINE for each Supplier/Style/Size combination and creates a Cutting Order (with Supplier information) for that date if it does not exist. If it DOES exist, the current summary is added to any existing amount but only if the Cutting order is not locked. You would lock it to indicate that you have changed the amounts and have submitted the order to the supplier so you do not want additional records writing to those totals.

Locked:

You said you will be changing the Amounts in the cutting orders so I added an IsLocked field in Cutting so that once you have finalized the order and sent it to the Supplier, you can lock it to prohibit any new LineItems from potentially writing over the values. It will create a new Cutting order if the existing one (for same Supplier/Date) is locked.

Overkill?

It may seem that way but the other problem with using fields for the sizes within a Style record (instead of creating CutLine records) is that if, after you transfer this data to Cuttings, you add a value into a size FIELD on the existing Style, you lose the ability track it separately and would have no way of knowing it should be added to CutLines. Of course, there are many other reasons for this type of structure which were mentioned above. If you move to version 12 there are other strategies but I’m going to skip that information since you are on v11. And there are other methods even in v11 for creating the Cutting Orders and CutLines including importing or writing to variable but since we may be adding to an existing CutLines OptionID, writing directly through is much easier even though it requires the lower TOG (the cutlines process).

UPDATE: One thing not provided is protection from record lock. That should be incorporated after we more fully understand your set-up, whether you are using transaction model, etc etc

POrdersMOD.zip

Edited September 20, 2012 by LaRetta