Portal showing *some* of the data

[MHardee@mercuryprtg.com]

Have a project I will have to deal with on a daily basis and am looking for ideas on how to start so I don't end up with a mess..

 

I've attached conceptual images to this email.. Basically, a "invoice" from client supplied data.. The data, IMHO, is the issue.. On the attached file, SuppliedData.jpg, shows my issue or non-understanding of the project. I've highlighted a "record" in yellow.

 

Basically, it's an invoice that shows what you bought, how many, etc... Column A shows the "order" number, Column O the package, Column N is the contents of that "package" and column Q the price. I've gotten a preliminary Filemaker file started that has an import table (to read in the data and make some calculations for changed the date structure) and an invoice table that contains a portal to list the product. I have a portal that lists the purchases in the invoice table.

 

From the example, you can see there a several "records" in the Excel data that must become a single record in filemaker (or not, that's why I am here!).. What they are looking for is to (refer to the highlighted areas) (Column O) Package "SW ESS PLUS" to be listed Once (First Occurrence), Column N (Pieces of package) to list ALL of them, and lastly Column Q (Package Price) to only be listed on the first line (First Occurance)...

 

What I have done is get the portal to list ALL of the data.. What I cannot grasp is how to display ONLY the first occurance of Columns O and Q in the same portal...

 

I've attached another image with a comp of how the invoice is to end up... all of the cosmetic stuff is not an issue.. Just the show me only one of these and all of the other parts of that portal...

 

and, wow, it's very hard to describe without hand gestures! If someone would like to send me an estimate of what you would charge for this if that's the way we need to do it, that would be fine as well... I don't THINK this is a biggie... it's just beyond my novice skills at this moment..

[MHardee@mercuryprtg.com]

Whoa... not a peep...

 

Now I AM worried.....

 

Could anyone recommend a book, URL, etc. that explains in laymans terms:

 

Tables, relationships and Database 101..

 

I'm having a tough time beating into my gray matter how using auto-enter serials in two different tables constructs a meanginful relationship between them. I mean, what does 0001 in a products table have to do with a 0001 in a invoice table...

 

Thanks in advance....

 

Confused....

[AHunter3]

I'm having a tough time beating into my gray matter how using auto-enter serials in two different tables constructs a meanginful relationship between them.

 

No wonder you're confused. IT absolutely, totally, does NOT construct a relationship, meaningful or otherwise, between diddly & squat.

 

Let's start over. Table A and Table B.

 

Let's put auto-enter serial numbers in each of them. Not to relate them to each other, just to have a guaranteed-unique identifier in each table.

 

Now let us suppose we want a relationship between Table A and Table B, where the Table B records will be "children" of the Table A records. That is, more than one Table B record might exist for a given Table A record, or for that matter there might be zero Table B records for a given Table A record, but every Table B record will have one and only one, exactly one, Table A record, which is its parent.

 

We can use our serial number field in Table A in our relationship. We do not HAVE to, but it has the advantage of, first, being there already and, second, being unique (will not appear in some OTHER Table A record).

 

Let us start out assuming that the relationship we're going to create is for a portal from a Table A layout to a Table B layout, and we want to enable record creation in Table B via this relationship, so that we can go to the bottom portal row and just start typing in order to create related records in Table B. I'll cover the other main kind of relationship in a minute.

 

How about on the other side? We need a field to match it to. Can we use the auto-serial number field in Table B? Hell no! Absolutely NOT! What we want is an empty number field that will not auto-enter anything. The act of creating related records in Table B via the relationship from Table A will auto-enter, NOT a serial number (next number in a serial) within Table B, but rather the value of the Table A field that hooks Table A to Table B. In this case it happens to be an auto-enter serial number in Table A because that's what we picked, but if we'd picked, oh I dunno, the creation date as the Table A field to use in the relationship, it would auto-enter the creation date in the Table B field. If we picked the Favorite Color field, it would use that. There is nothing intrinsically "auto-enter serial-ish" about the foreign side (remote side, external side, portal side) of a relationship or the field that that relationship uses. It just fills in with the value that the Table A field that's hooked to it happens to have.

 

Meanwhile, not all relationships are set up with "allow record creation via this relationship" checked. Let's say we unchecked it. We can now no longer create a record in Table B by going to the bottom portal row and typing in it. The bottom portal row is no longer a blank empty line waiting for us to type in it. The bottom portal row is an atual (already existent) record with data in it.

 

To create a related record in Table B, now, we would probably run a script:

 

Set Variable [$CopyTheSerial, Table A::Serial Number]

Go to Layout ["Table B Layout"]

New Record

Set Field [Table B::Foreign Key, "$CopyTheSerial]

Commit Records [no dialog]

Go to Layout [original layout]

 

As you can see, perhaps more clearly in this example than the previous one, there is nothing auto-enter-serial-ish happening here. The field was blank when the record was created. It has a value because I put one there, and I put one there precisely in order to make it be related to the record I started from in Table A.