HELP - Inventory Numbering System

[ADJUDD1402]

HI all, I have an inventory system calculation as follows - Left(SURNAME;5) & DATE & "_" & Right ("00" & Inventory Number ;3)

The Inventory Number is a number field with an auto enter calculation - If ( IsEmpty (ARTWORK 2::s_Max_Inventory_Nr) ; 1 ; ARTWORK 2::s_Max_Inventory_Nr +1 )

"S_Max_inventory_nr" is a summary field - maximum of Inventory Number.

I'll attach my relationship that makes the system work.

Everything runs in order fine.. until a user wants to change the date of an existing record. What happens then is that record becomes the maxium number of the new date (that is fine). However, the old date has not realigned / readjusted,

What I mean is.. say I change the first record from 2013 to 2015. I will now have one record from 2013 that is 002.

Any ideas?

Thank you!

 

[dwdata]

Should not the ARTWORK 2 relationship only be based on the Artist Name?

If the end result that you want to is to have records that remain in sequence after an EDIT, you will have to run a TRIGGER SCRIPT on the Artist_Name and the c_Date field to keep the records in SEQUENCE. Check out the attached file.



 

dwdc_example.zip

[ADJUDD1402]

thank you! Can't seem to open with FM13 (Even after converting?)

scrap that, it was minimized! will take a look...

[ADJUDD1402]

Trying to work with the attached. I am getting all sorts of errors though. The find is even getting names from other records I have tried the script on a since deleted all and trying with new records. Attached first image here is before entering a new date. Second is putting in "May" from another aritst name that doesn't even exist in this database anymore?!

I've worked out the system though, just need to script -

1. Trigger enter field "date" - get existing date with a variable - if there is a date in it.
2. If no change is made to date, exit script
3. If change is made - find all records with that date under that artist, find and replace with numbers 1 onwards.
4. Use existing date variable from step 1, to find and replace the sequence numbers for that date and artist name too (as that will be out of order now / have one missing)

That is it I think?

Thank you

 

 

 

Edited July 31, 2015 by ADJUDD1402

[ADJUDD1402]

I seem to go to find and then the script pauses (not ideal) when i press continue it fills in the correct info in the fields but says no records match this criteria? Odd when clearly they do? This is the latest effot..

(i've tried $$ and $ for the variables)

during script and after you have press cancelled!

[bruceR]

It's your script. If you don't want the script to pause, don't set it to pause.

[ADJUDD1402]

thanks yes spotted that, however the script is still not performing the find. it stops to say criteria not matched.

[bruceR]

That isn't what your message shows. Shows the script IS performing the find.

But doesn't find anything.

Switch to $ variables unless you KNOW why you need $$variables.

Edited August 1, 2015 by BruceR

[bruceR]

Do you know how to use the debugger? You can step through the script and look, stop just at the perform find statement and look at the values. I suspect there is an issue with your date field. As in - is it really a date field?

Oh; but I see DATE isn't a date anyway. It's a NUMBER; it is really YEAR.