Ordering Requirements the same way as within the file

Discussions related to Crystal Reports development.

Moderator: Lisa Ennis

Post Reply
sweinstock
Posts: 18
Joined: Thu Jun 16, 2016 2:45 pm

Ordering Requirements the same way as within the file

Post by sweinstock »

I'm trying to retrieve the requirements from the 4.0 database and to order them the same way that they're ordered in the file.

If the requirements have the "show with numbering" checkbox checked, then I can do the below query to get it in the correct order.
SELECT *
FROM pfm.phrase
WHERE rootId# = @RootID
order by CAST(replace(Number, '.', '') AS int)

but if there's no number, how can I specify to order it the same way it's ordered in the file?

Thank you
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Ordering Requirements the same way as within the file

Post by Lisa Ennis »

The Id# field in the table is an incremented number, so using that as your sort instead of the number may work, or a combination of the two.
Lisa Ennis
Senior Report Developer
SoftPro
sweinstock
Posts: 18
Joined: Thu Jun 16, 2016 2:45 pm

Re: Ordering Requirements the same way as within the file

Post by sweinstock »

Thanks, the issue I'm having with the id# field is that if they use the move up / move down feature in select then the order of the ids is not accurate anymore
sweinstock
Posts: 18
Joined: Thu Jun 16, 2016 2:45 pm

Re: Ordering Requirements the same way as within the file

Post by sweinstock »

If someone unchecks ‘Show with numbering’ – then I need to say put it in after this number. If I say to put it in after this id# then it’ll be inaccurate because the id# column seems to just be in order of the way they entered the requirements into the file.

For example, This is how the database would look:

I add to my file as follows:
1) Test One
2) Test Two
3) Test Three

In the database the table looks as follows:
Id#: Number: Requirement
1 1. Test One
2 2. Test Two
3 3. Test Three


After all this is added I add a new requirement between 2 and 3 and I uncheck “Show with numbering”, the database now looks as follows

Id#: Number: Requirement
4 "" Test Requirement bet three and four (no number)
1 1. Test One
2 2. Test Two
3 3. Test Three

If I base on the id# field it’ll put this requirement last because it was added last.
If I base on the numbering column it also won’t know where to put it because the number field is empty.
Is there a column in the database that would allow me to specify that Id# 4 should go after number 2. Or after Id 2?

Thank you!
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Ordering Requirements the same way as within the file

Post by Lisa Ennis »

Unfortunately, you need the position field and that is not in the database. Please submit a request through SoftPro's Support Team that the field be added to the database.
Lisa Ennis
Senior Report Developer
SoftPro
sweinstock
Posts: 18
Joined: Thu Jun 16, 2016 2:45 pm

Re: Ordering Requirements the same way as within the file

Post by sweinstock »

Thank you, I will submit a request.
sweinstock
Posts: 18
Joined: Thu Jun 16, 2016 2:45 pm

Re: Ordering Requirements the same way as within the file

Post by sweinstock »

I need to do the same as the above for the existing liens. Is there a field in the database that enables me to order that same way that it's in the file?

Thanks!
sweinstock
Posts: 18
Joined: Thu Jun 16, 2016 2:45 pm

Re: Ordering Requirements the same way as within the file

Post by sweinstock »

I still didn't get a reply. Do I need to use the api to get the existing liens from the file in the same order that it's listed in the file (just like the requirements I need to get via api) or is there a way to get from the database directly?

Thanks!
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Ordering Requirements the same way as within the file

Post by Lisa Ennis »

The Existing Lien position field is not persisted to the database. Please submit a request through SoftPro's Support Team that the field be added to the database.
Lisa Ennis
Senior Report Developer
SoftPro
Post Reply