Greetings,

I have added a new table to my database called "Serial Numbers" - I know, original. In the table "Serial Numbers" there are 7 fields.

*SN_Serial Number Prefex

*SN_Serial Number

*SN_Category_1

*SN_Category_2

*Category Full Name

*SN_Serial Number Complete

*SN_Status

SN_Serial Number Prefex:

Just a field that has the first part of the Serial number stored, for example Adult Games is AG. Books & Mags is BM.

SN_Serial Number:

Just a field that contains the Second part of the Serial number, this is basically 500 records that have a increase of one. For example 0001 - 0500.

SN_Category_1:

Has a field that contains a drop down box that brings up a list of the Categories.

SN_Category_2:

Has a field that contains a drop down box that brings up a list of the Sub Categories that are affiliated with the Category field.

Category Full Name:

Contains the Category and Subcategory names together in the one field with a _ in the middle of the two. (this is set for reationship use only).

SN_Serial Number Complete:

Joins the first and second part of the Serial number together, for example Adult games becomes;

AG0001

AG0002

AG0003

SN_Status:

This has two possible enteries, Assigned and Unassigned. This is a Calaculation field. The code is below that should decide between the two.

Case(SN_Serial Number Complete = Product Catalog::v_products_model;"Assigned";"Unassigned")

[color:red]The Problem:

The first record is working well, it returns "Assigned" for the Serial Number AG0001. However the rest of the 500 of them all return "Unassigned"

I don't know why it isnt working on the first record but not on the rest of them.

Anyone have any ideas why ?

Assuming your Serial Numbers SN_Serial Number Complete is related to Product Catalog by v_products_model, that calc should work, although I'd probably write it:

Case( not IsEmpty( Product Catalog::v_products_model) ;"Assigned";"Unassigned")

Or I'd make SN_Status a number field, rename it SN_Assigned, format it as a checkbox with a value list of "1" and calculate it like this: 

not IsEmpty( Product Catalog::v_products_model )

But I digress. It seems to me what's relevant here is not how you constructed your SN_Serial Number Complete field, but how you constructed your relationship to Product Catalog. You haven't told us anything about that.

Also, does your SN_Status calc return a text result?

Greetings;

I was playing with the relationships last night and managed to get it working a bit better, now instead of just the first one, it is doing others, however it still isnt 100 %. infact more like 50 %.

I am starting to wonder if there are too many records for it to look up and it is getting it's self confused or something.

Above is a picture of the relationships.

Edited October 9, 200817 yr by Guest

Okey, this is weird, it works when there is data in the Subcategory field, infact it works like a charm, but it won't work 100 % when there is only data in the field Category_1 and nothing in Category_2.

: