Matching fields in different databases

[Daniel Blake]

FileMaker Pro 5 on a Mac

I think this application was created from a template or two. It has four databases which make up a POS system.

Roster (No relationships)

Inventory (barcode = barcode in Sales and Line Item)

Sales

Line Item (barcode is a Lookup field from Inventory)

The problem is that Inventory wasn't designed with Item quantities so every Item is a new Item and Inventory is getting HUGE. Nothing is deleted from Inventory after a sale.

I am trying to create a (Find View) Layout in Inventory that will find matching Barcode records in the Line Item database so I can delete all the matching records from the Inventory database making it MUCH smaller. It has been used since 1998 at least.

barcode is set as a number field

The documentation didn't give me much to go on but I thought this is what I needed to do.

Find View (in the barcode field)

==(Line Item::Barcode)

I get an error about the data needs to be numbers.

What am I doing wrong?

Thanks for the help

Daniel Blake

[Ugo DI LUCA]

Hi,

Draw a portal in Inventory using the relationship :

Inventoty:Barcode::LineItem:Barcode.

Put a button at end of each row with script "delete portal row".

Now, not sure I understood all.

Is it that some records in the Line Item aren't related to the Inventory, but some are and you want to delete only those that are related ?

If so, using the relationship you're using for the lookup, create a calculation field in the Line Item :

Case(Is Valid(YourRelationshipToInventory::Barcode),1,0)

This calc will tag all matching records with a 1.

Then search for the 1 in delete (if really that's what you're looking for)

[Daniel Blake]

I said I was a beginner. I have seen the term portal used in many posts here. What is a Portal?

Actually it is the other way around.

Some records in Inventory won't be in Line Item.

I am trying to delete all items that have already been sold.

If I wanted to be really thorough I would like to be able to search for anything sold before a certain date. That way if there was any mistake or an item was returned before 30 days was up the Inventory record would still be there.

Thanks for the help so far.

[Lee Smith]

A portal is a window in a ship or a way of displaying fields in the Current file from a Related file.

Beyond that, if you really don't know what they are, you should read the Manual, buy some reference books, take a class about relational databases, or do all of the above.

To teach you the ABC's here about relationships, etc. is darn near impossible.

Sorry to be so brutal, but you can not Short Cut this stuff.

Lee

[Ugo DI LUCA]

OK, so the calc given would not suit your needs, even if (as mentionned in your other post), you're using it in the Inventory file instead.

The calc given won't check for your invoice field.

Create a few calculations in the Line Item :

c_checkInvoicedItems = Case(not is Empty(InvoiceN

[Ugo DI LUCA]

Hihi...Lee already answered about portals....

BTW Old Hand, how are you feeling with this status

[Lee Smith]

Old

[Daniel Blake]

Thank you both for your help.

Lee, I understand about not short cuting on these things. I have created a few relational databases with MS Access before and I never ran accross that particular term before. In fact I just searched Access' help file and it doesn't know what a portal is either.

Ugo, Why do I need to create additional fields when all I really need to do is find every number in field BARCODE in one database that has an equal in another database with the field BARCODE?

If this was Access this would be a One To One relationship and pretty easy for me to do. I'm not knocking FileMaker Pro, I just don't know this application yet, so I don't know how it thinks.

I'm at work without my Mac, so I will try Ugo's solution when I get home. I have been spending a while on the forums and have read many answers to questions from both of you.

Thanks again for the help.

Daniel Blake

[Ugo DI LUCA]

Absolutely, you're making big progess

Change the calc in the line item :

c_checkInvoicedItems = Case(not is Empty(InvoiceN

[Lee Smith]

Hi Daniel ,

[color:"blue"]

> Lee, I understand about not short cuting on these things. I have created a

> few relational databases with MS Access before and I never ran accross that

> particular term before. In fact I just searched Access' help file and it

> doesn't know what a portal is either.

I'm not at all familiar with M$ Access, and I'm suppose it might have a term or two that I wouldn't know too. However, if I was on an Access List and a term came up that I didn't know, I think I would first search my Access manual or on-line help instead of FileMaker's.

Lee

[Daniel Blake]

You are correct Lee. I replied immediately without thinking about checking the Documentation, sorry.

I still haven't had time to try Ugo's suggestion, but I have learned why the new fields are nessasary. I learned that a portal doesn't care about a found set. They always show records that match the criteria for the relationship.

Daniel Blake