External SQL tables, add local fields to SQL table?

[Tim Design]

I am working on a "Quality Control" solution for FMGo on the ipad, using our company's SQL Tables (from SAP Business One). I have live connections with four SQL tables (Products, Purchase Orders, Manufacturers, and PO Line items). All of the relationships are good, and this works great as a closed solution for viewing information. However, I need to add a local FM table (or several fields, and container fields) that will house some qualilty control information for each record in the PO Line Items table, and will be directly related to the SQL table "PO Line items). The problem I can't figure out is how to create these fields, because I can't/don't want to make these new fields part of the SQL table. So, the question is, "is it possible to create fields (other than calculations/summaries) offline/local inside of filemaker in the SQL table occurence? Of if not, (which I believe is the case), how can I create a local FMP table that actually uses the records from the SQL table "PO Line Items"?

Basic problem that I need to resolve is:

I don't know where to put the quality control information that we collect on the iPad in FMP. It can't go into the SQL table, but it has to use the records from the SQL Table "PO Line Items".

Can you help?

[bcooney]

Yes, you can only add calc and summary fields to a ESS table in FM. However, you can create a new table in FM, and relate to your sqlPOLineItems in a one-to-one relationship.

[Tim Design]

Would that automatically populate my new table with the records from the SQL table?

[bcooney]

Perhaps I misunderstood. The new table is a FM table, "POLineItemDetail", and does not contain sql data. You'd need an interface in FMGo to enter this data, and I would imagine the user would need to navigate to a layout based on sqlPOLineItem. There you'd place the related fields in the FM POLineItemDetail table.

[Tim Design]

I'm sorry, but I'm struggling to understand what you are saying I should do at this point.

Here are the clarifying points:

The new table will be local FMP table, but it will need to use the records of the matching live SQL table, as it's records. The new local table will have container fields etc to capture quality control information.

The key is that the records will need to match the records of the SQL table for POLineItems.

I really just need a place to put some additional information related to each of the sql POLineItems records. Trying to figure out how to set this up, since I can't add fields to the SQL table to capture the info.

While I can relate the tables together, I don't know how to get the records into the new table and always have them current with the SQL table records...for example when new records are added in the SQL table, I need them to be in my local FMP table as well.

I know a bit about relationships, and am learning more each day, but please feel free to treat me like I'm brand new so I can't confuse what you are you telling me to do.

Thanks

[bcooney]

"The new table will be local FMP table, but it will need to use the records of the matching live SQL table, as it's records."

Um, no. The records in the new table are related to a parent record in the sql table.

"for example when new records are added in the SQL table, I need them to be in my local FMP table as well. "

I see the FM table as holding additional data for its parent POLineItem record. The FM table will have hold the foreign key from the sqlPOLineItem table. If there's no addtl data for this record, then you don't need the FM record. If you turn on create related record in the relationship, as the user enters addtl data the related record will be created.

I've also assumed that you've already connected your FM file to the ESS. You'd create the relationship from the sqlPOLineItem table to the FM POLineItemDetail table by sqlPOLineItemID in FileMaker's Relationship Graph.

hth,

Barbara