Need help defining a field showing missing numbers in a series

[sojahseh]

I've been using FMPro for a while with no formal training, so I am a bit of a beginner when it comes to a lot of the topics discussed here, so please bear with me.

I have two related databases. One is a job database that contains all the processing details of each of the production runs we have. Each record in this database is called a Shop Order, and each has a SO# which is a serialized number auto-entered on creation. The other is an invoicing database that creates invoices for these Shop Orders.

Is there a way I can have a field in the invoicing database which will show me which shop order numbers are not in the invoicing database?

I look forward to hearing from you all.

Matt

[Orlando]

Hi sojahseh

I think the easiest way to achive this is to have a relationship from your Shop Order table to you invoicing table by SO#.

Create a new calculation in Shop Orders that has the value of the SO# if the relationship is not active.

UnusedSO [text]

If ( IsValid ( InvoicingByShopOrder::UniqueID ) ; "" ; SO# )

Then from the Invoicing table create a constant relationship and create calculation in Invoicing that uses the List Function to get all the SO#

Missing Shop Orders [text]

List ( ShopOrdersByConstant::UnusedSO )

There is more than likely an easire way to achieve this but I hope this helps.

Regards

Orlando

[sojahseh]

Orlando,

Thanks for the help. As I read it, this set-up will return unused SO#'s and display them in the field in the Invoicing file. Is this correct?

I set up the calcs and fields as outlined but got no results.

I should clarify that there are no gaps in the Shop Order numbers in the in the Jobs database, only in the Invoicing database. So as it stands, there are Shop Order Numbers that have no invoices. There are also Shop orders that span two or three invoices.

I have the invoicing database related to the jobs database. At the creation of a new invoice the file asks for the Shop Order number and looks up the pertinent fields info.

Do I have this right, or am I misunderstanding the set-up?

Thanks in advance,

Matt

[Orlando]

This is what you are asking for, I just dont think I explained it to well to be fair.

The first step is to make sure the relationships are setup correctly.

1st relationship: Shop Orders - Invoicing by SO#

InvoicingByShopOrder

2nd relationship: Invoicing - Shop Orders using a constant or cartesian [x] join.

ShopOrdersByConstant

The second step is create the calculations.

First Calculation: In Shop Orders

UnusedSO [number]

If ( IsValid ( InvoicingByShopOrder::Uni queID ) ; "" ; SO# )

This will give you the SO# if there is no invoice with a corresponding SO#. Test this by checking one you know is used and one that is not.

Second Calculation: In Invoicing

Missing Shop Orders [text]

List ( ShopOrdersByConstant::Unu sedSO )

This should give you a complete list of SO# from Shop Orders that have not been used in Invoicing.

If you are still having trouble I will post an example file for you.

Regards

Orlando

[sojahseh]

Orlando,

Got it. Thanks so much. How to display that info has been bugging me for a while now.

One other thing though. How would I go about filtering and or sorting the results of the List calculation? Currently the numbers are displayed in ascending order, descending would be better.

Also, the Invoicing file is much younger than the Shop Order file, so the early SO#'s are all missing.

Thanks in advance,

Matt

[Orlando]

Hi Matt

I’m glad this has worked for you.

As for reordering the list, simply place a sort on the join between Invoicing and Shop Orders, SO# Descending, the list function will return the values in the order it sees them through the relationship.

If you want to exclude earlier values add an extra condition in the UnusedSO calculation

For example if you do not want to see any SO#'s before 100

If ( IsValid ( InvoicingByShopOrder::Uni queID ) or SO# < 100 ; "" ; SO# )

This should do the trick.

Another option to having the calculation in Invoicing, use a global and set the global with the calculation via a script.

Regards

Orlando

[sojahseh]

Orlando,

Works perfectly! Thanks so much for your help. It always amazes me how much sense these solutions make after the fact. Of course having them spelled out for you doesn't hur either.

Thanks again,

Matt