Blank fields in a repeating field

I have two tables, an Invoice and a Customs Manifest. The Invoice
references a Product Catalogue through a relationship based on a
Product ID in the Product Catalogue. I enter the Product ID in the
Invoice through a Value List. Certain related fields then appear, such
as colour, weight, cost etc.

The Manifest has a relationship with the Invoice based on an Invoice
#. Again my aim is to see certain fields from the Product Catalogue
and the Invoice appear in the Manifest. Some of these categories or
fields are omitted from the Invoice (MadeIn, ExportCodes etc) but now
relevant. One field is Invoice based; number ordered.

My problem is that the data I hope to see in the Manifest does not
appear consistently, that is, data is dropped.

Schematically it might look like this
ABCDE (all fields with data)
AB
A
A
AB

(the letters are repeating fields)

Any ideas why?

Additionally, sometime 'Lookup' data does not apear in the Invoice
until I re-reference it by selecting the Product ID anew. ??

Thanks for any replies

Re: Blank fields in a repeating field

schreef in bericht
news:1194929007.373807.47310@k35g2000prh.googlegroups.com...
>I have two tables, an Invoice and a Customs Manifest. The Invoice
> references a Product Catalogue through a relationship based on a
> Product ID in the Product Catalogue. I enter the Product ID in the
> Invoice through a Value List. Certain related fields then appear, such
> as colour, weight, cost etc.
>
> The Manifest has a relationship with the Invoice based on an Invoice
> #. Again my aim is to see certain fields from the Product Catalogue
> and the Invoice appear in the Manifest. Some of these categories or
> fields are omitted from the Invoice (MadeIn, ExportCodes etc) but now
> relevant. One field is Invoice based; number ordered.
>
> My problem is that the data I hope to see in the Manifest does not
> appear consistently, that is, data is dropped.
>
> Schematically it might look like this
> ABCDE (all fields with data)
> AB
> A
> A
> AB
>
> (the letters are repeating fields)
>
> Any ideas why?
>

Your question is not very clear to me. You first state some categories are
omtitted. Did you do that (by calc or by search or??) or did you expect to
see them? You loose some data probably because of the fact those fields are
repeating fields. It is not exactly clear how you display them (portal,
listview), but as a general idea you should loose all the related fields and
rework your tables so they use normal fields. Although with the newer
versions of filemaker (you don't state version and platform) there is an
extended set of functions to use with repeats, they still are difficult to
work with. I would only use them for storing graphics for the UI.

> Additionally, sometime 'Lookup' data does not apear in the Invoice
> until I re-reference it by selecting the Product ID anew. ??

This is normal behavior. A lookup does not update itself. Because of this
you might reconsidder your needs. Do you really need the data present again,
although it is in your database somewhere else? Could you do it with a
calculated result? If you really need the look-ups, you will have to create
a script wit a loop and a re-lookup scriptstep (you'll find it in the
help-file)

Keep well, Ursus

Re: Blank fields in a repeating field

In article <1194929007.373807.47310@k35g2000prh.googlegroups.com>,
buck.matthew74@yahoo.com wrote:

> I have two tables, an Invoice and a Customs Manifest. The Invoice
> references a Product Catalogue through a relationship based on a
> Product ID in the Product Catalogue. I enter the Product ID in the
> Invoice through a Value List. Certain related fields then appear, such
> as colour, weight, cost etc.
>
> The Manifest has a relationship with the Invoice based on an Invoice
> #. Again my aim is to see certain fields from the Product Catalogue
> and the Invoice appear in the Manifest. Some of these categories or
> fields are omitted from the Invoice (MadeIn, ExportCodes etc) but now
> relevant. One field is Invoice based; number ordered.
>
> My problem is that the data I hope to see in the Manifest does not
> appear consistently, that is, data is dropped.
>
> Schematically it might look like this
> ABCDE (all fields with data)
> AB
> A
> A
> AB
>
> (the letters are repeating fields)
>
> Any ideas why?
>
> Additionally, sometime 'Lookup' data does not apear in the Invoice
> until I re-reference it by selecting the Product ID anew. ??
>
> Thanks for any replies

I'm not sure why you've got an Invoice AND a Customs Manifest table.
Wouldn't one table doing both jobs be sufficient since they have many
of the same fields in common?? You would simply have two different
Layouts associated with that table - one for printing the invoice and
one for printing the customs manifest. Having them both in one table
would also side-step your problem of copying the same details from one
table to another.

Are you really using Repeating fields?? If you are, then don't -
they're worse than useless in most situations where people think they
should be using them. They're largely just a left-over from the days
before FileMaker was a proper relational system. At best they should
really only be used for pure storage, when you don't need to do
anything with that data except display or store it.

Unfortunately I don't understand your main problem though, but I'm
guessing it's probably because you are using silly Repeating fields.
Normally an invoicing system would have two related tables:

Invoices to contain the overall invoice data
(ie. date, invoice number, address, etc.)

InvoiceItems to contain the individual line items for
the Invoices records

These two tables would be related by Invoice Number, with the
InvoiceItems appearing as a Portal on an Invoices data entry Layout.
BUT, because printing Portals is a no-no, the actual invoice would be
printed from the InvoiceItems table using a relationship back to the
Invoices table to obtain the address, etc.

In your case, if you really want to stay with a separate Invoice and
Customs Manifest system, you'll need four tables: Invoices,
InvoiceItems, Manifest, ManifestItems. Now you can begin to see why it
could be better to combine these.

Such a design also makes it much easier to find out how many "Apples"
you've sold over a user-defined time period by simply performing a Find
in the InvoiceItems table and having a Summary field / report total up
the individual records. Trying to do this in a system that uses
Repeating fields for the invoice line items would be a headache of
extra fields and calculations.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: Blank fields in a repeating field

Hello Ursus and Harry

Before I attempt to rectify my problem I will try be more clear about
my setup. I use 8.5 Pro.

The Manifest Layout is based on the Invoice Table - there was a
Manifest Table but lets say I shall abandoned this. When I say
repeating fields I think more accurately I should call them
'repetitions' as in Field/Control Setup - Show repetitions 1 through 8
(12 defined). These repetitions correspond with the Sales Lines in the
Invoice (where I input the Product ID and all relevant associated
information drops in (colour, weight, cost etc). I am trying to get
the Manifest to reflect a portion of this information (number ordered,
price and weight) and some new information (CustomsCodes for
exporting, the text ' Canada' under a 'Manufactured In' heading, an
export value, and line subtotal $). The new information comes out of
the Product Catalogue, which I think Harry is calling InvoiceItems
(?). The Product Catalogue has its own Table. The Product Catalogue
has a relationship with the Invoice through the Product ID. With all
fields in the Invoice Table do I need a relationship to the Manifest -
I am guessing not?

The reason I used Lookups is to prevent the address changing later -
the Invoice data becomes a permanent record.

Thanks for your thoughts

My Head Is Spinning
Matthew


On Nov 13, 12:03 am, "Ursus" wrote:
> schreef in berichtnews:1194929007.373807.47310@k35g2000prh.googlegroups .com...
>
>
>
> >I have two tables, an Invoice and a Customs Manifest. The Invoice
> > references a Product Catalogue through a relationship based on a
> > Product ID in the Product Catalogue. I enter the Product ID in the
> > Invoice through a Value List. Certain related fields then appear, such
> > as colour, weight, cost etc.
>
> > The Manifest has a relationship with the Invoice based on an Invoice
> > #. Again my aim is to see certain fields from the Product Catalogue
> > and the Invoice appear in the Manifest. Some of these categories or
> > fields are omitted from the Invoice (MadeIn, ExportCodes etc) but now
> > relevant. One field is Invoice based; number ordered.
>
> > My problem is that the data I hope to see in the Manifest does not
> > appear consistently, that is, data is dropped.
>
> > Schematically it might look like this
> > ABCDE (all fields with data)
> > AB
> > A
> > A
> > AB
>
> > (the letters are repeating fields)
>
> > Any ideas why?
>
> Your question is not very clear to me. You first state some categories are
> omtitted. Did you do that (by calc or by search or??) or did you expect to
> see them? You loose some data probably because of the fact those fields are
> repeating fields. It is not exactly clear how you display them (portal,
> listview), but as a general idea you should loose all the related fields and
> rework your tables so they use normal fields. Although with the newer
> versions of filemaker (you don't state version and platform) there is an
> extended set of functions to use with repeats, they still are difficult to
> work with. I would only use them for storing graphics for the UI.
>
> > Additionally, sometime 'Lookup' data does not apear in the Invoice
> > until I re-reference it by selecting the Product ID anew. ??
>
> This is normal behavior. A lookup does not update itself. Because of this
> you might reconsidder your needs. Do you really need the data present again,
> although it is in your database somewhere else? Could you do it with a
> calculated result? If you really need the look-ups, you will have to create
> a script wit a loop and a re-lookup scriptstep (you'll find it in the
> help-file)
>
> Keep well, Ursus

Re: Blank fields in a repeating field

In article <1195010567.128275.275900@v29g2000prd.googlegroups.com>,
buck.matthew74@yahoo.com wrote:

> Hello Ursus and Harry
>
> Before I attempt to rectify my problem I will try be more clear about
> my setup. I use 8.5 Pro.
>
> The Manifest Layout is based on the Invoice Table - there was a
> Manifest Table but lets say I shall abandoned this. When I say
> repeating fields I think more accurately I should call them
> 'repetitions' as in Field/Control Setup - Show repetitions 1 through 8
> (12 defined). These repetitions correspond with the Sales Lines in the
> Invoice (where I input the Product ID and all relevant associated
> information drops in (colour, weight, cost etc). I am trying to get
> the Manifest to reflect a portion of this information (number ordered,
> price and weight) and some new information (CustomsCodes for
> exporting, the text ' Canada' under a 'Manufactured In' heading, an
> export value, and line subtotal $). The new information comes out of
> the Product Catalogue, which I think Harry is calling InvoiceItems
> (?). The Product Catalogue has its own Table. The Product Catalogue
> has a relationship with the Invoice through the Product ID. With all
> fields in the Invoice Table do I need a relationship to the Manifest -
> I am guessing not?
>
> The reason I used Lookups is to prevent the address changing later -
> the Invoice data becomes a permanent record.
>
> Thanks for your thoughts

Do not use Repeating fields. They are a pain in the sit-upon to use for
anything other than PURE storage / display.

Instead have a separate table called InvoiceItems to stores each row of
the invoice as a separate related record (related by Invoice Number).
This is a different table to the Products which stores the product
details. The relationship graph should be something like:

Invoices -> InvoiceItems -> Products
by InvoiceNum by Product ID


For example, for an invoice of:

Fred Flinstone Invoice Number: 2007-123
1 Granite Street
Bedrock

Product ID Item Price Quantity Cost
015 Apples 0.20 5 1.00
081 Bananas 0.15 3 0.45
782 Oranges 0.18 1 0.18

Total Cost 1.63

The address details would be stored in the Invoices table, but the line
items (Apples, Bananas, etc.) would be separate records in the related
InvoiceItems table.

The Total Cost is simply a sum calculation (in the Invoice table) of
these related record Costs.
eg.
Total Cost Calculation, Number Result, Unstored
= Sum(rel_ToInvoiceItems::Cost)


Each of the InvoiceItem records is related to the Products table via
the Product ID, which allows you to "lookup" the appropriate things
like the item's Price. There's no problem using Lookup, although
personally I tend to use an Auto-enter by Calculation since it gives
more felxibility.

Data entry can be done via the Invoice tabel using a Portal to display
/ enter the related InvoiceItem records.

Printing of the invoice must be done from the InvoiceItems table since
Portals are not designed to be printed.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: Blank fields in a repeating field

Since Harry so eloquently has answered your question I refrain from further
action.

"I refer the honourable gentleman to the answer previously given"

Ursus

"Helpful Harry" schreef in bericht
news:141120071825515742%helpful_harry@nom.de.plume.com...
> In article <1195010567.128275.275900@v29g2000prd.googlegroups.com>,
> buck.matthew74@yahoo.com wrote:
>
>> Hello Ursus and Harry
>>
>> Before I attempt to rectify my problem I will try be more clear about
>> my setup. I use 8.5 Pro.
>>
>> The Manifest Layout is based on the Invoice Table - there was a
>> Manifest Table but lets say I shall abandoned this. When I say
>> repeating fields I think more accurately I should call them
>> 'repetitions' as in Field/Control Setup - Show repetitions 1 through 8
>> (12 defined). These repetitions correspond with the Sales Lines in the
>> Invoice (where I input the Product ID and all relevant associated
>> information drops in (colour, weight, cost etc). I am trying to get
>> the Manifest to reflect a portion of this information (number ordered,
>> price and weight) and some new information (CustomsCodes for
>> exporting, the text ' Canada' under a 'Manufactured In' heading, an
>> export value, and line subtotal $). The new information comes out of
>> the Product Catalogue, which I think Harry is calling InvoiceItems
>> (?). The Product Catalogue has its own Table. The Product Catalogue
>> has a relationship with the Invoice through the Product ID. With all
>> fields in the Invoice Table do I need a relationship to the Manifest -
>> I am guessing not?
>>
>> The reason I used Lookups is to prevent the address changing later -
>> the Invoice data becomes a permanent record.
>>
>> Thanks for your thoughts
>
> Do not use Repeating fields. They are a pain in the sit-upon to use for
> anything other than PURE storage / display.
>
> Instead have a separate table called InvoiceItems to stores each row of
> the invoice as a separate related record (related by Invoice Number).
> This is a different table to the Products which stores the product
> details. The relationship graph should be something like:
>
> Invoices -> InvoiceItems -> Products
> by InvoiceNum by Product ID
>
>
> For example, for an invoice of:
>
> Fred Flinstone Invoice Number: 2007-123
> 1 Granite Street
> Bedrock
>
> Product ID Item Price Quantity Cost
> 015 Apples 0.20 5 1.00
> 081 Bananas 0.15 3 0.45
> 782 Oranges 0.18 1 0.18
>
> Total Cost 1.63
>
> The address details would be stored in the Invoices table, but the line
> items (Apples, Bananas, etc.) would be separate records in the related
> InvoiceItems table.
>
> The Total Cost is simply a sum calculation (in the Invoice table) of
> these related record Costs.
> eg.
> Total Cost Calculation, Number Result, Unstored
> = Sum(rel_ToInvoiceItems::Cost)
>
>
> Each of the InvoiceItem records is related to the Products table via
> the Product ID, which allows you to "lookup" the appropriate things
> like the item's Price. There's no problem using Lookup, although
> personally I tend to use an Auto-enter by Calculation since it gives
> more felxibility.
>
> Data entry can be done via the Invoice tabel using a Portal to display
> / enter the related InvoiceItem records.
>
> Printing of the invoice must be done from the InvoiceItems table since
> Portals are not designed to be printed.
>
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)